Tech Tuesday : Create a New Global Variable CRM Query
When you need to create a map pulling data from a CRM query data source, and you want to limit the data pulled based on criteria. To do this, you need to create a global variable and set that value in a Map Pre Task. The first thing you need to do, is create a new global variable. I created GBL_STATE to be used as criteria for State column in CRM.
In your map pulling CRM data, create a task that runs before the map, this will set the new global variable to the state of MN. You could also add code to prompt the user to enter the criteria if needed, and set the global variable at run time. Another option in the map pre task, do a query to pull the data from another source such as a SQL table.
In the source, setup the source as CRM Query and set your organization. Click the modify button and chose your entity and columns needed. In the field you need to apply the criteria, the State/Province in this example, enter the criteria = GBL_STATE and move off that line to save the criteria
If you click the Data tab to verify this is working, you will get an error message similar to this:
The reason, the global variable has no default value when we created it, and cannot find a record with a blank state criteria.
To resolve this, you need to set the Default value in the Global variable setup shown in step 1 in this document. You can also set the default value just for this map. Click on the Global Variables tab within the SmartConnect Map, enter a default value, move off the line and click save.
Save the map and reopen the map. This will save the default value and be used to preview the data. Now when you click preview it will show you only the records where the start = MN. If you have added criteria to prompt the user for the criteria at the time you run the map, you will not be able to run that map on a scheduled basis.
Integrate & Automate without Any Code.
SmartList Data has Never Been Faster.
The Easiest Way to Report on GP Data.
Leave a Comment