In my last post I showed how to create a multiselect lookup. While this feature is pretty cool, when adding the field to a view, the data is shown as an array of objects which will not work from a readability standpoint. In this post I will show you how to convert an array of objects to a comma separated string using Power Automate so that the data can be displayed in a view.
You can see from the image on how the accounts are displayed when adding the new multiselect lookup to a view. Of course, this will not be feasible. It would be much better to have the accounts displayed as Account 1, Account 2, Account 3…
Of course, there are several ways to do this, however, I am going to show you how to convert the array of objects and store them in a readable comma separated sting in a new column using Power Automate (Flow). The final result will have a multiselect lookup on the contact form and a new column called companies displaying in the contact view in a readable format.
Before we get started with the Flow, you will first need to create a new column that will store the reformatted account names. So, create a column called Associated Accounts, set the data type to multiline text and the maximum length of characters under advanced settings should to 4000 characters.
Next, add the Associated Accounts column to the Active Contacts view
Create a Flow to convert the array of objects to a comma separated string
Now that we have the basics set up, we can now move onto creating the Flow that will convert the selected accounts into a readable format.
First, you will need to copy the name of the account’s column from the contact table as your will need it for the next step.
Create a new automated cloud flow and select Dataverse for your trigger. Set the change type, label name and scope as per the image. Finally, paste the accounts field name into the select columns field. This tells the flow to only trigger when the accounts field is modified.
Add a Get a row by ID action and set the Row ID to the dynamic value of Contact.
Add a compose action and set the input the dynamic value for Accounts. Be sure to select the dynamic value from the Get a row by ID action.
Add an Initialize variable action. In the step we will take the output from the compose action and convert it to an array of JSON objects. You may be wondering why since the data already looks like it is formatted? The issue is the data output from the compose action is of type string and we need it in an actual array so that we are able to reach each object within the array. To set the value, you will need to write an expression.
Before you move onto the next step, you need to run a test on your flow and copy the output from the variable. We will use the output to define the schema in the next action. On the top right of your screen, click on Save, then click on test. Wait for the test session to start, the edit on of the contact records by associating a few accounts. Once you click on save, your Flow should fire.
Now that your Flow has successfully run, you need to copy the output from the variable as you will need it to define the schema for the next action. Once the Flow has run, click on the variable to view the output and copy all the contents.
The next action we will add the Parse JSON action. This action will convert the sting of accounts into a format called JSON so that we are able to reach each of the account names from the object.
Next you need to click on Generate from Sample. when the dialog box opens, paste the output from the variable in the previous step into the box the click on done. This will generate the schema required to convert the variable output the JSON.
For the next action, you will initialise another variable, this will be to hold the newly comma separated account names. Add the action, initialise variable and set the properties as per the image.
In this step, you will loop through each of the objects and extract the name value followed by appending each name to the var_associatedaccounts variable. Add an Apply to each action and a compose action within in Apply to each. Set the properties as per the image.
Continuing on from the previous step, add an Append to string variable and set the name to the associated accounts variable (var_associatedaccounts). For the value, you will need to write the following expression.
concat(outputs(‘compose_2′),’, ‘)
This expression is what creates the comma separated account names.
Finally for the last action you will now update the contact record with the newly created comma separated account names. Add a Dataverse Update a row action. Make sure to add the action outside of the Apply to each as shown in the image. Set the table name to contacts and the row id to the dynamic value of Contact (this is the record id for the contact).
And that’s a wrap. you have managed to convert an array of objects to a comma separated string using Power Automate. Save and test your Flow.
The final result
The final result of this solution provides a way for a user to select multiple accounts on the contact record and then using a flow you remote the output into a readable format that can be added to a view. In essence, you have two columns, one for a form and one for a view.
I hope you found this article helpful.
Until next time, happy CRMing!
