Dynamic lookup? What is the best practices?

Question

I know that lookup is 1-M relationship between just two objects. Tried to find but so far wasn't able a proper way. Maybe someone faced the same case and can share or give an advice?

Use case: I want to link custom object (myCustom) to one or many other standard/custom objects (whateverObject). E.g. log object and some other use cases. So, the record of the object should have lookup to parent object and some other info. Ideally I'm looking for something like Activities that can be linked to different objects and enabled for custom objects.

Problem: If I want to reuse the same object and link it to 20-30 other objects I have to create 20-30 lookup fields but only 1 will be used. As well as in code all the time I have to know which field to populate (get it from schema or pass as parameter).

My ideas:

1. Create 20 lookups and populate just one. Not that efficient and scalable in my opinion.

2. Add a long text field to Parent object and text field to myCustom object.

   • Insert myCustom object record (populate that text field with ID of whateverObject.

   • Update my whateverObject and add myCustom object id to text as comma separated.

   I think that is not the best way of doing things but then I don't have to worry about API names and so on. Another bad things - I won't get related lists and those TEXT ids won't be indexed.

Any ideas how I can implement something like this? This idea https://success.salesforce.com/ideaView?id=08730000000Bq7Z kinda the same. Upvoted but how to design this for now?

Best

Answer 1

One thought comes to mind that might help make this more palatable for you and easier to manage, although you wouldn't be able to easily identify which object the original lookup came from. I'm going to call the lookup objects Object_Ln where n is a an integer that represents a number for the object to as many objects as you can create lookups to (BTW, for the purposes of SOQL, 10 is probably the practical limit since you can only traverse up to 10 relationships).

You would create a junction object, Object_J that sits between what I'll call Object_C and the many other objects you want to have the lookups to. The junction object would contain ALL of the fields for the various objects you are trying to manage the lookup to. The final link would be only from the junction object to your final custom object (Object_c) which would have one field that looks up to the junction object record.

In addition to Name, you'd probably want to have 1 special field on the junction object:

• the recordId or "link" to that object record

• The Name field should be populated with the name of the object the lookup is on and thus would not be unique

That kind of approach would seem to simplify determining what the actual look-up record/object is from your custom Object_c that is ultimately linked to the junction Object_J by only one link. If you're clever about it, you should be able to pass through the final link from the custom link field you created in Object_J to a field on the destination custom object, thus requiring you to use at most two fields to accomplish what could have taken up to 20 fields as per the description in your question.

The biggest difficulty you will likely encounter is "how" you'll create the link from the junction object to the object you want to have the lookup to. This might be a good use case for a flow to help simplify the record link-up process.