Answered

Display one to many relationship in Canvas Apps

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I am just a beginner user, but decided to build a test app to explore the capabilites of CDS and Powerapps. I build two tables in CDS with a one to many relationship "Test Company" (Company Name) One>>>Many "Test Sales" (Company Name)... See attached:

CDS Test Company RelationshipCDS Test Company EntityCDS Test Sales Entity

So with all this defined I though would a create a simple canvas app with two galleries. One for "Test Company" and one for "Test Sales" and the idea would be to click on "Test Company" gallery and filter "Test Sales" gallery. However the "Company Name" in "Test Sales" does not appear to be visible. Any ideas as why this is???

CDS PoweraApp Form.jpg

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (1)

mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!
@Anonymous
I am not an expert on CDS but I have two ideas:
Can you please share what error message you are receiving?
CompanyName appears to be a LookUp column. Type 'Company Name'. with the period and tell us what autocomplete options come up.

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

Like (0)

Report
Copy link

Link copied!

Thanks mdevanev,
This works if typed into the items section of Test Sales Gallery....
Filter('Test Sales','Company Name'.'Company Name'=Gallery1.Selected.'Company Name')

For the life of me I am not understanding why it works, but it works.

Jason

Was this reply helpful? Yes No
Verified answer

mcolbert 126 on at

Like (0)

Report
Copy link

Link copied!

@Anonymous

When using related entities in CDS, you can filter by referencing the unique identifier (GUID) that CDS creates for each row. On the many side (Test Sales) there will be a reference to the parent entity (Test Company).

Look at your entity fields and look for the field with the data type of Unique Identifier,. In your example, you likely have a filed called Test Company, that is of type Unique Identifier, named crdf5_testcompanyid. This is referenceable in the filter on Test Sales based on the relationship you created.

Using the display name for the fields your filter would look like ...

Filter('Test Sales', 'Test Company'.'Test Company' = Gallery1.Selected.'Test Company')

Using the field name your filter would look like ...

Filter('Test Sales', 'Test Company'.crdf5_testcompanyid = Gallery1.Selected.crdf5_testcompanyid)

Hope this helps!

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like (0)

Report
Copy link

Link copied!
@Anonymous
In response to your question it is because LookUp is a complex column type. This means the field stores several key-value pairs. For example, a single entry within your Company Name column would look something like this.

{ ID: 4560-AS02-F03A-B930, 'Company Name': "ABC Company", Field1, "some text data" Field2, 12345 }
To access data within the LookUp column we reference the column name followed by the key name

'Company Name'.Field1
This would return the value

"some text data"
If we've solved the problem here please close the thread to let us know.

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

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

Like (0)

Report
Copy link

Link copied!

Thanks mdevaney,
You have no idea how many other problems (questions) that answered.

Regards

Was this reply helpful? Yes No
Gyllentid 894 on at

Like (0)

Report
Copy link

Link copied!
I have attempted to do this too andI receive error:

In the Items of Gallery2 I placed :
Filter(Candidates,'Course schedule'.courseschedulid = BrowseGalleryCourses.Selected.coursescheduleid)

The identifier isn't recognized (the first courseschedulid). These two tables have a many-to-one relationship. Many candidates can be booked to one Course, the lookup column is on the Candidates table:

Is there something backwards with this? I attempted to make this so that the same person could not be related to more than one course. I am not understanding what I am referencing wrong unless there is something I am missing.

Was this reply helpful? Yes No