locked
Need outer join to gather marketing list members RRS feed

  • Question

  • Marketing came to me with the request to be able to have a marketing list for contacts without a product. Seems like a relatively common user story so I dug in. I can't seem to find an easy way to do this.

    Advanced fine doesn't natively handle outer joins via fetch

    The custom System View I created and imported with the outer join can't be referenced from Advanced Find builder of the add members dialog of the marketing list, but it does work as expected when selected

    I could page through the system view selecting all and adding the users manually, but that is over 2500 records and as people bought stuff would need to be maintained.

    A workflow might be an acceptable solution but that would require me maintaining a dedicated field for each marketing list they want to create, which sounds like an on going headache.

    Is there a good solution here?

    Tuesday, July 8, 2014 3:50 PM

Answers

  • Yes I agree for the case presented that is pretty straight forward, if not somewhat cumbersome... However, the real world is a little more complicated and we were having trouble removing all of the right members since there were some other considerations...  Also, we are a small company and we can't have marketing spending too much time on static list maintenance. I ultimately decided on a scheduled workflow to process the data, but wasn't overly enthused about the field level maintenance that it would entail so I ran an end around.

    I created some helper entities to help track these memberships, I'm not creative so I called them Tag and TagItemLink.   Each tag link can be associated to a tag and a contact, account OR Lead. I'm using business rules to enforce that.

    I have one workflow that can be run on demand for each primary customer entity kicked off via . I'm using the Workflow Field Lookup tool from codeplex (http://workflowfieldlookup.codeplex.com/) to execute the fetch for each entity to resolve whether a given tag should be associated with the record or not. I then look for the tag to see if it already exists or not and add, remove or take no action as appropriate. 

    I'm currently using the MsCrmtoolkit (http://mscrmtoolkit.codeplex.com/documentation#workflowexecutionmanager) to schedule the workflow execution for off hours. Initial testing had it in the 40 minute execution time on about 15000 records.

    Another tool I like is the Fetch XML Tester in the XRMToolbox (http://xrmtoolbox.codeplex.com/releases/view/119275) for writing and testing those outer join statements we seem to need frequently.

    Now all of the marketing lists can use these pre processed tags from dynamic lists, marketing can create their own tags as needed and if we can bring them up to speed on the Fetch xml I can pretty much set and forget hopefully.

    While I definitely think the Advanced find UI should handle outer joins, I am just expecting they didn't get to it yet and it will show up sooner or later. Until then we are mostly happy, but I wouldn't mind scrapping this workaround.

    • Marked as answer by Bill Lucas Wednesday, July 9, 2014 2:21 PM
    Wednesday, July 9, 2014 2:21 PM

All replies

  • You should learn to use "Add to marketing list" and "Remove from marketing list".

    You are absolutely correct.. marketing list cant do outer joins, but you can manage them manually.

    So in this case you will want to add all contacts to the list to begin with..

    Then remove all the contacts that have that specific product.

    The end result is the people that doesnt have the product.


    Rune Daub Senior Consultant - Dynateam CRM http://www.dynateam.dk

    • Proposed as answer by RuneDaub Wednesday, July 9, 2014 11:05 AM
    • Unproposed as answer by Bill Lucas Wednesday, July 9, 2014 1:23 PM
    Wednesday, July 9, 2014 11:05 AM
  • Yes I agree for the case presented that is pretty straight forward, if not somewhat cumbersome... However, the real world is a little more complicated and we were having trouble removing all of the right members since there were some other considerations...  Also, we are a small company and we can't have marketing spending too much time on static list maintenance. I ultimately decided on a scheduled workflow to process the data, but wasn't overly enthused about the field level maintenance that it would entail so I ran an end around.

    I created some helper entities to help track these memberships, I'm not creative so I called them Tag and TagItemLink.   Each tag link can be associated to a tag and a contact, account OR Lead. I'm using business rules to enforce that.

    I have one workflow that can be run on demand for each primary customer entity kicked off via . I'm using the Workflow Field Lookup tool from codeplex (http://workflowfieldlookup.codeplex.com/) to execute the fetch for each entity to resolve whether a given tag should be associated with the record or not. I then look for the tag to see if it already exists or not and add, remove or take no action as appropriate. 

    I'm currently using the MsCrmtoolkit (http://mscrmtoolkit.codeplex.com/documentation#workflowexecutionmanager) to schedule the workflow execution for off hours. Initial testing had it in the 40 minute execution time on about 15000 records.

    Another tool I like is the Fetch XML Tester in the XRMToolbox (http://xrmtoolbox.codeplex.com/releases/view/119275) for writing and testing those outer join statements we seem to need frequently.

    Now all of the marketing lists can use these pre processed tags from dynamic lists, marketing can create their own tags as needed and if we can bring them up to speed on the Fetch xml I can pretty much set and forget hopefully.

    While I definitely think the Advanced find UI should handle outer joins, I am just expecting they didn't get to it yet and it will show up sooner or later. Until then we are mostly happy, but I wouldn't mind scrapping this workaround.

    • Marked as answer by Bill Lucas Wednesday, July 9, 2014 2:21 PM
    Wednesday, July 9, 2014 2:21 PM