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

[Black_Magic100]

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?

[v-xida-msft]

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,

[Black_Magic100]

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]

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]

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

[v-xida-msft]

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,

[Black_Magic100]

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