9 Replies Latest reply on Dec 28, 2010 7:09 PM by BobSchwenkler

# Simpler calculation possible? (Case with multiple conditions)

### Title

Simpler calculation possible? (Case with multiple conditions)

### Post

Edit:Solution I used to solve this issue is down at (what is currently) the bottom of this thread.

Hi All,

I have a calculation for a category ID field set to automatically enter either two values, or one or the other of the two.

Simplified, what I'm currently doing is as follows:

case(x=true and y=true;result 1;

x=true and y=false;result 2;

x=false and y=true;result3;

default result)

The calculation is manageable as is but if I want to add more variables it could get unwieldy pretty fast. Is there a simpler way to do this? Below is the actual calculation.

Case(

(PatternCount ( Self;"38")=0 or IsEmpty(contactCategoryID))and (not IsEmpty(Contacts_Assign Contact Category If Distro Vendor_INVENTORY::vendorID) and PatternCount(Self;"27")=0);"38¶27¶"&contactCategoryID;

(PatternCount ( Self;"38")=0 or IsEmpty(contactCategoryID))and (not IsEmpty(Contacts_Assign Contact Category If Distro Vendor_INVENTORY::vendorID) and PatternCount(Self;"27") > 0);"38¶"&contactCategoryID;

(PatternCount ( Self;"38") > 0 or IsEmpty(contactCategoryID))and (not IsEmpty(Contacts_Assign Contact Category If Distro Vendor_INVENTORY::vendorID) and PatternCount(Self;"27")=0);"27¶"&contactCategoryID;

Self)

• ###### 1. Re: Simpler calculation possible? (Case with multiple conditions)

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;
Self
) //case
) // 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.

• ###### 2. Re: Simpler calculation possible? (Case with multiple conditions)

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?

• ###### 3. Re: Simpler calculation possible? (Case with multiple conditions)

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.

• ###### 4. Re: Simpler calculation possible? (Case with multiple conditions)

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.

• ###### 5. Re: Simpler calculation possible? (Case with multiple conditions)

See how close this comes to what you are looking for: http://www.4shared.com/file/Vx618esi/CheckBoxGroup_with_AutoEnter.html

• ###### 6. Re: Simpler calculation possible? (Case with multiple conditions)

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?

• ###### 7. Re: Simpler calculation possible? (Case with multiple conditions)

Thanks, I'll check out the file.

• ###### 8. Re: Simpler calculation possible? (Case with multiple conditions)

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.

• ###### 9. Re: Simpler calculation possible? (Case with multiple conditions)

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).