Flexible Conditional Formatting for Related Data Entry Fields

If you show me something that needs to happen again and again, I’m immediately trying to find a way to create something flexible with the least amount of maintenance from me, the developer. So for the sake of efficiency (which is partly what this blog post is about), let’s get right to it! Ah! But before I do that, let me give you the spoiler of this blog article: Yes! There is a sample file at the end of the article! 🙂

The Scenario

Imagine having a layout that has a high number of date or number fields – think chemical analytics, data gathered before/during a surgery, or patient results from a blood test. Now imagine that the user has to either enter the value OR check “unknown” if the user doesn’t have that information. What we need to do to keep the data clean is make sure that a user can’t both enter a value and check the  “unknown” checkbox. Additionally, it would really help the user, visually, if the value field was grayed out when the “unknown” checkbox is checked. If you have 2+ of these value field + unknown checkbox field, it would take a lot of work to hardcode all the field names in the script triggers and conditional formatting. I love the Case statement but if I have to write too many potential tests, it can become overwhelming (and the amount of maintenance required is high!).

For the sake of organization, here is the problem in bullet form:

• If the user checks off “Unknown”, the related Value field must be grayed out
• If the user puts a value in the Value field, the related Unknown field must be empty
• If the user checks off “Unknown”, the related Value field must be empty

The Solution

Well, the most important thing for creating something flexible is through consistency. And here, specifically, a consistent naming convention will make this a much easier problem to solve. Let’s assume that the value field will store the lowest temperature that occurred during a patient’s surgery. We might call that field LowestTemperature. The key is that the unknown checkbox field follow a strict convention, so in this case, I would call it LowestTemperature_Unknown. If we also had a field for a patient’s last surgery date, we could call that DateLastSurgery and the unknown checkbox field would be called DateLastSurgery_Unknown.

So now that we have consistent field names, the next step is to “relate” them on the layout so that 1) conditional formatting can gray out the value field (if applicable), and 2) a script trigger can clear the other related field.

What I learned is that you can use the following function to manipulate conditional formatting based on the field name on the layout:

GetFieldName( Self )

From a logical perspective, if the field name of the current field does NOT end in “_Unknown”, that means that appending “_Unknown” to the field name would provide the related “unknown” checkbox field. And if the field name of the current field DOES end in “_Unknown”, removing “_Unknown” from the field name gives us the related Value field.

Let’s start with the Conditional Formatting:

By putting the following logic into conditional formatting for the Value fields, I can gray out the field when the unknown box is checked:

Let(
[
//Table + field name
n = GetFieldName( Self )  ;

//Table + field name + "_Unknown"
f = n & "_Unknown" 
] ;

// If the related field isn't empty, activate the conditional formatting
not IsEmpty( Evaluate( f ) ) 

)

Next up is the Script Trigger:

OnObjectSave is the script trigger I’m going to use. It’ll only run when the user has modified the field and is attempting to leave the field. So what should happen?

1. Check to see if the current field is empty. Note that if the user entered a value field, typed something, and then deleted what they typed, we wouldn’t want to clear the “unknown” field,
2. If the current field is not empty, clear the related field.

One script trigger for both field types would rock our world because it means less maintenance, right? I can apply a script trigger to all of the Value fields and “unknown” checkbox fields and it’ll work, no matter what. So here is the script I came up with:

Here’s the calculation for both the target field and the calculated result for the Set Field By Name script step:

The target field calculation is:

Let(
[
t = Get ( ActiveFieldTableName ) ;
n =  Get(ActiveFieldName)  ;
f =

If(
// Remove "_Unknown" to get other field name
Right ( n ; 8 ) = "_unknown" ; Left ( n ; Length ( n ) - 8 )  ; 

// Add "_Unknown" to get other field name
n & "_Unknown"
)
] ;

t & "::" & f // table + field name

)

The Calculated result is

"" //Empty

Bam! Now whenever we have a pair of fields (value and unknown), we can duplicate a previously created pair, change the fields, and the script trigger and conditional formatting will already be attached. Caution should be taken, since you’ll also have the field format, the dropdowns, etc. but trust me – it’s easier to change those things than to copy and paste the conditional formatting!

I already gave you the spoiler, which is a sample file! I’ve created a small file with 4 fields (2 pairs). Feel free to test out the logic and check out the components (conditional formatting/script trigger). Let me know what you think! Hopefully you can put this to use in your solution or let me know if you’ve come up with some other way of handling something similar!

Download the sample file: Related-Values-Soliant-Martha-Zink.fmp12.zip