cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Szabolcs
Helper I
Helper I

Filter by lookup field

Hi,

 

I have the following entity structure in CDM:

HeaderEntity {Title (Text), Date (Date), State (Enumeration)}

LineEntity {Title (NumberSequence), Reason (Text), Header (Lookup to HeaderEntity)}

 

I have a Screen with two part. At the top there is an EditForm with DataSource: HeaderEntity. At the bottom there is a Gallery controll where I want to show all the LineEntity records according to the EditForm's record.

 

I could not figure out how to reference to the actual record of the EditForm. Now I have a Context Variable (ActualRecord) with this record. Maybe this variable is unnecessary.

 

I wrote into the Gallery.Items property: Filter(LineEntity, ActualRecord =LineEntity.Header) but I got an error message: "Invalid argument type.".

 

How to filter the LineEntity?

 

Regards,

Szabolcs

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Can you please try this instead?

 

    Filter(LineEntity, ActualRecord.Title = Header.Title)

 

In a nutshell, the expression you see after the ',' is evaluated for each row in LineEntity. In that row scope, "Header" has a special meaning: it refers to the field with that name, at each row. Assuming you have a context variable ActualRecord that is a row from HeaderEntity, and the uniquely identifying field in that entity is "Title", you can compare the titles above in your filter. If there is a different field that uniquely identifies the row in HeaderEntity, other than Title, please feel free to use that instead.

 

As a side note, regarding your question on "LineEntity.Header" -- the type of this table slicing formula is a one-column table where each field is a record. As you correctly observed, it is not possible to compare such a structure with a simple scalar value. Apologies, I originally misunderstood your requirements.

 

I hope this helps.

 

Radu Gruian [MSFT] ** PowerApps Staff

View solution in original post

4 REPLIES 4
rgruian
Power Apps
Power Apps

The '=' operator allows you to compare individual values (text, numerics, bools, etc) but not aggregates (records, tables). You may want to compare with respect to specific fields within those records, for example an ID that they share. For example:

 

    Filter(LineEntity, ActualRecord.ID = LineEntity.Header.ID)

 

 

Radu Gruian [MSFT] ** PowerApps Staff

Hi Radu,

 

Thanks for your answer and sorry for my late response, I was off for a week.

 

I have tried your syntax but it does not work. The ActualRecord is a record from the HeaderEntity table. According to it when I press the "." operator after ActualRecord I can see all the fields from this table and I can select Title or RecordId. But when I press "." operator after the LineEntity.Header I can not see any fields and after I type the fieldname manually I get error message: "InvalidName" on field name and another error message on "=" operator with "Invalid argument type".

 

I do not understand what is the type of the "LineEntity.Header" expression. Is it a reference id of the header record or the referenced record itself? If just a reference id why can't I compare with the ActualRecord.RecordId? If the record itself why hasn't it fields?

 

What's wrong with it?

 

Regards,

Szabolcs

 

Can you please try this instead?

 

    Filter(LineEntity, ActualRecord.Title = Header.Title)

 

In a nutshell, the expression you see after the ',' is evaluated for each row in LineEntity. In that row scope, "Header" has a special meaning: it refers to the field with that name, at each row. Assuming you have a context variable ActualRecord that is a row from HeaderEntity, and the uniquely identifying field in that entity is "Title", you can compare the titles above in your filter. If there is a different field that uniquely identifies the row in HeaderEntity, other than Title, please feel free to use that instead.

 

As a side note, regarding your question on "LineEntity.Header" -- the type of this table slicing formula is a one-column table where each field is a record. As you correctly observed, it is not possible to compare such a structure with a simple scalar value. Apologies, I originally misunderstood your requirements.

 

I hope this helps.

 

Radu Gruian [MSFT] ** PowerApps Staff

Hi Radu,

 

Thanks a lot! It works well.

 

After I removed the "LineEntity." from my three part identifier and used just "Header.Title" it worked.

As I see the only field that I can use behind the lookup type field is the "Title", which is absolutely ok for me.

 

I will never forget to use only two part identifier within Filter().

 

Regards,

Szabolcs

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,821)