Consolidating a list of records

[21st Century Man]

Greets, all:

I'd like to take child records (from/in a join table) and use a calculation to create a consolidated text string list (see attached)--the data's in the portal, but I'd like the output to look like like the two lines of text above it.

I played with using UniqueValues and List, but I just can't get my head in gear to utilize those correctly no matter what syntax I've tried.

Another possibility that just occurred to me is a script trigger that would run OnRecordLoad, but I still don't know how to pull this off. (I'm sure it's not too difficult, just that I'm being dense.)

As always, thanks in advance for your time.

Rich

[comment]

Where exactly do you need this result - IOW, how do you intend to use it? It could be produced by script, a calculation field or even by export. It might be even possible to build a layout that only displays such result using a sub-summary part or a filtered portal.

It probably doesn't matter, but just in case: do all three fields belong to the join table? Or are some of them defined in one of the parent tables?

 

[21st Century Man]

I'd like it on-screen--it's informational only and mostly for aesthetics: The layout where users choose the values consists of large, checkbox'd value lists that eat up a lot of "real estate" on-screen, so if I can have a "shorthand" version of what they choose appear on the layout where they do their data entry work, that'd be useful to them.

All three fields are in the join table, but contain only the foreign IDs of the records from the parent tables.

 

 

Edited June 7 by 21st Century Man

[comment]

I am afraid I still don't follow fully. Please elaborate what are the tables involved here, what is the "layout where users choose the values" and what are the fields that hold the values you want to be shown in this summary.

I suspect the best approach would be to use a portal from one of the parent tables, filtered to show only unique values. I posted a demo of such method here:
https://fmforums.com/topic/71906-getting-more-out-of-filtered-portals-3unique-values/#comment-340017

Unfortunately the forum currently does not allow file downloads, so you won't be able to learn from it. I'll try to post a file adapted to your situation, after I get a better understanding of it.

 

[21st Century Man]

Thanks for your patience. I've attached a screen shot from the Relationships map and the tidied-up layout with the value lists (District_Staff table). After a user chooses Grade Level, Position, and Subject Taught, she/he clicks the Add button whereupon a script "pushes" the respective IDs from the Grade_Level, Position, and Subject_Taught tables into the join table.

 

On edit: Maybe I need another join table between Subject_Taught and Grade_Level?

Edited June 9 by 21st Century Man

[comment]

This file shows how I would approach this using the aforementioned method of filtering a portal to display only unique values.

A few notes:

1.  For simplicity, I have left out the Positions and Subjects tables and used meaningful values for PositionID and SubjectID in the Assignments join table instead. This has no impact on the calculation formulae that need to be used.
2. To some extent, this is a cop-out: I believe I could have done without the cCombinedKey field in the Assignments table. But it would have taken some time and - perhaps more importantly - the formula used for portal filtering would be much more difficult to understand.

A note about your setup: I don't understand why you need the Levels table. Does it hold any other information besides an ID and the level? It seems to me that a custom value list of these levels would be quite sufficient.

The other thing that puzzles me is the checkbox of these levels shown in your screenshot. It looks like users actually select multiple levels for each unique combination of Position and Subject, and your script breaks these down to individual records. And now you are asking how to combine them back to the original form? Wouldn't it be easier just to store the data as entered by the user?

Link to the file (expires in 24 hours):
https://wormhole.app/3D9xaz#GF8aSO2FXKXPIp8mfOLBkQ

 

 

Edited June 9 by comment

[21st Century Man]

Wonderful--thank you!

To answer your question, there's a fundamental logic flaw with the original checkbox setup where all three value lists were checkbox'd. Say a teacher taught Math 6 and English 3--users would tick the Math and English values in Subjects Taught and 3 and 6 in Grade Levels, therefore it was impossible to figure out whether the teacher taught Math 3 or 6 or English 3 or 6. By creating a join table, I was hoping to prevent that from happening.

Edited June 9 by 21st Century Man

[comment]

3 minutes ago, 21st Century Man said:

users would tick the Math and English values in Subjects Taught and 3 and 6 in Grade Levels, therefore it was impossible to figure out whether the teacher taught Math 3 or 6 or English 3 or 6. By creating a join table, I was hoping to prevent that from happening.

A join table is indeed appropriate here. I am just asking why the join table cannot simply store multiple levels for each subject/position combination, exactly as entered by the user and save you the trouble of breaking them up to individual record for each level and then putting them back together for the display as we are doing here.

 

[21st Century Man]

Ah! I see what you're saying. Well, that would be simpler. Thanks again!