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!

How to calculate conditions

Posted by: John

Date: July 31, 2007 05:08AM

Hello,
Below are a few screen shots of my current database…

j1.png

Currently the Abacus Calculated Fields is calculating the 8 fields in the center highlighted and the total is calculated at the bottom in the Sq Footage Total.
I need to know how to calculate conditions.
In this project, a Sales Rep. selects from 8 types of products – the below example illustrates such options…

j2.png

The conditions are these…… Once a Product is selected it is either calculated by Square Footage or Square Yards.
The overall desired calculation is in the field SQ Footage Total and all summations of these fields combined is to be Square Footage.

So the need to convert any field on the right that has a SY (Square Yards) must be multiplied by 9 (conversion for Square Footage) and the total at the bottom needs to reflect the converted multiplied total once added together with any other fields in the center.

The Abacus Calculated Fields is working perfectly to add the fields in the center as you can see, however, the answer in the total should read ( 1430 ) because the multiplication conversion should have taken place before the addition of the center fields because the option SY was selected for two products.

I understand that if a user selects the SY – this calculation can be completed, if the user selects SF then the center fields simply adds the data and provides the answer in the SQ Footage Total field at the bottom.

Can you please provide assistance on how to write the formula… I understand this to be an “IF” statement – yet your tutorials and training materials do not reflect a solution for this calculation.

Best Regards,
John

Re: How to calculate conditions

Posted by: Admin

Date: July 31, 2007 05:20AM

Dear John,
below is solution for your configuration.

I don't know your actual fields names, so lets assume that :

Product1, Product2, Product3, Product4, Product5, Product6, Product7, Product8 - product name fields

Product1_sq, Product2_sq, Product3_sq, Product4_sq, Product5_sq, Product6_sq, Product7_sq, Product8_sq - product square values

Product1_measure, Product2_measure, Product3_measure, Product4_measure, Product5_measure, Product6_measure, Product7_measure, Product8_measure - product measure types (SF or SY)

I guess your current total formula is as follows :

[Product1_sq] + [Product2_sq] + [Product3_sq] + [Product4_sq] + [Product5_sq] + [Product6_sq] +[Product7_sq] + [Product8_sq]


Now we need to apply condition to every item - if corresponding ProductN_measure field value is SY then we need to multiply square value by 9 , else - use raw value.

So we will replace every [ProductN_sq] item with condition (IF [ProductN_measure]='SY' THEN [ProductN_sq]*9 ELSE [ProductN_sq] END)

So changed total formula will look like this :

(IF [Product1_measure]='SY' THEN [Product1_sq]*9 ELSE [Product1_sq] END)+
(IF [Product2_measure]='SY' THEN [Product2_sq]*9 ELSE [Product2_sq] END
)+
(IF [Product3_measure]='SY' THEN [Product3_sq]*9 ELSE [Product3_sq] END
)+
(IF [Product4_measure]='SY' THEN [Product4_sq]*9 ELSE [Product4_sq] END)+
(IF [Product5_measure]='SY' THEN [Product5_sq]*9 ELSE [Product5_sq] END
)+
(IF [Product6_measure]='SY' THEN [Product6_sq]*9 ELSE [Product6_sq] END
)+
(IF [Product7_measure]='SY' THEN [Product7_sq]*9 ELSE [Product7_sq] END
)+
(IF [Product8_measure]='SY' THEN [Product8_sq]*9 ELSE [Product8_sq] END)


If you have any further questions - please feel free to ask.

Re: How to calculate conditions

Posted by: John

Date: September 03, 2007 05:28AM

Hi,
There are Two issues…

This is the first:

Your posted solution for adding the fields below functioned well at first … let me explain …
I had a systems crash and had to re-install my computer – thus rebuilding the Act Database …

The below solution when applied does not show a total in the Square Footage Total Field

(IF [Product1_measure]='SY' THEN [Product1_sq]*9 ELSE [Product1_sq] END)+
(IF [Product2_measure]='SY' THEN [Product2_sq]*9 ELSE [Product2_sq] END)+
(IF [Product3_measure]='SY' THEN [Product3_sq]*9 ELSE [Product3_sq] END)+
(IF [Product4_measure]='SY' THEN [Product4_sq]*9 ELSE [Product4_sq] END)+
(IF [Product5_measure]='SY' THEN [Product5_sq]*9 ELSE [Product5_sq] END)+
(IF [Product6_measure]='SY' THEN [Product6_sq]*9 ELSE [Product6_sq] END)+
(IF [Product7_measure]='SY' THEN [Product7_sq]*9 ELSE [Product7_sq] END)


