Power Automate - List Rows with LookUp

(0) Share

Report

Posted on by alovalpwa

159

I'm working in a flow using Dataverse connector and List Rows action.

I have two tables Appoint and Client

In the table Appoint the column ClteId is a LookUp column pointing out to table Client (many-to-one).

In the "Select columns" option of List Rows action, I want to declare columns from table Appoint and columns from table Client.

I've wrote down a FetchXML query. If I do not include in the query a link-entity reference everything goes well.

But when I include in the query the link-entity sentence the List Rows action is trying to retrieve from the server in a loop.

See attachments

Any idea of what is wrong?

Best regards

Categories:

Microsoft Dataverse with Power Apps

att 4.jpg

att 3.jpg

att 5.jpg

I have the same question (0)

All responses (10)

Answers (1)

Prakash4691 1,332 on at

Like (0)

Report

@alovalpwa ,

Please find below fetch xml query. Test 1 and test 2 are two entities. Test 2 has lookup of test 1. For below query, I am getting test 2 name from lookup.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="cr5b9_test2">
<attribute name="cr5b9_test2id"/>
<attribute name="cr5b9_name"/>
<attribute name="createdon"/>
<order attribute="cr5b9_name" descending="false"/>
<link-entity name="cr5b9_test1" from="cr5b9_test1id" to="cr5b9_test1" visible="false" link-type="outer" alias="a_a08f4df0d2fceb1194ef6045bd72e732">
<attribute name="cr5b9_name"/>
</link-entity>
</entity>
</fetch>

As I mentioned earlier, try to formulate fetch xml from advanced find or fetch xml builder from xrmtoolbox.

If it answers your question, kindly give kudo and accept it as solution.

Regards,

Prakash

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Hi @alovalpwa,

Couple of things I noticed:

Your FetchXml looks good apart that you're missing the alias property in the link-entity element. Add a friendly alias and then the results from the link entity will return in the format "aliasname.columnphysicalname"

You also applied a filter in the filter box. I don't think both are supported (I may be wrong). Apply the filtering directly in your FetchXml instead since you using FetchXml.

Hope this helps!

Was this reply helpful? Yes No
alovalpwa 159 on at

Like (0)

Report

@EricRegnier
I still have the same issues, the list row action retries and get internal server error in a loop.
The filter expresion is "addHours(startOfDay(utcNow()),-2)".
How could I write it down in FetchXML query?
Regards

Captura de pantal...

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Yup if I understand the logic correctly you can use the last-x-days operator. Example:

<fetch> <entity name="account" > <filter> <condition attribute="createdon" operator="last-x-days" value="2" /> </filter> </entity> </fetch>

Note: all dates are already stored as UTC in Dataverse.

Also, your initial FetchXml was cleaner, you can omit all the properties in the <fetch> tag and remove the visible property from the link-entity tag.

Hope this helps!

Was this reply helpful? Yes No
alovalpwa 159 on at

Like (0)

Report

@EricRegnier @Prakash4691
I have found the mistake in my fetchxml query (in the from of link-entity).
But now I'm not able how to reference the "alias.cltefirstn" column in the CSV table.
See attachments

Captura de pantal...

Captura de pantal...

Was this reply helpful? Yes No
Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report

You need to reference the property with an expression, example:

Expression format: items('apply for each name')?['alias.cltefirstn']

Was this reply helpful? Yes No
alovalpwa 159 on at

Like (0)

Report

@EricRegnier
It was hard but I've got it.
And now my last question for this time.
My target is to create a text file separated by semicolons .
I don't see how to change that when I create the CSV table.
Any advice?
Thank you very much

Captura de pantal...

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Haven't fully tested but you can try Replace "," with ";" in a Compose action. Expression:

replace(body('Create_CSV_table'), ',', ';')

Was this reply helpful? Yes No
alovalpwa 159 on at

Like (0)

Report

@EricRegnier
It does not work

Was this reply helpful? Yes No
alovalpwa 159 on at

Like (1)

Report

@EricRegnier
Sorry.
Yes it works.
Perfect
Thank you very much

Was this reply helpful? Yes No