For starters, I'd change the order of the boolean expressions. Case branches on the first true so if PatternCount doesn't return a value greater than zero, it must return zero.
You can also use the Let function to clean up your expressions when you have the same sub expression used more than once.
Let ( [ pc38 = PatternCount ( self ; "38" ) ;
pc27 = PatternCount(Self;"27");
EmCat = IsEmpty (ContactCategoryID) ;
EmpCont = IsEmpty(Contacts_Assign Contact Category If Distro Vendor_INVENTORY::vendorID)
Case ( ( pc38 > 0 or EmCat ) and not EmpCont and not pc27 ; "27¶"&contactCategoryID ;
EmCat and not EmpCont and not pc27 ; "38¶"&contactCategoryID ;
not EmpCont ; );"38¶27¶"&contactCategoryID;
) // Let
That's just a first cut, additional simplification may be possible. If I were looking at your complete set up and understood why you want to build this list, a completely different approach that avoids this complicated auto-enter calc might be possible.
This field is part of our Contacts table. User can assign any of a variety of contact categories, but I want 38 (All) to always be present (for use in value lists), and 27 (Vendor) to be selected if a contact is assigned as a vendor in the inventory table.
Category 27 brings up another issue I'm trying to figure out right now, if this is too much of a divergence from topic let me know and I'll start another thread. Basically it's this: I want category 27 to be inserted into the contactCategoryID field if a contact is assigned as a vendor in the Inventory table, and removed if they are not. How can I get a text auto-calc field to update based on a condition in a related table?
Is this a field formatted with check boxes?
but I want 38 (All) to always be present
But that's not the case in your calculation above as you have one result where the value is "27¶"&contactCategoryID
List ( 38 ; ContactCategoryID ; If ( Contacts_Assign Contact Category If Distro Vendor_INVENTORY::vendorID ; 27 ) ; self )
If this is a checkbox formatted field, duplicate values of 38 and 27 will not affect which check boxes show as checked.
How can I get a text auto-calc field to update based on a condition in a related table?
This is a limitation of auto-entered calculations. Modifications of values in fields from related tables will not trigger a recalculation of this field's value. You should either use a calculation field or a script trigger that updates this field in all related records each time the field in the related table is changed.
That last offering doesn't let you remove the 27 in all cases if the contact is no longer a vendor. I'm playing with a demo file here that I'll post in a few minutes.
The field in question has a checkbox on the UI, yes.
The thing about the result of "27¶"&contactCategoryID is that 38 will always be present and carried through the calc if this result is reached. I was trying to create something clean that wouldn't create duplicate values.
"You should either use a calculation field"
I'm not quite following you here... Use a calc field how?
Thanks, this is helpful. I'm curious about this last thing, but otherwise I'm clear enough on how to approach this.
Thanks, I'll check out the file.
Use the same expression in the Auto_enter calculation as the calculation field for a field that returns text as it's return type. Format it as a check box group.
The result will auto-update, but it is no longer directly editable by the user--which is why I also mentioned using a script trigger to update values when the field in the related table is edited.
For future reference here's what I ended up doing:
Relation between Inventory::VendorID and Contacts::ContactID (primary key)
I created a boolean isVendor field in my Contacts table for ease of scripting. Kept essentially the same calc field as in the first post, subbing Contacts::isVendor for Contacts_Assign Contact Category If Distro Vendor_INVENTORY::vendorID.
On the inventory layout: OnObjectEnter script sets isVendor in related record to 0. OnObjectExit sets this same field to 1 If not IsEmpty(Inventory::vendorID).