Power Platform Community Forum Thread Details

Here is your step-by-step and the answer to match location with Orlando, FL 🙂

If the steps below are not clear enough or if your process is a little different from the example, let me know and we work together to make the necessary changes.

Lists overview

We are working with a 'customers list', from where we will extract the 'office' data to find the contact info:

The contact info is stored in this other list:

As you can see, the customers list contains a kind of numeric code before the city name ("xxx -"), while the office list has only the city name. I'm assuming that all of your 'customer' records will follow this pattern: if this is not the case, we may need some small changes in the flow design.

Step 1 - Create the trigger and extract the office city

First, add a 'When an item is created' trigger and populate it with your List details. After that, initialize a string variable to store the office city:

In the 'Value' input, we are setting an expression that will access the content from the 'office' field of the new record added to your list, but extracting only the text allocated after the "-". The expression is the following:

trim(split(triggerOutputs()?['body/Office'], '-')[1]). Here is a quick explanation about each element of this expression:

triggerOutputs()?['body/Office']: this is the dynamic content related to your 'Office' column from the customer list. You don't actually need to type it, but just add it as dynamic content. To add a dynamic content inside an expression, add some text to you 'Fx' input (highlighted in yellow), keep the cursor focused into the input and then navigate back to the 'dynamic content' tab (highlighted in green) to select the desired content highlighted in blue):

'-': this is the separator that we are using to isolate only the last part of '555 - Orlando, FL'
split: this is the expression that actually will return to us 'Orlando, FL', from '555 - Orlando, FL'. We are passing two parameters to it: the text to split (triggerOutputs()?['body/Office']) and the separator to define where the text must be broken ('-'). This expression will return an array, which will look like this: ['555 ', ' Orlando, FL']
[1]: as mentioned before, the split will return an array, and we need to access its second element (Orlando, FL). To access elements from arrays into expressions, you need to pass it index between brackets, and as Power Automate arrays are zero-indexed (the counting starts at zero), the second element will be [1].
trim: as we have a blank space between '-' and 'Orlando', the final result of the expression until now will be ' Orlando, FL' (with an extra blank space at the beginning). If we compare it to the value existent in the office list, we won't find any result, as this extra blank space make the both strings different. So we use the trim expression to remove any blanks from the beginning of from the end of the text.

If you are new to build expressions or to include dynamic contents into them, refer to this article that I recently wrote about it (and, of course, ask here if you need additional clarification): http://digitalmill.net/2023/07/31/using-dynamic-content-in-power-automate/

Also, there is this other article about text expressions that brings some instructions about split, trim and array indexing: http://digitalmill.net/2023/08/12/how-to-extract-and-clean-texts-with-power-automate/

Step 2 - Return the office contact info

Now we need to find the contact info among the records into our office list. We can achieve it by adding a 'Get items' action and including a filter query to restrict the retrieved records to only those which office city (in my List, the 'Title' property) matches to our 'office' variable:

You just need to populate your 'Get items' action with the details from your office list and, into the filter query (highlighted in yellow), write Title eq '[VARIABLE_DYNAMIC_CONTENT]'. Make sure to change 'Title' for the column name that is storing your office location. Also, make sure to surround the dynamic content from 'office' variable with single quotes (otherwise, you will receive an error).

It will return an array containing a single record (unless you have more than one record for the same city).

Step 3 - Populate your email

For this step, I will use a Microsoft Teams message step, but you can use the same logic for an Outlook email one 🙂

Just insert your email action and populate it with the dynamic content extracted from 'Get items' action. Once you include a first dynamic content, it automatically will generate an 'Apply to each' action. Don't worry: as the 'Get items' output is an array, this is the expected behavior from Power Automate, and as it has only one record, the 'Apply to each' won't result in dozens of repeated messages:

Output

Let's test the flow! I included this new record:

The flow ran successfully:

And I received the Teams message (only one) with the content populated from the office list:

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/