AnsweredAssumed Answered

Field Calculation for child record by ID and boolean field

Question asked by jbryan on Jun 23, 2017
Latest reply on Jun 23, 2017 by jbryan

I'm relatively new to FM but here's an attempt at a question I can't seem to find answers for...

 

BACKGROUND

Duplicated an icon and set unique colors on each with different "Hide Object When" calculations. The calculations look at a specific field value in the current record. Right now that field is a drop-down manually set by the user. To help illustrate, the field is called "Photoshoot Needed". The values are "NA", "Needed", "Scheduled". The icon is gray for "NA", gold for "Needed" and black for "Scheduled".

 

THE GOAL

The same layout includes a portal to a child table; "Milestones". All three values of "Photoshoot Needed" can be determined by records populated in Milestones.

– If there is a child record with "ID" field of 62, the "Active" field is true and the date field is NOT empty, the value should be "Scheduled".

– If there is a child record with "ID" field of 62, the "Active" field is true and the date field is empty, the value should be "Needed".

– If there is a child record with "ID" field of 62, the "Active" field is false, the value should be "NA".

– If there is NO record in that portal with "ID" field of 62, the value should be "NA".

 

Can I rewrite the "Photoshoot Needed" as a calculation field that will automatically set it's value according to these criteria? If so, my color coded icons would show status without the step of the drop-down picker for users (which is redundant since they have to create the Milestones records).

 

SOLUTION

I assumed I could use SQL but my problem seems too complex to work as a calculation field...or maybe just over my head to write. I can think of other ways to do this but a calculation field seems the cleanest. I welcome any feedback.

Outcomes