Answered

One to Many Relationship

(0) Share

Report

Posted on by duncant

278

I have built a table (table A) with children in another table (table b). In the power app, I am building a screen with a form to push the data into table b. But since there can be more than one but no more than three, how should I allow the user to submit more than one? Should I create three screens, or is there a way I make both the screen and the form dynamic?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @duncant

Are you building a Canvas App or a Model App here? You mention they can have 1-3 total child records in the 1-N relationship?

Model Apps
What I have done in this situation in the past was not let the user actually create the records directly for "Table B" but make them run a workflow from the model app to do this and by running the workflow I could easily control what happens and only allow a maximum of 3 of the records to be created. You would need to have a rollup field for the count of Table B records on Table A and in the workflow call an action to recalculate that value (there are workflow tools out there for this like https://github.com/demianrasko/Dynamics-365-Workflow-Tools/blob/master/docs/Force%20Calculate%20Rollup%20Field.md).

This way you are controlling the creation of these records and they would not have the permission to just "add" them manually in your role based security. The Workflow would have to run under a user that does have permissions as the owner.

Hope this helps. Please accept if answers your question or Like if helps in some way.

Thanks,

Drew

Was this reply helpful? Yes No
duncant 278 on at

Like (0)

Report
Copy link

Link copied!

I am building a canvas app.

Was this reply helpful? Yes No
Verified answer

rprahalad 377 on at

Like (1)

Report
Copy link

Link copied!

The way I have done is, I inserted a Blank Vertical Gallery. I put label/Textbox and a label/dropdown and whatever controls you need inside the template of the gallery along with a Trash icon (to Delete Row) towards the end of each row. So, these set of controls show up in each row.

I have a + icon on top and I display the header (table A in your case) details on top of the gallery. Each time user clicks on the + icon, I do

Collect(colDetails, {ID: Text(Last(colDetails).ID + 1, Text1InsideGalleryField: "", SomeOtherField:0});

Basically I am initializing the Collection with a Blank Gallery Row. Note that this ID is just used for UI purposes and I don't persist it to a Database or anything. Next, the Gallery->Items property, I set it to colDetails. TextBox1 (Inside the gallery)'s .Default property, I set it to ThisItem.Text1InsideGalleryField property of the collection. The Trash icon OnSelect, I do

RemoveIf(colDetails, ID=ThisItem.ID) to remove the Row from the above collection

I have a Save Button at the bottom of the screen and I used 2 separate Patch statements: One to save the Master record and then store all the details record. I used ForAll to insert the Details records.

In my case, I had to save both the "one" Master Record as well as all "the many" Detail records in one go.

Was this reply helpful? Yes No
rprahalad 377 on at

Like (1)

Report
Copy link

Link copied!

From a Canvas app, all you need is one screen and one gallery and couple of icons/buttons like I mentioned. To ensure that the user can ONLY add 3 rows like you mentioned, it is as simple as setting the Visible property of the + Icon or Add Button that I mentioned in my reply, to
CountRows(colDetails <=3); and that would do it.
Or set its DisplayMode to If(CountRows(colDetails <=3), Edit, Disabled); and that would do it.

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Hi @duncant,

The quick answer to your question is yes it can be done with one or two screens and don't need three. The best recommendation if you're unsure where to start is to start with a template canvas app. In make.powerapps.com --> Create --> Start form Data --> pick Dataverse. It will setup by default all the basic components and plumbing with a list/gallery and then a form when selecting a record. The rest for restriction on no more than three becomes validation within your app.

Hope this helps!

Was this reply helpful? Yes No
duncant 278 on at

Like (0)

Report
Copy link

Link copied!

@rprahalad how can I link the child records to the parent record in the patch?

Was this reply helpful? Yes No
rprahalad 377 on at

Like (1)

Report
Copy link

Link copied!

@duncant , when you patch the Master record, you can actually move the entire record into a Variable. I am not in front of my work computer and apologize for not typing in the exact syntax. BUT its as simple as something like

Set(varMasterRec, Patch(MasterTable,, Deault{master table data goes here..}..)

This is akin to doing a scope_identity() in SQL Server except, that here, the variable varMasterRec will contain the just persisted, entire master row's data here.

Once the master record reference is in a variable, you can use that when you are Patching the detail records in a loop. Make sure you always alias the collection when you are doing a ForAll loop like I have done below (colDetails). This will save you tons of hardship. I once spent an hour trying to do a simple Patch Update because it doesn't understand ID=ID syntax.

So, patching the details record will be like:
,
ForAll(colDetails As 'colDetails // or galleryDetails.AllItems or whatever your details collection is)),
Patch (TableDetails, Default { MASTERCOLUMNNAMEI: varMasterRec, Rest Of the Detail Row Data}));

NOTE that, you don't have to varMasterRec.ID when patching that master column. You can directly assign the entire variable to it, as long as it is a proper lookup column to the Master row, in the back end.

Was this reply helpful? Yes No