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

Can somebody explain how relationships work in CDS with regards to submitting forms.

Okay I have two entities. One entity contains Monthly Expense Reports and the other contains Line Items for each expense in the month. I have a one to many relationship between the primaryname column of the entity table (parent) and the primaryname column of Line items(child).

If I am submitting these two forms at 2 different times (you create an expense report first and then add the line items throughout the month) how is it able to relate them? The idea of a primaryname column is so odd to me. How can that be a primary key if it is not unique?
6 REPLIES 6
Community Support Team
Community Support Team

Re: Can somebody explain how relationships work in CDS with regards to submitting forms.

Hi @Black_Magic100 ,

Do you mean that a Expense report record could match multiple Line items records in your CDS (one-to-many)?

How do you submit your form data into your CDS Entity? Using Edit form? Do the two forms exist in same app?

Further, do you want to relate the submitted Line items record to the previously submitted Expense report record?

 

Based on the needs that you mentioned, I think the Relate() function could achieve your needs. I have made a test on my side, please take a try with the following workaround:

Set the OnSuccess property of the Edit form (EditForm2) which connects to 'Line Items' Entity to following:

Relate(
           SubmittedExpenseReportRecord.'Line Items',  /* <-- 'Line Items' represents the relationship (LookUp field) you set up between your 'Expense Report' entity and 'Line Items' entity */
           EditForm2.LastSubmit
)

Note: The SubmittedExpenseReportRecord represents your previously submitted Expense Report record.

More details about Relate function in PowerApps, please check the following article:

Relate function

Please also check and see if the following blogs would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/relate-records-in-many-to-many-relationships/

https://powerapps.microsoft.com/cs-cz/blog/option-sets-and-many-to-many-relationships-for-canvas-app...

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Black_Magic100
Level: Powered On

Re: Can somebody explain how relationships work in CDS with regards to submitting forms.

See that just does not make sense to me.  What you are doing is looking at the last expense report.  I have 21 locations in a franchise so if somebody else creates one now it is relating to the incorrect row.

 

What I ended up doing was creating an Autnumber in my Expense entity and calling it my ID column.  Whenever a new record is created it SHOULD populate an incrementing ID (I say SHOULD because it only works half the time it appears.... Go figure...).  I then have another ID column in my LineItems entity that is a simple text field.  

 

Based on the reportID selected in the gallery (after a new Expense row has been added) I will then autopopulate an invisible edit form row with that same ID.  This works great, but for whatever reason the Autonumber is glitchy as hell.

TimRohr
Level 10

Re: Can somebody explain how relationships work in CDS with regards to submitting forms.

The LastSubmit property is available in the OnSuccess action. It is the form holding on to the record that it just submitted, so it is instance-specific... so you have 21 people entering records simultaneously, and they are each going to return their own LastSubmit, no matter who wrote in what order.

 

While I think you can do what you're looking for with the Relate() function, as @v-xida-msft suggested, as a DB admin, I tend to break into hives when I encounter anything that sniffs of entering downstream records before upstream records are established... like entering unrelated line-items and trusting that they will be connected after the fact.

 

Your relationship between the line items entity and the expense report entity should point to the primary key of the Expense Report entity. That is, ONE Expense Report could have MANY Line Items (1:N). It doesn't matter to the Line Items entity how many times the Expense Report GUID appears in the data, just that EVERY record in the Line Items entity has a valid GUID.

 

When you create an entity, CDS gives you a default GUID named after the singular version of your item (ie, "ExpenseReport"). That is the field that will drive the relationship to the Line Items entity. That leaves you 2 ways to get or use the GUID...

...either give them a drop-down (that will have all of the fields of the Expense Reports entity) and let the user choose which Expense Report they are updating, or...

...capture the LastSubmit Expense Report (after a successful submission of the form), and write it to a variable that you then use when you write your Line Item entry (like with an invisible control in the Line Item form that reads the GUID into the text property of the control and is then saved with the form contents when the Line Item is committed to CDS).

 

Bottom line, I think it's simpler than you are making it out to be.

Black_Magic100
Level: Powered On

Re: Can somebody explain how relationships work in CDS with regards to submitting forms.

Like I said I just used autonumber to create a primary key in the expense entity. I then have an invisible form for the line items entity that auto fills whatever expense report ID is chosen in the previous step.

Your solutions don't really make sense to me TBH. I feel like my way is your typical primary key foreign key relationship.

Regardless, some of my columns in my line items entity are still throwing GUID errors. My category is a basic text type, but when I change that form to Allowed Values and use brackets to specify the items it throws some weird GUID error. It still seems to work though, not sure why... Powerapps is so weird to me
Community Support Team
Community Support Team

Re: Can somebody explain how relationships work in CDS with regards to submitting forms.

HI @Black_Magic100 ,

In order to fix the GUID error, please consider take a try to turn off/disable the "Relational data, option sets, and other new features for CDS" option within Advanced settings of App settings of your app, re-load your app, check if the issue is solved.

 

In addition, you could also consider take a try to use GUID function to convert a GUID string value into a GUID type value. Please check the following blog for more details:

https://powerapps.microsoft.com/en-us/blog/display-names-and-guids-for-canvas-apps-graduate/

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Black_Magic100
Level: Powered On

Re: Can somebody explain how relationships work in CDS with regards to submitting forms.

Hey I think it fixed it... Now can you explain why that would be?

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 26 members 4,565 guests
Please welcome our newest community members: