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:
Please also check and see if the following blogs would help in your scenario:
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.
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.
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:
Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.
Featuring samples like Return to the Workplace and Emergency Response Applications
Features releasing from October 2020 through March 2021