5

Contacts have Specialities (skills) to sell so I have a Master-Detail from Contact to Speciality. We sell Product (1/2 day, full day) for those Contacts so the Product has a lookup to the Contact. Speciality <= Contact => Product.

Now I want to search for Products based on Specialities. I can get from Product to Contact but I can't get from Contact to Speciality because it is a one-to-many relationship. What I'd like to do is denormalise the Contact => Speciality relationship and put some of the information into a single ';' separated string. A bit like a multi-picklist. (BTW, multi-picklist won't work for the Speciality because there is other information in the Speciality record not required for the search.) If I could denormalise it I could just do a text search on the values.

Any ideas welcome.Contact-Speciality ERD

4
  • Do you have a really basic image of the ERD for this data model? An ERD would go a long way to determining if you've got a model between these objects that will support your needs before the community suggests a solution based on what your paragraphs imply is going on in the model.
    – Mark Pond
    Jul 2, 2015 at 2:05
  • If it is helpful, what it appears that you are describing that already exists in your data model is on the top of this image and what it sounds like you need to meet your requirements is drawn on the bottom.
    – Mark Pond
    Jul 2, 2015 at 2:34
  • 1
    Are you trying to solve this problem with zero custom development? (Specifically: VisualForce and Apex)
    – Mark Pond
    Jul 2, 2015 at 3:08
  • I was hoping to avoid custom development and was more interested to see if it could be done. I don't like duplicating data if I can avoid it. But it is definitely an option. Thanks.
    – Greg Shaw
    Jul 2, 2015 at 6:01

2 Answers 2

0

I wanted to comment on Mark's comments, but I can't so...

Using Apex Code you can add a trigger to Speciality and update a field on the Contact called say "Specialties__c" where you simply append ; and the newly added Speciality. You'll have to be careful to have the trigger handle delete/undelete/update/insert because you'd want to remove specialties if they were deleted or add them back if undeleted, etc. I've done this before, careful about duplicates. If you update a Speciality it shouldn't add itself again, you'll you have to split the custom field and look through it to ensure there aren't duplicate entries, etc. And if you had 2 of the same speciality and delete one, you may need to be careful not to remove that one. Lots of edge cases on this one. Careful about data volumes as well, if you're mass updating Specialities or if a Contact can have a huge number of Specialities these might throw some wrenches in there.

Another option may be a Joined Report. If you had a report of Contact with Product and Contact with Speciality you could join them on Contact. This isn't as easy as a Product.Contact.Specialities contains X type filter. But you might be able to filter the Contact with Speciality report and join it to the unfiltered Contact with Product report and hopefully come to the same conclusion. I find that you have to played with Joined Reports some to get them where you want, but something to try.

Help Doco on Joined Reports https://help.salesforce.com/apex/HTViewHelpDoc?id=reports_joined_format_concepts.htm&language=th

1
  • As I just commented to Mark I'm hoping to do this without custom development but it may be the only way. Thanks.
    – Greg Shaw
    Jul 2, 2015 at 6:02
0

I've just finished by DEV501 training and it turns out the answer is that I should be querying from a different point.

My attempt to denormalise the data was related to my expectation that I needed to query the Product object. But that meant that I couldn't get to the picklist on the Speciality object.

The solution was to query the Contact object with visibility over the Product and Speciality objects. This will still give a set of Product results but without the need for the denormalisation.

Thanks, Greg.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.