@Newbie2021
Just a general comment to start. Your Data_Comments table has a lot of Data Redundancy built into it. This is where you have repeated data across many records eg the columns Teacher_First --> Class will be REPEATED every time you add a new comment. This is wasted storage, adds complexity to any updates required and a number of other issues.
@Drrickryp has a really good blog series on Database Design principles that may be worth reading?
A better way would be to have
Date | Teacher_ID | Student_ID | Comment | ...
Then you can use LookUp() to get the Teacher and Student information relevant to your app usage.
To combine your comments together you can use either Concatenate or Concat, whichever suits. You will also need to setup your rules, eg
[Name] <space> [Comment] .<space> [Pronoun] <space> [Comment] .<space> [Name] ...
In the above, I've used Name then comment, Pronoun then comment, Name then comment and so on. So, every second comment gets the Pronoun first. Is that your business rule?
You could write a Concatenate formula to cover this but I think that Concat is far better option because it would add dynamic functionality. Concat takes a table of data then returns a string based on the formula you set. Using @timl 's blog post here, you can create the table you want then string it all together, like so
First, get the Student record
Set( vStudent, Lookup(Student_Details, Student_ID = ID))
// This assumes that you have picked or entered into an input field some ID that relates to a Student and it's identified as 'ID'
Then, create a local collection of data
ClearCollect(
colStudentComments,
Filter(
Data_Comments,
// DatePicker1 is a DatePicker setup to filter the comments by a Date
Student_ID = vStudent.ID && Date > DatePicker1.SelectedDate
)
);
Clear(colComAdded);
// Create collection of comments with Pronoun to be used ie "ProN"
ForAll(
colStudentComments,
Collect(
colComAdded,
Last(
FirstN(
AddColumns(
colStudentComments,
"ProN", If(Mod(CountRows(colComAdded) + 1,2) = 0,
vStudent.Pronoun,
vStudent.FirstName1
)
),
CountRows(colComAdded) + 1
)
)
)
)
Then, in a label you can add this code to see the output
Concat(colComAdded, ProN &" " & Comment & ". ")
I've made a fair number of assumptions in the above but I think I've given you enough of the bare bones to get you started. Let me know if you need further help.
