cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JasonF
Level: Powered On

Display one to many relationship in Canvas Apps

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 Company Relationship.JPGCDS Test Company RelationshipCDS Company.JPGCDS Test Company EntityCDS Sales.JPGCDS 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

1 ACCEPTED SOLUTION

Accepted Solutions
mcolbert
Level 8

Re: Display one to many relationship in Canvas Apps

@JasonF 

 

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!

View solution in original post

5 REPLIES 5
mdevaney
Level 8

Re: Display one to many relationship in Canvas Apps

@JasonF 

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.
JasonF
Level: Powered On

Re: Display one to many relationship in Canvas Apps

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

mcolbert
Level 8

Re: Display one to many relationship in Canvas Apps

@JasonF 

 

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!

View solution in original post

mdevaney
Level 8

Re: Display one to many relationship in Canvas Apps

@JasonF 

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."

JasonF
Level: Powered On

Re: Display one to many relationship in Canvas Apps

Thanks mdevaney,

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

 

Regards

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 92 members 5,064 guests
Please welcome our newest community members: