Get data from two tables in to one output table

[StephenRoberts]

Hi,

I'm trying to create a flow that automatically sends an alert to all users with contacts that have surpassed their review date. On the contacts table, there is an owner ID, however no owner display name or email, this is all within a separate User table. 

I want to pull data from both the Contact table to identify which contacts have surpassed their review date, and also pull data from the User table, to link the owner ID from the contact to a Display name, and their email address.

What's the best way to do this? Currently my flow looks like this, I've looked at the Join function however I'm not sure this is what I need? 

Thanks

Steve

[StephenRoberts]

When I attempt to use the join function, I get the below error message: Invalid Template. Unable to process template language expressions in action 'Join' inputs at line '1' and column '3091': 'Error setting value to 'From' on 'Microsoft.Azure.Workflows.Data.Operations.JoinActionInput'.'.

[StephenRoberts]

[v-litu-msft]

Hi @StephenRoberts,

The Join action is to convert an array to a string that delimits by a specified character, not used to merge two arrays.

If you want to merge two entities in Dynamic, you should initialize an array variable and restructure the array, please following these steps:

1. Initialize an array variable to store the data;

2. List records to get these contact met conditions, in my screenshot I did not add any filter query, if you need it you should add;

3. Use Apply to each to loop these contact, then add a second List records action to find the corresponding user:

systemuserid eq '@{item()?['_ownerid_value']}'

4. Append data get in the previous step into the array, you could add the field you want.

5. Create HTML table by using the array variable.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StephenRoberts]

Hi,

Thanks for your swift and detailed response 🙂

I've attempted to follow this flow, however I get the following error when trying to create the array variable before creating the HTML table. Please see below screenshot. I feel this is because I'm not using the the correct values? Which Value should I use in the out from previous step field, I assumed it would be the list from the contact entity as that has the contact full name and owner described within the variable field?

Your help is appreciated

Thanks

Steve

[v-litu-msft · Accepted solution]

Hi @StephenRoberts,

Please add a comma (",") to split each key-value pair:

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[StephenRoberts]

Hi,

in the append to array variable value field, is the full name from the Contacts Entity or the User Entity?

When attempting to create the HTML table, an apply to each action is automatically created

I attempted to test this run of the flow however it was stuck running for 5 hours before I cancelled out.

Thanks

Steve