Sometimes in Workato, you may have an array that you want to combine into a single string to move it to a field. For example, you've run a search in google sheets using a query which has given you multiple results, and you want to send that entire list via email. If you were to use the repeat step, that would mean that you would receive one email for each result, and that's not ideal. Thus, we will show you how to use the format_map formula in such a use case.
Here we have a sample list from google sheets
| Company Name | Order Quantity | Status |
| Alpha | 10 | Completed |
| Bravo | 20 | Completed |
| Charlie | 30 | Completed |
| Delta | 40 | In Progress |
| Echo | 50 | In Progress |
| Foxtrot | 60 | In Progress |
| Golf | 70 | In Progress |
| Hotel | 80 | In Progress |
In the above example, we have a recipe that runs every week to run a search on google sheets for statuses showing "in progress" to generate a report. When you run a search, the data would be given as an array, as indicated by the stack symbol next to "Rows" (Shown below circled in red)
This means that rows will contain the following array, based on the condition of the search
| Company Name | Order Quantity | Status |
| Delta | 40 | In Progress |
| Echo | 50 | In Progress |
| Foxtrot | 60 | In Progress |
| Golf | 70 | In Progress |
| Hotel | 80 | In Progress |
Next, we move to the email step. So we want the email to contain all the names of companies whose status is "in progress". We'll take at what format map does to this. Basically, the formula creates another array of strings by formatting each row of a given array.
The text between the quote marks (') determine what will the string look like in each line of the array. The text within the %{ } is a placeholder for the data in each field, and basically acts like a data pill. It needs to be filled with the exact field API name. You can find out the API names of the data fields by looking at the job report. In a job report, the data fields are represented by their exact, case-sensitive API names. In this case, Company Name becomes company_name. Using this formula, this is what the new array looks like:
| Company Name: Delta, Order Quantity: 40 |
| Company Name: Echo, Order Quantity: 50 |
| Company Name: Foxtrot, Order Quantity: 60 |
| Company Name: Golf, Order Quantity: 70 |
| Company Name: Hotel, Order Quantity: 80 |
as you can see, each row has been converted to a single string, based on the formatted order. To further combine this whole thing to a single string, you can use the .join("\n") command, and this will "join" each row with a new line in your email. There you have it! You have successfully used format_map!