AnsweredAssumed Answered

Report/Layout that Inserts Paragraph Text based on Checkbox Selections

Question asked by jrwhite2 on Jul 28, 2016
Latest reply on Jul 28, 2016 by philmodjunk

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.