Save multiple Comments using IDs and one-to-many relationships

[darogael]

Introduction
This blog describes how you can save multiple comments to an single data entry using one-to-many relationships. The blogs walks through how you use IDs from the tables to build the relationships. The demo includes a sample app used by a school teacher to save comments and scores of students, however, the same app can be used for other similar scenarios. Majority of the content is covered in the video, however, expressions and important links are provided here.

Key items covered
 • Saving multiple comments in a separate table/list
 • Building relationships between tables using IDs

Video
The video deep-dives into how the solution works. The video focuses on the key items mentioned above. In addition you can download the attachment which has a copy of the app plus the Excel spreadsheet. The spreadsheet needs to be saved in your Business OneDrive.

Important Expressions / Formulas
Here are some formulas shown in the video.

Filtering the Home gallery using the search
Search(Record,HomeSearch.Text,"FullName")

 

Submit button in the Add Screen 

Set(NewRecordIDValue,(Max(Record,ID))+1);
Patch(Record,Defaults(Record),{ID:NewRecordIDValue,FullName:FullNameComboBox.Selected.FullName,Grade:AddGradeDropdown.Selected.Value,Note:AddNoteTxt.Text,Date:Now()});
Reset(FullNameComboBox);Reset(AddNoteTxt);Navigate(HomeScreen,ScreenTransition.Cover)

Submit button in the Edit screen

Patch(Comments,Defaults(Comments),{ID:Text(Max(Comments,ID))+1,RecordID:HomeGallery.Selected.ID,Comment:CommentsTxt.Text,Score:CommentScoreRating.Value,Date:Now()});
Reset(CommentScoreRating);Reset(CommentsTxt);
Navigate(HomeScreen,ScreenTransition.Cover)

Conclusion
Using the one-to-many relationship you no longer have to worry about how many comments you'll add since they are added as rows and no longer columns. As mentioned in the video this can be done with Excel spreadsheet in OneDrive, SQL tables and SharePoint Lists. In SQL and SharePoint, the ID columns are automatically incremented which makes things easier.

StudentScore.zip

[AddDynamics]

Hi Daniel,

Great video, thanks a lot.

This gives me a starting steering, however, my scenario is slightly different. when creating a primary record i.e. inspection, I should be able to log multiple defects before I submit the main form?

In the database (D365 CRM) I have one to many relationship between Inspection and defects entity. The expectation is, The inspection entity should be submitted with the one or more defects as child record in the CRM on the completion of the inspection form? Any help please?

[Anonymous]

Thanks @darogael this is helpful to me.  I am new to PowerApps and not a developer, I am not sure how exactly to open your App and make it work in my environment - I get a heap of errors.  If I can get it working then I can amend to suit my needs.  Can you point me to a tutorial that shows me how to open an app and to connect to an existing data source (Ideally I would like to use a Sharepoint List)  thanks very much.

[dedrasher]

@darogael - This is helpful, but the msapp file appears to have been changed. Can't follow along with the video since the screens are slightly different and there are errors in the Patch functions on the AddScreen and EditScreen..