Answered

Approvals, get data from 2 tables

(0) Share

Report

Posted on by Community Power Pla...

I am working on a approval flow. I have 2 SQL tables like this:

I have a trigger "When a new item is created" in sql. Trigger is for table TABLE1.

How would I setup a condition that checks: if new row is created in TABLE1, find matching rows with TABLE1.ID=TABLE2.ItemID,

and then present this rows to a user for a approval like this for example if row ID 3 in TABLE1 is new row(text in bold is from tables)?:

------------

Joe has submited an approval on Date: 2021-27-03.

Items:

Fuel, 20, 1.4

Oil, 2, 1.7

Please approve/reject.

-------------

I have tried doing it with Apply to each, and conditions but cant seem to get all the information in one approval request, I get sent seperate requests for seperate lines in tables. In this example i get 2 requests, but should get everything in one.

Categories:

General topics

I have the same question (0)

All responses (14)

Answers (3)

Verified answer

DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Anonymous

From your trigger, you should get table1.id. Then using list rows on table2, you can filter rows on
ItemID eq 'reponsefromtriggerofTABLE1.ID'

Insert reponsefromtriggerofTABLE1.ID dynamically from the expression builder and switching tabs to dynamic content.

Whilst this is not exactly the same I have used a list as a lookup based on data retrieved from a MS Form. Take a look here

When you are retrieving rows from SQL, even if you confidently retrieve 1, PowerAutomate will attempt to put you in to an uneccessary apply to each as it expects an array as a result. You can see this in my demo. You want to retrieve the first element from your list rows. Whilst this isn't a must it makes things more efficient.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Copy link

Link copied!

@DamoBird365 thank you for your answer.
Im not sure how to do this. I am getting 2 seperate approvals, if I for example insert 2 rows in my table LineItems.
But would like to get these 2 rows somehow in 1 approval. I think I would need some sort of array for this?
I tried it like this:

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Anonymous

If you are getting two approvals, the result of your get rows must include two rows that match your filter. Technically you don't need the condition as you have already limited the results from the get rows filter. If you look at the history of the get rows you will see the two rows being returned.

If this is due to testing that you have duplicates, do a wee tidy up. The get rows actions has a top rows limit, you could set that to 1, but ideally you want to understand why you are getting two results back.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Copy link

Link copied!

I have removed condition. And get correct rows in Get rows(V2) 2.

Now I need columns from these 2 rows, and 1 row from first Get rows(V2) included in ONE approval request.
If I include in approval request columns from Get rows(V2) 2, I get automaticly inserted Apply to each. And then I get 2 emails with 2 Approval request.

Maybe I should make something like HTML table with all rows, in approval request... Or get these 2 rows in an Array, that can be used in one request... Sorry im really banging my head in the wall, trying to do this 😄

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Anonymous

If retrieving two rows is deliberate and you want to obtain something from object 1 and something from object 2 for 1 approval then you could create a select or a compose on those items.

You can call objects from an array by number starting from 0.

Something like:
outputs('List_rows')?['body/value']?[0]?['description']
outputs('List_rows')?['body/value']?[1]?['description']
Would get you the two descriptions from the results of your get rows.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Copy link

Link copied!

@DamoBird365

Compose function looks great and would get me what I want.

Is there a way to automaticly list/format all rows from Compose to Approval details? I can have 2 rows returned, and sometimes 10 etc.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

@Anonymous

You can just use the select action. It would return all descriptions as an array using the value of the body as a source. Either with a key value or as a list in an array, enter advanced mode for select and simply select the dyanmic value.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Copy link

Link copied!

@DamoBird365
I have pu expression in the Details of Approval. Im getting error for: outputs('Compose')?['body/value']?[0]?['Description']

raw outputs for compose are:

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @Anonymous

Looks like you have put the results of the get rows into a compose and then performed an expression on a compose? You just want to perform the expression on the get rows action as it is already an array. The compose will create a string.

Your other option would be to use createarray(outputs('Compose'))?[0]?['Description']

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Copy link

Link copied!

Im really sorry but I cant seem to work this out. Would you be willing to recreate my project in your PowerAutomate, and then post screenshots of the setup?

Was this reply helpful? Yes No