|
|
|
|
|
|
|
|
|
|
ACT! addons by ActPresentation.com : Abacus Calculated Fields, PicturePlus, Quick Navigation List, Employment History, Quick Search, Presentation for ACT!
I have a field which has a yes no or maybe condition, and a field with a value associated to that condition.
There are 8 such combination fields, so if the condition is "y" I want to add the values to a total. What is the most efficient formula to use. Thanks again. Richard,
please provide an example example:
Field Name Field status Field amount add on 1 Y 300.00 add on 2 ? 250.00 add on 3 N 0.00 add on 4 Y 200.00 When the status field changes from ? or N to Y, I want to add the amount in the corresponding field, to a total field elswhere called add on totals Hope this is clear. Thanks Richard,
as I understand you have 8 fields : add on 1 ( value = 300.00 ) add on 2 ( value = 250.00 ) add on 3 ( value = 0.00 ) add on 4 ( value = 200.00 ) status 1 ( value = Y ) status 2 ( value = ? ) status 3 ( value = N ) status 4 ( value = Y ) You want to calculate total [add on 1] + [add on 2] + [add on 3] + [add on 4] and put result to field [add on totals] The problem is you want to calculate total only for fields that have corresponding status field value = Y Solution 1 : Assign formula to [add on totals] field. Formula is as follows : (IF [status 1]='Y' THEN [add on 1] ELSE 0 END)+ (IF [status 2]='Y' THEN [add on 2] ELSE 0 END)+ (IF [status 3]='Y' THEN [add on 3] ELSE 0 END)+
(IF [status 4]='Y' THEN [add on 4] ELSE 0 END)+ Solution 2 :
Assign formula to [add on totals] field. Formula is as follows : (CASE [status 1] WHEN 'Y' THEN [add on 1] ELSE 0 END)+ (CASE [status 2] WHEN 'Y' THEN [add on 2] ELSE 0 END)+ (CASE [status 3] WHEN 'Y' THEN [add on 3] ELSE 0 END)+ (CASE [status 4] WHEN 'Y' THEN [add on 4] ELSE 0 END) Solution 3 :
Assign formula to [add on totals] field. Formula is as follows : CASE WHEN [status 1]='Y' and [status 2] ='Y' and [status 3]='Y' and [status 4] ='Y' THEN [add on 1]+[add on 2]+[add on 3]+[add on 4] WHEN [status 1]='Y' and [status 2] ='Y' and [status 3]='Y' and [status 4]<>'Y' THEN [add on 1]+[add on 2]+[add on 3] WHEN [status 1]='Y' and [status 2] ='Y' and [status 3]<>'Y' and [status 4] ='Y' THEN [add on 1]+[add on 2]+[add on 4] WHEN [status 1]='Y' and [status 2]<>'Y' and [status 3]='Y' and [status 4] ='Y' THEN [add on 1]+[add on 3]+[add on 4] WHEN [status 1]<>'Y' and [status 2] ='Y' and [status 3]='Y' and [status 4] ='Y' THEN [add on 2]+[add on 3]+[add on 4] WHEN [status 1]='Y' and [status 2] ='Y' and [status 3]<>'Y' and [status 4]<>'Y' THEN [add on 1]+[add on 2] /* ... an so on ( 16 combinations in total ) ... */
END Solution 4 :
If you can use in status fields 1 instead of Y , 0 instead of N, empty instead of ? Assign formula to [add on totals] field. Formula is as follows : [add on 1]* ToNum([status 1]) + [add on 2]* ToNum([status 2]) + [add on 3]* ToNum([status 3]) + [add on 4]* ToNum([status 4]) Solution 5 :
In next release of "Abacus Calculated Fields for ACT!" addon we plan to add new function COMPARE( expr, value, value_if_true, value_if_false) This function compares expr and value (1 and 2 params) : if expr=value then 3rd param is returned (value_if_true) else 4th param is returned (value_if_false) using this function you can write the following formula : [add on 1]* COMPARE([status 1],'Y',1,0) + [add on 2]* COMPARE([status 2],'Y',1,0) + [add on 3]* COMPARE([status 3],'Y',1,0) + [add on 4]* COMPARE([status 4],'Y',1,0) Got solution 1 to work fine..using it now.
Got syntax error on solution 2. Didnt try others but may use as examples in future. Thanks again. Powered by Phorum. |
|
ACT! software CRM addons - best plugins for ACT contact management software ( Act! by Sage software : ACT!2005, ACT!2006, ACT!2007, ACT!2008, Act by Sage Premium for Workgroups ). Custom addon solutions ( ACT! add ons ) development and CRM (Customer Relationship Management) software training and consulting. |
