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.