cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JaredE
New Member

Correct relationship for new app?

Im trying to create an app where we relate which products are shown by which customers.  I understand Access, and this would be setup as a many to many relationship with a junction table.

 

The app would have a customer list, and when selecting a customer, you would go to a second screen where you can relate the multiple products they have.

 

I have done alot of reading and cant find the best solution for this with the CDS in Powerapps.  in the app, It would not be necessary to relate customers to products, only products to customers, although it would be necessary to select a product and see which customers have it.

 

Should this be setup as a many to many relationship as in access with a junction table?  Or would you suggest a one to many (one customer to many products), and can the products be drilled down by a report to see which customers have an indivudual product this way?

 

Thanks im trying to learn how this all works with CDS and find the information and tutorials out there mainly for very basic non related apps.

1 ACCEPTED SOLUTION

Accepted Solutions
Meneghino
Community Champion
Community Champion

Your understanding is correct.

In order to check for membership/relations you can use the Filter function on the junction table/entity/list

Filtering the junction table by customer gives you a list of products for that customer.

Filtering the junction table by product gives you a list of customers that have that product.

View solution in original post

9 REPLIES 9
v-micsh-msft
Community Support
Community Support

Duplicate

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-micsh-msft
Community Support
Community Support

Hi @JaredE,

 

Many-to-many relationship is not supported under Common Data Service, this is documentated in the article below:

Build a relationship between entities

Quoted

"

Many-to-many - In this type of relationship, each record in entity A can match more than one record in entity B, and vice versa. The current release of the Common Data Service doesn't support this type of relationship.

"

Regards,

Michael

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Meneghino
Community Champion
Community Champion

Hi @JaredE

To add to what @v-micsh-msft said, the only solution to many-to-many is CDS is currently a junction table, as you yourself mention.

Up to you to know if you need many-to-many or one-to-many according to your data structure and purpose.

Can you point me to a tutorial for using a junction table in CDS,    Id imagine the junction table needs to have an ID field, and a lookup fileds to the Customer and Product tables.   Would there also need to be lookup fields in the Customer and Product tables that point back to the junction table for the data to be entered via the customer table and relate?

 

For such an easy app, would it be better to use a sharepoint list?  Guessing the Many to Many relationship would be the same setup there?

 

 

 

 

Meneghino
Community Champion
Community Champion

No need to point back from the Customer and Product tables.

SharePoint list will do the same job, but you will also need to use a junction table since lookups with multiple choices are not supported in PowerApps.

So it would not be possible to (when in the Customers list/entity) directly associate multiple products to that customer, and it would all need to be done one by one in the junction list/entity?  

 

Understanding this would work with a One customer to Many product senario with multiple choices selected, i also want to be able to click on a product from the product list/entiy and see what customers have this product, hence the Many to Many relationship im looking for.

 

Thx again,  sorry if im not understanding. 

Meneghino
Community Champion
Community Champion

Your understanding is correct.

In order to check for membership/relations you can use the Filter function on the junction table/entity/list

Filtering the junction table by customer gives you a list of products for that customer.

Filtering the junction table by product gives you a list of customers that have that product.

View solution in original post

Thats a huge bummer.  I cant believe this is replacing access with no compatibility for a common Many to Many senario.

Guess i will stick with something else like airtable until this is supported in the future. Smiley Frustrated

Meneghino
Community Champion
Community Champion

Hi @JaredE

I would be very grateful if you could read the following post on many-to-many relationships and maybe leave a comment, thanks.

https://baizini-it.com/blog/index.php/2017/10/10/powerapps-101-many-to-many-relationships-between-ta...

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (81,346)