ActPresentation.com HOME FAQ FORUM info@ActPresentation.com
Phorum: speed & power.

ActPresentation.com


Add-ons for Sage ACT! CRM

ACT! addons by ActPresentation.com : Abacus Calculated Fields, PicturePlus, Quick Navigation List, Employment History, Quick Search, Presentation for ACT!

condition testing for field addition

Posted by: Richard

Date: April 18, 2007 10:10AM

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.

Re: condition testing for field addition

Posted by: Admin

Date: April 18, 2007 10:19AM

Richard,
please provide an example

Re: condition testing for field addition

Posted by: Richard

Date: April 18, 2007 01:04PM

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

Re: condition testing for field addition

Posted by: Admin

Date: April 19, 2007 12:07PM

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

Re: condition testing for field addition

Posted by: Admin

Date: April 19, 2007 12:09PM

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)

Re: condition testing for field addition

Posted by: Admin

Date: April 19, 2007 12:13PM

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

Re: condition testing for field addition

Posted by: Admin

Date: April 19, 2007 12:15PM

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

Re: condition testing for field addition

Posted by: Admin

Date: April 19, 2007 12:31PM

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)

Re: condition testing for field addition

Posted by: Richard

Date: April 19, 2007 02:12PM

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.

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.