Document Generation With Omnistudio | Data Mappers, Formula examples
If you're just getting started with OmniStudio Document Generation and Data Mappers, this guide will walk you through the basics to help you create your first Data Mappers and generate documents using merge fields.
I'll share an example, showing how to transform extracted data into a format ready for Word documents. Since finding information on the internet on this topic can be challenging and limited, I'll also highlight key insights that I had to figure out myself. Let’s dive in! 🚀
Data Extraction
Data Mappers with the Extract type are used to retrieve information from a specific object. There are two types: Data Mapper Turbo Extract and Data Mapper Extract, each serving distinct purposes in data processing.
Omnistudio Data Mapper Turbo Extract
Data Mapper Turbo Extract pulls data from a single Salesforce object while allowing the inclusion of fields from related objects. It enables data filtering and field selection by mapping related objects. However, it does not support formulas, custom JSON, default values, or transformations.
Omnistudio Data Mapper Extract
Data Mapper Extracts retrieve Salesforce data and output results in JSON, XML, or custom formats. They allow data filtering, field selection, and support for formulas, default values, and translations. These extracts supply the necessary data for OmniScripts, Integration Procedures, and FlexCards.
Extract
When you specify the desired data extractor, you will be directed to this page, where the first step is to extract data from the selected object.
This initial step functions like querying data from Salesforce, determining the information that will appear in your document.
Here is one example with Account:
As you can see from example you can also query other Objects, and you can filter the result by adding AND/OR/LIMIT/OFFSET/ORDER BY as you would do in soql queries.
At the right you can see the JSON structure that your result will have, you can select show all sObject fields checkbox to get view of fields as well.
Formulas 🧮
Formulas in OmniStudio differ slightly from those used in standard formula fields for example. Their structure is a bit different, but my goal is to show you various formula examples. Since documentation is limited, I had to figure out some of them on my own.
The format of IF statement in Data Mappers is very similar to one in field formulas, the difference is that the field is referenced with 2 colons instead of one:
IF(objectName:fieldName expression, "trueResult", "falseResult")
Note ** objectName is the name defined in extract step, so in our case it would be:
account, or contacts
Here are some examples of formulas in Data Mappers:
Here are some date formulas that I needed to use in my previous project:
Current Year, Current Date, Current Hour
Current Year => YEAR(TODAY())
Current Date => YEAR(TODAY()) + '-' + IF(MONTH(TODAY()) < 10, '0' + MONTH(TODAY()), MONTH(TODAY())) + '-' + IF(DAY(NOW()) < 10, '0' + DAY(NOW()), DAY(NOW()))
Current Hour => SUBSTRING((FORMATDATETIME(NOW()),7)
Formula for sum of all products
SUM(Products:Price)
Output
When you click on the output tab what you should do first is to write down the expected output, this is needed in order for you to map the input values(extract) to desired outcome variables.
From our example from above this can be one of desired outputs:
When you enter Expected JSON Output you can now match input values by clicking 'Quick Match'.
There you will have a look of input and output mappings. Note that the formula result 🧮 will also be in input mappings, so make sure when you use formula to select that input value:
Mappings will look like this in our scenario:
Once you do that you will be able to see mappings in output tab:
One very useful tip is that when you click on mapping, you can set the default value of the field, but you can also transform map values:
For example I used this in situation where I needed to translate the picklist values, in the Key input field I wrote done the picklist value, and on the value part I put the translated value.
Preview 👀
Here is the place where magic happens ! ✨
Preview is perfect for testing your outputs with real values. You just need to add new Key/Value pair and Execute the response !
From our example since
Data Mapper Transformer
In short, transformers have a role to transform the data collected by extractors so that it can be used in Word documents as merge fields.
When you create it you will see that now you have 4 tabs:
Formulas, Transforms, Options and Preview
I will now just cover the part with transforms since the same goes for formulas and preview as in extractors.
Transforms
Used to transform the data collected by extractors.
In here first here that we need to do is to set expected JSON input, and that will actually be the expected output from extractors(if no additional formulas provided).
After this you should enter the expected output. This will be the text that is shown as merge field in your document so make sure to write proper name corresponding to your document.
As if we want to show an Account with multiple contacts the syntax in Document will be like in the example:
Some of the word tokens:
merge fields are inside {{ }}
# is repeating element used like a loop
/ is end of the repetition
Auto Match goes the same as in extractors. You will be given recommended inputs and outputs and if it fits your need, you can just click on Auto Match.
And you will be given the list of pairs:
After successful mappings you can now preview 👀 your input/outputs by clicking preview and just executing the Input value. ✨
Conclusion
Mastering OmniStudio Document Generation and Data Mappers opens up a world of possibilities for automating and personalizing your workflows.
By understanding how to extract and transform data, you can seamlessly integrate it into your documents, making your processes more efficient and tailored to your needs.
With practice and a deeper dive into these tools, you'll unlock even greater potential for streamlining your operations and delivering dynamic results. Happy mapping! 🚀
Resources: