3 Replies Latest reply on Jul 28, 2016 1:41 PM by philmodjunk

    Report/Layout that Inserts Paragraph Text based on Checkbox Selections


      This should be an easy question but I can't seem to find the answer I'm looking for.


      I have two tables:


      Table 1, "Projects," has fields such as "Field_Conditions_Exterior" and "Field_Conditions_Interior." It has an associated Layout designed for use on an iPad, where an inspector would go out to a job site and for each field, select one or more found conditions using checkboxes.


      Table 2, "Conditions," has fields such as "ID_No", "Field_Conditions", and "Corrective_Actions." A Value List is set up so that the "Field_Conditions" in this table display as the checkboxes in the Table 1 fields. The Value List has the data stored in Table 1 using the "ID_No." There is only one list of "Field_Conditions" here. It is filtered by Category (e.g. Interior or Exterior depending on the layout tab. The Projects table simply breaks the checkboxes down into smaller subsets for ease of use in the field.


      I would like to be able to generate a report for each project that looks up the "Field_Conditions" the inspector selects in the field, and returns a list of the "Corrective_Actions" necessary. The "Corrective_Actions" are too lengthy to be used efficiently by the inspector in the field directly, hence the shorter "Field_Conditions" checkboxes.


      I imagine I need a new field in the Projects table that looks up each "ID_No" stored in "Projects::Field_Conditions_Exterior" and returns the associated "Corrective_Actions" separated by a paragraph break. Then, the Layout could just use this new field to display the "Corrective_Actions." I would do the same for "Field_Conditions_Interior" ... and so on for other similar fields.


      So far, I have set up a relationship between "Projects::Field_Conditions_Exterior" and "Conditions::ID_No". I duplicated the latter table in the graph and created a relationship between "Projects::Field_Conditions_Interior" and "Conditions 2::ID_No". I'm not entirely sure that this is the correct way to set up the relationships.


      I've tried to develop a calculation in the new field using the Case or Substitute functions but I have not been successful. I've never really used Custom Functions before and have considered trying them but it seems excessive for something that really should be simple. I must just be not using the case functions properly or missing some other easier way of doing this. I am relatively new to FM so I'm hoping someone with more experience knows of a simple calculation, script, or other way of doing this. Thank you.

        • 1. Re: Report/Layout that Inserts Paragraph Text based on Checkbox Selections

          Is this what you have?

          In one table you have a check box formatted text field of Field Conditions. For each value you might select in this field, there is a corresponding corrective action in a different table that also has a field condition field to show to which field condition that corrective action corresponds?


          If so, a simple relationship can exploit the Multi-Key feature of FileMaker where a return separated list of values matches to any related related records that match to any one of the listed values.


          So a relationship like this:


          Projects::FieldConditions = Conditions::FieldConditions


          would let you set up Projects::FieldConditions with your check box formatted value list and either of the following methods can produce a list of corrective actions:


          List ( Conditions::CorrectiveActions )


          A portal to Conditions on the Projects layout could list the corrective actions


          So if you click the 1st, 3rd and 10th check boxes, any corrective actions that correspond to the 1st, 3rd or 10th field conditions will appear in the List or portal. (The List function produces a single block of text in one field with each corrective action as a separate paragraph. you can use the appearances tab to control indents, vertical spacing between paragraphs, etc. to dress up the presentation of such text.)

          1 of 1 people found this helpful
          • 2. Re: Report/Layout that Inserts Paragraph Text based on Checkbox Selections

            I knew it had to be something simple. Thank you!


            It was the List function that was escaping me.


            For any others with a similar issue, I did tweak the solution a bit ...


            My value list is stored using the "ID_No" Field (which I use to rank order the value list FieldConditions in a way that will make sense to the user - instead of just listing the FieldConditions alphabetically) ... so I was correct in setting up my relationship to the Conditions::ID_No field (not to Conditions::FieldConditions as suggested in the answer).

            • 3. Re: Report/Layout that Inserts Paragraph Text based on Checkbox Selections

              An ID makes sense as you can edit the conditions without breaking links to corrective action records, but you can sort your relationship on a value--such as an ID number in the corrective actions table and control the order of the paragraphs returned by list via a relationship matching on either value.