1

I was trying to compile a list of VisualForce components (but this could be any data really) and their attributes and put them in an Salesforce custom object.

So I did this in a way that was geared towards a traditional database. I made a list of all possible components and gave each one an ID. Then I created a list of all possible Attributes and gave each one an ID. Then I created an "Intermediate" table that held pairs of component IDs and attribute IDs, so that you could query any component and get a list of all its attributes and vice versa.

I naively thought I would then make a component object and a attribute object and an junction object to join them. But I am having problems.

I can't really set the IDs of the objects themselves, so I tried to set the name field to use the ID numbers I created and create a different field for the text name. Then I created a junction object, with a master detail to the component and a master detail to the attribute.

But when I try to upload the ID lists I get an error on the data type. Apparently it is looking for a reference and doesn't care that I have a number that does in fact match the name field of the other object.

So if you had a traditional many to many relationship using three tables in this manner, what is considered the best way to translate it to Salesforce?

Or if I wanted to plan such a thing from scratch and have it be designed towards the Salesforce platform what approach should I take?

Edit to clarify, below is a screen grab of three spreadsheets, each one would be the data that would go into traditional SQL tables.

The one I have labeled "1" is the components, the one I labeled "2" is a list of all the attributes and the on labeled "Junction" relates one table to the other. So that you could query any item in one and see the many attributes it has from 2, and you could query any item in 2 and see how many components in 1 that it is used in.

This would be the quick way to set this up in SQL Server for instance, but for the reasons I mentioned above it does not seem to be very Salesforce friendly.

enter image description here

Improve this question
2
  • 3
    Scotty, can you share some more details? Perhaps in a graphical way? I am having a bit of trouble following your questions
    – Sebastian Kessel
    Commented Apr 8, 2016 at 16:55
  • Scott.... Can you add the SFDC structure? (Schema Builder screenshot would be ideal)
    – Sebastian Kessel
    Commented Apr 8, 2016 at 18:23

1 Answer 1

Reset to default
2

Your data model in Salesforce won't be any different than what you've currently got documented in the image in your question.

If you want to load data into this junction object structure, you're going to need the Salesforce id values of the Component__c and Attribute__c records to populate the lookup fields.

Or if you would rather use your own Id column value to populate the relationship, you would add a text field on Component__c and Attribute__c, marked as an External Id, and populate that field with the values you're showing in your images in the Id column. Then when you load data into the ComponentAttribute__c table you can choose to use the external id field on the parent objects as the identifier for the lookup field (rather than needing to know the Salesforce record id).

enter image description here

Improve this answer
2
  • Mark, thank you for the detailed answer. It seems like this should work, but in data loader my junction object show "Reference" as data type for the lookup fields. When I try to import to the junction object I get a "value of incorrect type" for every single record. Is there supposed to be some way to designate the lookup field when I create the junction object? Because I can't see a way to change the data type once I am in the data loader.
    – ScottH7a9
    Commented Apr 8, 2016 at 20:24
  • Okay, never mind my previous comment, this works, I just had to use Upsert instead of Insert when following your instructions. Not sure why but you get the option to change the external ID mapping with Upsert but not Insert....Thanks again.
    – ScottH7a9
    Commented Apr 8, 2016 at 20:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .