I'm relatively new to FM but here's an attempt at a question I can't seem to find answers for...
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 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).
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.