I have recently purchased abacus. I think it's a great product I have already utilised basic calculations.
But I'm having trouble understanding the instructions for formulas and commands.
I'm not really a computer programmer so I have difficulty with translating the info.
Could you please provide me with more examples that I can understand for if else formulas and how to change the
format of fields and easier to understand instructions overall.
It is a little frustrating trying to figure out how abacus works with trial and error.
Please help me I need to organise the formulas.
I do have a good understanding of excel with financial formulas and organising lookup and vlookups etc:
please provide me with further information I believe this product can be very useful to me if I can utilise all it's
functions.

Evren,

Here are some calculation examples that will help you understand how Abacus add-on works.

EXAMPLES OF CALCULATIONS


CALCULATING THE AGE OF A CONTACT


If 'Birth Date' is the date of birth field then use one of the following formulae :


Age( [Birth Date] )
DATEDIFF( year, [Birth Date], GETDATE() )
DATEDIFF( month, [Birth Date], GETDATE() ) /12


ADDING FIELDS ( CALCULATING A TOTAL SUM )


[Field 1] + [Field 2] + [Field 3]



MULTIPLICATION OF FIELDS

[Field 1] * [Field 2]



CONVERTING A DATE INTO A STRING


If 'Birth Date' is the date field to convert:


DATENAME(weekday,[Birth Date])+", "+DATENAME(month,[Birth Date])+" "+
DATENAME(day,[Birth Date])+", "+DATENAME(yyyy,[Birth Date])


Ex: 01/11/2004 is converted into string 'Sunday, January 11, 2004'


CAPITALIZING THE FIRST CHARACTER OF A FIELD


Lets use 'User 1' field for example:


UPPER(Left([User 1],1)) + LOWER( Substring([User 1],2,Len([User 1])-1) )



Ex: "Send an Invoice" is converted to "Send an invoice"


COPYING THE CONTENT OF A FIELD TO ANOTHER FIELD


This is really simple.
If 'User 1' is the field to copy to 'User 2', open 'Formula Editor' for field 'User 2' and simply
insert [User 1] as formula: [User 1]


FORMATTING A NUMBER ( Lets assume that 'FieldA' is numeric field )



Format( [FieldA],"$###,###.00" )

Ex: if FieldA contains 2343.3 then it will be formated as $2,343.00


BLANKING OUT A FIELD


Simply type a double quote "" in the 'Formula Editor'
and the target field will be reset to a blank value.



CONDITIONAL CALCULATIONS



Example 1 ( IF...CASE statement usage ) :



IF( [Country]='Canada' OR [Country]='United States' ) THEN
   [Country] + ' is in North America'
ELSE

   'International'
END




Example 2 ( simple CASE statement usage ) :



CASE( [Country] )
   WHEN 'United States' THEN 'Domestic'
   WHEN 'Canada' THEN 'North America'
   WHEN 'Mexico' THEN 'North America'
   ELSE 'International'
END



Example 3 ( simple CASE statement usage ) :



CASE [State]
   WHEN 'NY' THEN ' State is New York'
   WHEN 'NJ' THEN ' State is New Jersey'
   WHEN 'NY' THEN ' State is New York'
   WHEN 'NV' THEN ' State is Nevada'
   ELSE 'State is ' + [State]
END



Example 4 ( searched CASE statement usage ) :



CASE
   WHEN UPPER([First Name]) = 'CHERYL' THEN 'Contact ' + [Contact] + ', type 1'
   WHEN SUBSTRING([Last Name],1,1) = 'J' THEN 'Contact ' + [Contact] + ', type 2'
   WHEN ([First Name] = 'Sean')AND([State] = 'NY') THEN 'Contact ' + [Contact] + ', type 3'
   ELSE 'Unsupported contact type'
END



For further examples see functions reference [www.actpresentation.com]
Every function description provides formulae examples.

Financial functions (with examples) are described here [www.actpresentation.com]

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



Edited 1 time(s). Last edit at 03/24/2007 12:02PM by Admin.