Summary over all fields of a record based on checkbox
in my database there is table A which includes about 100 text fields (textfield). Additionally, each text field has a corresponding boolean value in a number field (textfield_b). Finally, there are tables B and C which both have the same number of text fields as table A, all named identically as in table A. However, B and C include global fields only.
The text fields in A are used for a structured interview where the interviewer can add answers, comments, etc to questions (stored in B). The boolean value is represented as a checkbox where the interviewer can flag the most important aspects of the interview. C holds general advice for each of the questions.
What I need is an elegant way of displaying a summary of the ongoing interview i.e. show only those comments (tableA::textfield-value) which have been flagged (textfield_b=1) side-by-side with the questions (g_tableB::textfield-value) and the advice (g_tableC::textfield-value).
I could do this with a calculation field, however, I would have to state for each question (>100) "if (checked) then (textfield-value)" as I see no way of adding a loop within a calculation (please note that I'm using FM Pro and not Advanced.). Is there an easier way of solving this?
I also thought of transforming the checkboxes into buttons that simply add the information to a summary field, however, this approach would be static. If the interviewer would change his comments or wants to toggle the checked state, this approach would break.