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