Create a conditional list

[Rich S]

Howdy, howdy:

I'm stuck in trying to figure out how to pull this off (see attached.) What I want to do is take each comment (left pane, a portal) that has either (or both) the Impact or Implementation checkbox ticked and insert those comments into their respective columns (in the right pane.) So, the first comment, "Over 90 tech tickets..." would fall under the Impact column; "Utilizing the ESU more now..." comment would go into the Impact + Implementation column, "Like the instructional coaching." wouldn't appear at all, etc.

Obviously, the records would be pulled from a found set made in the child table where the initial pull would be of records belonging to a specific school district, a specific school year, and have have either (or both) the Impact and/or Implementation ticked (with a Boolean "1".) From there, then what? Do I create a script to loop through the records and place them into a dedicated "holding tank" field, or have each value's results/comments created in a calculation field that runs a WHILE command, or use create an ESQL calculation, or..?

I be lost.

Thanks in advance for your help!

Rich

[comment]

29 minutes ago, Rich S said:

Obviously, the records would be pulled from a found set made in the child table

I am afraid that's not at all obvious. The term "child table" implies a relationship - and the term "found set" has nothing to do with relationships. A relationship sees only records that are related, regardless of whether they belong to any found set of the related table or not.

If you want to pull the records from a found set, then you would need a script that summarizes the found set in a layout of the child table, then passes the result to a record in the parent table. However, since the relationship is not being used in this process, it is not clear which record in the parent table should be the recipient of this result (you haven't provided any details regarding what these two table represent).

You mention "records belonging to a specific school district [and] a specific school year". Why not make this the relationship's criteria? Or, alternatively, why not produce the report from a layout of the child table?

 

[Rich S]

Sorry for being unclear.

The parent table has the school district name (and school year) info whilst the child table stores the visit (comment) records since there are multiple visits per school district (per school year), so they are related. 

I figured that I'd have to go to the child table and do all the processing there; I'm getting from you that's the wrong idea because you mentioned passing the result to the parent table. What I _think_ I'm getting from you is that I need to summarize the data in the child table...maybe by creating a calculated field--say ImpactCalc--with something like "If ( ImpactCheckbox = 1 ; ImpactComment ; "" ), then use a summary field to make a list of ImpactCalc?

 

[comment]

If you insist on showing the result in three columns (as opposed to FM report's default method of stacking categories vertically), then I would suggest you use a repeating calculation field (defined in the child table) with 3 repetitions to usher each comment to the appropriate column. Then a summary field (summarizing repetitions individually) can be used to list the comments in each column.

The formula for the calculation could be something like:

If ( 
Extend ( Impact ) + 2 * Extend ( Implementation ) = Get ( CalculationRepetitionNumber ) ; 
Extend ( Comment ) 
)

 

Edited May 19, 2024 by comment

[Rich S]

Thanks, I'll give it a shot.

Being a drone, it's not what I insist on but what the boss wants. : P