My actual formula is :

(IF [SFSY1]='SY' THEN [SF1]*9 ELSE [SF1] END) + 
(IF [SFSY2]='SY' THEN [SF2]*9 ELSE [SF2] END) +
(IF [SFSY3]='SY' THEN [SF3]*9 ELSE [SF3] END) + 
(IF [SFSY4]='SY' THEN [SF4]*9 ELSE [SF4] END) +
(IF [SFSY5]='SY' THEN [SF5]*9 ELSE [SF5] END) + 
(IF [SFSY6]='SY' THEN [SF6]*9 ELSE [SF6] END) +
(IF [SFSY7]='SY' THEN [SF7]*9 ELSE [SF7] END

Your original post indicated 8 Items – the client has now specified 7

j11.png

The above solution does not work when adding numbers into the fields – nothing shows up in the Square Footage Total Field

Because the first functions did not work – I thought I would try and do a simple addition of the center fields

When doing a simple addition – the fields are not providing a sum total – the numbers are showing up in the Square Footage Total Field next to each other…

In the below Screen Shot – I added 41 + 41 which should = 82 … well you can see what is happening…

j22.png

If you could let me know what is wrong I thank you in advance!

Best Regards,
John

Re: How to calculate conditions

Posted by: Admin

Date: September 03, 2007 05:36AM

Dear John,
did you change fields type ( [SFn] fields ) ?



Edited 1 time(s). Last edit at 09/03/2007 06:36AM by Admin.

Re: How to calculate conditions

Posted by: John

Date: September 03, 2007 06:30AM

Yes, fields type was changed.
All are Characters and the SF / SY fields are drop down lists

My field names are featured below.

(IF [SFSY1]='SY' THEN [SF1]*9 ELSE [SF1] END) + 
(IF [SFSY2]='SY' THEN [SF2]*9 ELSE [SF2] END) +
(IF [SFSY3]='SY' THEN [SF3]*9 ELSE [SF3] END) + 
(IF [SFSY4]='SY' THEN [SF4]*9 ELSE [SF4] END) +
(IF [SFSY5]='SY' THEN [SF5]*9 ELSE [SF5] END) + 
(IF [SFSY6]='SY' THEN [SF6]*9 ELSE [SF6] END) +
(IF [SFSY7]='SY' THEN [SF7]*9 ELSE [SF7] END

Best Regards
John

Re: How to calculate conditions

Posted by: Admin

Date: September 03, 2007 06:35AM

Dear John,
The point is [SFn] fields type - it seems that you changed [SFn] fields type from Number to Character.

When you try to add Character fields together - fields are NOT added , fields are CONCATENATED instead.

There are 2 solutions :

1. Change fields type back to Number.

OR

2. In formula convert all Character fields to Numbers ( apply ToNum(...) or ToDec(...) functions )

(IF [SFSY1]='SY' THEN ToNum([SF1])*9 ELSE ToNum([SF1]) END) +
(IF [SFSY2]='SY' THEN ToNum([SF2])*9 ELSE ToNum([SF2]) END) +
(IF [SFSY3]='SY' THEN ToNum([SF3])*9 ELSE ToNum([SF3]) END) +
(IF [SFSY4]='SY' THEN ToNum([SF4])*9 ELSE ToNum([SF4]) END) +
(IF [SFSY5]='SY' THEN ToNum([SF5])*9 ELSE ToNum([SF5]) END) +
(IF [SFSY6]='SY' THEN ToNum([SF6])*9 ELSE ToNum([SF6]) END) +
(IF [SFSY7]='SY' THEN ToNum([SF7])*9 ELSE ToNum([SF7]) END)

Re: How to calculate conditions

Posted by: John

Date: September 03, 2007 07:20AM

I changed the [SFn] fields type to NUMBERS and now Abacus works…. Wow… did not even think of that when working in the code… Thanks for the reminder!

Best Regards,
John

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.