cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alovalpwa
Advocate I
Advocate I

Power Automate - List Rows with LookUp

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

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

EricRegnier_0-1648669099888.png

 

View solution in original post

10 REPLIES 10
Prakash4691
Super User
Super User

@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

EricRegnier
Super User
Super User

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!

@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 

EricRegnier
Super User
Super User

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!

@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

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

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

EricRegnier_0-1648669099888.png

 

@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

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

 

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

 

 

EricRegnier_0-1648672943971.png

 

@EricRegnier 

It does not work

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (1,859)