Home

Automatic Field Calculations - Sage ACT! add-ons

1. Ex if field FieldA 5 and FieldB 10 NOT FieldA gt FieldB returns Yes o AND OR XOR logical exclusion EQV logical equivalence IMP logical implication may also be used Ex if field FieldA and 5 FieldB 1 10 FieldA 5 OR FieldB 8 returns Yes Note If you would rather get the numeric equivalent of True and False returned instead of Yes and No adda tilde sign in front of your calculation 1 will be returned instead of Yes and 0 instead of No FieldA 5and51 10 and FieldC FieldA gt A FieldB then FieldC No FieldC FieldA gt FieldB then FieldC 0 Automatic Field Calculations for ACT User s Manual 21 Functions Automatic Field Calculations can accept a number of different functions The function names are not case sensitive Text String functions Even though not always necessary it is recommended to always surround strings with quotes o Gis really an operator It concatenates strings Ex if FieldA Mr and FieldB Huffman then FieldA amp FieldB returns Mr Huffman To insert a space use the underscore character as ACT won t allow a space in the arguments FieldA 6 _ S FieldB returns Mr Huffman o Ucase will convert to uppercase Ex if FieldA Chicago then UCase FieldA returns CHICAGO o LCase will convert to lowercase Ex if FieldA Chicago then LCase FieldA returns chicago o Fcase will capitalize every first letter of the words of a string
2. The rate and nper arguments must be calculated using payment periods expressed in the same units For example if rate is calculated using months nper must also be calculated using months For all arguments cash paid out such as deposits to savings is represented by negative numbers cash received such as dividend checks is represented by positive numbers rate interest rate per period For example if you get a car loan at an annual percentage rate APR of 10 percent and make monthly payments the rate per period is 0 1 12 or 0 0083 nper total number of payment periods in the annuity For example if you make monthly payments on a four year car loan your loan has a total of 4 12 or 48 payment periods pv present value or value today of a series of future payments or receipts For example when you borrow money to buy a car the loan amount is the present value to the lender of the monthly car payments you will make fv optional future value or cash balance you want after you ve made the final payment For example the future value of a loan is 0 because that s its value after the final payment However if you want to save 50 000 over 18 years for your child s education then 50 000 is the future value If omitted O is assumed type optional when payments are due Use 0 if payments are due at the end of the payment period or use 1if payments are due at the beginning of the period If omitted O is assumed Aut
3. returns 1000 opens the file changes the value to 1001 and saves the file NOTE if the file is not saved in the Data folder you will need to specify the complete path like C MyDocuments AutoNumber txt Automatic Field Calculations for ACT User s Manual 25 Date Time functions o NowQ will return the current date and time Ex Now returns 9 14 2004 9 10 50 PM o TimeQ will return the current time Ex Time returns 9 10 50 PM o Day will extract the day from a date Ex if FieldA 2004 08 31 then Day FieldA returns 31 o Weekday will return the day of the week Sunday 1 Monday 2 etc Ex if FieldA 2004 08 31 then Weekday FieldA returns 3 o Weekdayname will return the name of the day Ex if FieldA 2004 08 31 then Weekdayname FieldA returns Tuesday o Month will extract the month from a date Ex if FieldA 2004 08 31 then Month FieldA returns 8 o Monthname will return the name of the month Ex if FieldA 2004 08 31 then Monthname FieldA returns August o Year will extract the year from a date Ex if FieldA 2004 08 31 then Year FieldA returns 2004 o Hour Minute and Second will extract the hours minutes and seconds of a date time Ex if FieldA 9 10 50 PM then Hour FieldA returns 21 for 9PM o Age Birthdate ReferenceDate will return the age of a date in years as of the date of the reference date The reference date is optional If not mentioned to
4. Displays the date according to your locale s long date format Windows Control Panel Displays the date according to your locale s short date format Windows Displays the time according to your locale s long time format Displays the time in 12 hour format using hours and minutes and the AM PM designator Displays a time using the 24 hour format Displays the long date and short time according to your locale s format Displays the long date and long time according to your locale s format Displays the short date and short time according to your locale s format Displays the month and the day of a date Formats the date and time as Greenwich Mean Time GMT Formats the date as the year and month Displays the date as ddddd the time as ttttt if one or both are specified Displays the day 1 to 31 Displays the day using 2 digits 01 to 31 Displays the day using 3 characters Sun to Sat Displays the full day Sunday to Saturday Displays the date in Short date format your Windows settings Displays the date in long time format your Windows settings Displays the month using 2 digits 01 to 12 Displays the month using 3 characters J an to Dec Displays the full month J anuary to December Displays the time in AM PM format Displays the day 1 to 366 Displays the year using 2 digits Displays the full year 1000 to 9
5. Ex if FieldA A1 HARDWARE then Fcase FieldA returns A1 Hardware o FcaseL will capitalize every first letter of the words of a string and make all other letters lowercase Ex if FieldA A1 haRDWARE then Fcase FieldA returns A1 Hardware o Trim will remove leading and trailing spaces Ex Trim Chicago returns Chicago o Ltrim will remove leading spaces Ex Ltrim Chicago returns Chicago o Rtrim will remove trailing spaces Ex Rtrim Chicago returns Chicago o Len will return the number of characters in a string Ex if FieldA Chicago then Len FieldA returns 7 o Space x will insert x spaces Ex space 10 returns 10 spaces o String value will force to consider value as a string even if it is numeric or currency Ex String 65 00 amp taxes returns 65 00 tax whereas 65 00 amp taxes would return 65 taxes o Val returns a number in a string It stops at the first character it does not recognize as part of a number Ex if FieldA 12C then Val FieldA returns 12 Automatic Field Calculations for ACT User s Manual 22 o Left value _of_chars returns the first _of_ chars characters from the value starting from the left Ex if FieldA 414 555 1212 then Left FieldA 3 414 o Right value _of_chars returns the first _of_chars characters from the value starting from the right Ex if FieldA 414 555 1212 then Left FieldA 8 555 1212 o Mid
6. For a group calculation trigger you need to be in a group view 5 Automatic Field Calculations Pro m Calculations Series Automation Options Help Select a contact calculation or calculation series i User1 User2 User4 User5 User7 User8 Current record Current lookup O All records Simply select the calculation you want to run the records it should modify and click Run Automatic Field Calculations for ACT User s Manual 9 Running in manual mode from a button in your layout To have Automatic Field Calculations add a button to your layout in design layout mode add a label you need to select the text tool under Drawing to your layout Toolbox Custom Drawing h Pointer A Text N E Image E Rectangle O Ellipse Then type the following text for the label tc ButtonCaption CalculationName e tcis acode for Automatic Field Calculations to recognize the label e ButtonCaption is the text that will appear on the button e CalculationName is the name of your calculation v tc Copy Date DateCopy Here is an example Copy Date Size and location of the button The button will be placed at the same location as the label It will have the same width as your label so you may resize it as needed The height is automatic and therefore not tied to the height of the label When the button is clicked the specified calculation will be run NOTE If you want
7. fv type returns the number of periods for an annuity based on periodic fixed payments and a fixed interest rate For all arguments cash paid out such as deposits to savings is represented by negative numbers cash received such as dividend checks is represented by positive numbers rate interest rate per period For example if you get a car loan at an annual percentage rate APR of 10 percent and make monthly payments the rate per period is 0 1 12 or 0 0083 pmt payment to be made each period Payments usually contain principal and interest that doesn t change over the life of the annuity pv present value or value today of a series of future payments or receipts For example when you borrow money to buy a car the loan amount is the present value to the lender of the monthly car payments you will make fv optional future value or cash balance you want after you ve made the final payment For example the future value of a loan is 0 because that s its value after the final payment However if you want to save 50 000 over 18 years for your child s education then 50 000 is the future value If omitted O is assumed type optional when payments are due Use 0 if payments are due at the end of the payment period or use 1if payments are due at the beginning of the period If omitted 0 is assumed Pmt rate nper pv fv type returns the payment for an annuity based on periodic fixed payments and a fixed interest rate
8. 8 User1 User2 P ne 4 4 Sl la 4 lt l gt NOTE As you can see in this last screen you do have the option to run the Calculation wizard to create New calculations from here Automatic Field Calculations for ACT User s Manual 15 NOTE Calculation series are shown in between brackets to differentiate them from calculations Automatic Field Calculations Pro m i Calculations Series Automation Options Help Select a contact calculation or calculation series Can User1 User2 UUser4 User5 User7 User8 Current record Current lookup O All records Running calculation series You run calculation series the same way you run a calculation See Running a calculation in manual mode and Running a calculation in automatic mode above Calculation series are always shown in between brackets The only difference if for the layout buttons you need to enclose the series name in between brackets Automatic Field Calculations for ACT User s Manual 16 Multiple databases In case you are using multiple databases you have seen that you may define the scope of calculations and calculations series in the wizards If you wish to change the scope of a calculation or a series it can easily be done by editing the calculation or series But if you have more than one calculation or series to edit or if the calculation or series you want to edit is not listed becaus
9. 999 Displays the hours 0 to 12 12 hour format Displays the hours 00 to 12 12 hour format Displays the hours 0 to 24 Displays the hours 00 to 24 Displays the minutes 0 to 59 Displays the minutes 00 to 59 Displays the seconds 0 to 59 Displays the seconds 00 to 59 Automatic Field Calculations for ACT User s Manual 29 tt Displays AM or PM Ex Format 01 15 2004 dddd Thursday Format Now T 12 59 47 PM Run function The syntax of the Run function is the following o Run path_of_file_to_be_run parameters launches the file if it is an executable or opens the file with the program associated to its extension if any parameters optional parameters to be sent to the program at startup if any MessageBox function The syntax of the MessageBox function is the following o MessaegBox your_ message pops up a simple message window which the user can dismiss by clicking OK NOTE while the message window is displayed all calculations are halted The user will need to dismiss the window to continue working with ACT Automatic Field Calculations for ACT User s Manual 30 Financial functions O FV rate nper pmt pv type returns the future value of an annuity based on periodic fixed payments and a fixed interest rate The rate and nper must be calculated using payment periods expressed in the same units For example if
10. Another efficient and affordable ACT Add On by e ponenciel Automatic Field Calculations for ACT 2008 and up v 6 0 http www exponenciel com Automatic Field Calculations for ACT User s Manual 2 Table of contents P rpose OF the AIC ON a 3 O O E EREA 3 Upgrading from TriggerCalc Pro ai cet 3 Tnst llation Procede cia ica 3 Adding icons to other views AAA AAA ANA 4 USER S MANUAL corso cove wesc beso ai 5 Creating a CAC ATION A AAA A 5 ANUAL 8 lo o sraoin wis coteanea eotuven A ENE i E eE ias 8 Running a calculation in manual MOAE ccssscesssecssecesecesseesssesssessaeceeeessseseseseseesseeesssessaeees 8 Running in manual mode from the icon in your ACT toolbar o ooconoccnoconocccinnnconnccnnocanoss 8 Running in manual mode from a button in your layout ccooccooccnonononnnoonanoncnonnnnnnncnnnacnnos 9 Running a Calculation in automatic MOE ceseessecesecesseesssesseeceseceecesseessseeeseesseeeseseseseeeaes 10 Example SATA TEC aos saa Madi Made Ean 10 Mu ltipl databases ui A AAA AA 12 THE ISI Report ssnin A iE aa i 12 Creating a calculation SOS ii aia 12 Creating a calculation SOMOS e ia B Running calculation Snes dai aia 15 Mu l pledatabases idad 16 EROS dais 17 Using a function inside another function 5 5 s iesesscdaevsssessedsanvedatasevdssaneddeiansevossneyens edaedepsesoneoneee fa 17 Using comments and indentation cccsccssscssscessessesssscssscesssesss
11. Options Help Selecta d Edit Contact Calculation Series IS Usert Delete Contact Calculation Series User1 Choose the records to modify Current record Curent lookup O All records In the first screen you need to name your series Calculation Series Wizard Create a new calculation series Choose a name for the calculation series The type of records automatically matches the current view Calculation Series name Target records Automatic Field Calculations for ACT User s Manual 14 In the second screen if you have multiple databases you have the option to make the series available to all databases Calculation Series Wizard Multi database environment Specify if you want to make this calculation series available to all databases Multiple databases C Make this calculation series available to all databases In the third and last screen you select the calculations that you want the trigger to run If you select more than one calculation the order in which they are run might be important so make sure they are listed in the right order They will be run from top to bottom Finally click Save to save your calculation trigger Calculation Series Wizard Calculations list Choose the calculations you want the calculation series to perform Add calculations to your calculation series Calculations available Calculations to run User7 User
12. User s Manual 11 In the Field assignments window click in the Calculation column next to the field name and a drop down list will appear will all calculations and calculation series Select the one you want associated with the field Field Assignments Field Assignments Assign a calculation or calculation series to a field to have it run automatically on changing this field Field Calculation All DBs a User 1 User1 User2 User 10 User 2 User 3 User Fields User 4 User1 User2 User3 User4 User 5 User 6 User 7 User 8 User 9 Web Site ZIP Code Make all assignments available to all databases NOTE If you wish to remove a field assignment click the calculation name and select the blank line at the top of the drop down list of calculations Automatic Field Calculations for ACT User s Manual D Multiple databases If you have multiple databases you have the option to extend the scope of your assignment to all databases For this click the All DBs column and select the X in the drop down list Field Assignments Field Assignments o OK Assign a calculation or calculation series to a field to have it run automatically on changing this field Cancel Field Calculation All DBs A Title Trivia User 1 User1 User2 X User 10 User 2 User1 User2 x User 3 User 4 User 5 User 6 User 7 User 8 t User 9 Web Site y Make all assignments available to all database
13. a match it returns True If no match is found it returns False Ex if Field 2 then In Field 1 2 3 returns True Between reference_value value1 value2 returns True if reference value is equal or higher than valuel and equal or lower than value2 If not it returns False Ex if Field 2 then Between Field 1 2 returns True Statistical functions O Min value1 value2 returns the smallest value numeric values only non numeric values are ignored Ex if FieldB 10 and FieldC 20 then Min FieldB FieldC 10 MinC value1 value2 returns the smallest value characters are accepted the sort is based on the characters so in this case 10 is higher than 20 Ex if FieldB ProductA and FieldC ProductB then MinC FieldB FieldC ProductA Max valuel1 value2 returns the highest value numeric values only non numeric values are ignored Ex if FieldB 10 and FieldC 20 then Max FieldB FieldC 20 Automatic Field Calculations for ACT User s Manual 24 o MaxC valuel value2 returns the highest value characters are accepted the sort is based on the characters so in this case 10 is higher than 20 Ex if FieldB ProductA and FieldC ProductB then MaxC FieldB FieldC ProductB o Count valuel value2 counts the number of values that are numeric Ex if FieldB 1 FieldC X and 53 2 then Count FieldB FieldC 53 2 o CountC valuel value2 counts the
14. ate firstdayofweek firstweekofyear returns the specified part of a given date Ex if FieldB 12 12 2003 then DatePart y FieldB 346 346 day of the year You can use the DatePart function to evaluate a date and return a specific interval of time For example you might use DatePart to calculate the day of the week or the current hour The firstda y ofweek argument affects calculations that use the w and ww interval symbols Interval Firstdayofweek firstweekofyear Setting Descr Value Descr Value Descr yyyy Year 1 Sunday 1 Start with week in which q Quarter 2 Monday Jan1 occurs m Month 3 Tuesday y Day of year 4 Wednesday 2 Start with the 1st week that d Day 5 Thursday has at least 4 days in the new w Weekday 6 Friday year ww Week Y Saturday 3 Start with first full week of h Hour the year n Minute Ss Second Firstdayofweek and firstweekofyear are optional If omitted a value of 1is assumed Format function The syntax of the format function is the following o Format expression format Ex if field FieldB 2343 3 then Format FieldB 4 0 00 returns 2343 30 The format argument is a string of characters with one or more of the following characters String formatting A character should appear at this position If there is no character for this position a space is inserted If there are more than one they are applied from right to left Same as except that no space is inserted
15. be written to the target field Automatic Field Calculations for ACT User s Manual In the next window you may type your calculation Unless you are copying and pasting a calculation your best option is to click the Open editor button to access Automatic Field Calculations powerful calculation editor Calculation Wizard y Calculation Enter the calculation you want to perform and indicate whether this calculation should be made available to all databases Enter pour calculation A After update C Flash target field C Refresh view check only if required Multiple databases C Make this calculation available to all databases lt Previous Save Cancel In the editor you may use the dropdown lists to insert fields functions and operators Color coding is automatic and helps you define your calculation Syntax Editor JE File Edit ES amp r Fields Used x Functions Cie v Operators Calculation syntax If User 1 UPS PickUp Target field User 2 The more complicated your calculation the more you should be using tabs and comments the button to make it more readable see an example in Using a function inside a function below Once you are done close the window and say yes to the Do you want to keep your changes prompt You will be taken back to the last screen of the calculation wizard If you are satisfied with your calculation click the Save
16. button You may create as many calculations as you want Automatic Field Calculations for ACT User s Manual 8 After update After the calculation field has been updated you have the option to have it flash in red for a couple of seconds This is just a visual cue that the target field has been modified Note it does not work on checkbox fields You also have the option to refresh the view This may be useful in some rare instances when the changes do not appear in the ACT window unless you refresh the view You should leave it uncheck unless you really need it Multiple databases If you have more than one database you have the option to make your calculation available to only the current database or on the contrary to all databases If your databases share the same field structure or if the calculation uses fields that are common to all your databases and you need the calculation to work in all databases check the Make this calculation available to all databases box Otherwise leave it uncheck Running a calculation in manual mode You have two options to run a calculation in manual mode Running in manual mode from the icon in your ACT toolbar Click the Automatic Field Calculations icon in the ACT toolbar Make sure you are in the correct view if you want to run a contact calculation trigger you need to be in a contact view If you want to run a company calculation trigger then you need to be in a company view
17. day s date is used Ex if FieldA 12 31 1960 and today s date is 12 31 2008 then Age FieldA returns 48 if FieldA 12 31 1960 then Age FieldA 12 31 2010 returns 50 o DateDiff interval date1 date2 firstdayofweek firstweekofyear returns the number of time intervals between two specified dates Ex if FieldB 12 12 2003 and FieldC 12 15 2003 then datediff d FieldB FieldC 3 You can use the DateDiff function to determine how many specified time intervals exist between two dates For example you might use DateDiff to calculate the number of days between two dates or the number of weeks between today and the end of the year To calculate the number of days between date1 and date2 you can use either Day of year y or Day d When interval is Weekday w DateDiff returns the number of weeks between the two dates If date1 falls on a Monday DateDiff counts the number of Mondays until date2 It counts date2 but not date If interval is Week ww however the DateDiff function returns the number of calendar weeks between the two dates It counts the number of Sundays between date1 and date2 DateDiff counts date2 if it falls on a Sunday but it doesn t count date1 even if it does fall on a Sunday Automatic Field Calculations for ACT User s Manual 26 If date1 refers to a later point in time than date2 the DateDiff function returns a negative number Interval Firstdayofweek firstweeko
18. ds TE Note that the Target records type box is dimmed out It is just indicative of the type of calculation you are creating The value is automatically set based on the view you were in when you launched Automatic Field Calculations Automatic Field Calculations for ACT User s Manual 6 In the second screen you need to choose a Target field that is the field in which the result of the calculation will be written and overwriting conditions Calculation Wizard Target field and overwriting conditions Select target field as well as overwriting conditions Choose a target field User 1 x Choose an overwriting condition Never overwrite Append existing value with Overwrite if calculation result is not empty Always overwrite There are 4 overwriting condition options e Never overwrite the result of the calculation will only be written to the target field if the target field is empty e Append existing value with the result is appended to the end of the data currently in the target field preceded with the append character If no data is in the field the result will be written to the field without append character e Overwrite if calculation result is not empty the result will only be written if it is not empty So if the field contains data and the result is empty Automatic Field Calculations will not blank out the field e Always overwrite the result will always
19. due at the end of the payment period or use 1if payments are due at the beginning of the period If omitted 0 is assumed Rate nper pmt pv fv type guess returns the interest rate per period for an annuity For all arguments cash paid out such as deposits to savings is represented by negative numbers cash received such as dividend checks is represented by positive numbers Rate is calculated by iteration Starting with the value of guess Rate cycles through the calculation until the result is accurate to within 0 00001 percent If Rate can t find a result after 20 tries it fails If your guess is 10 percent and Rate fails try a different value for guess nper total number of payment periods in the annuity For example if you make monthly payments on a four year car loan your loan has a total of 4 12 or 48 payment periods pmt payment to be made each period Payments usually contain principal and interest that doesn t change over the life of the annuity pv present value or value today of a series of future payments or receipts For example when you borrow money to buy a car the loan amount is the present value to the lender of the monthly car payments you will make fv optional future value or cash balance you want after you ve made the final payment For example the future value of a loan is 0 because that s its value after the final payment However if you want to save 50 000 over 18 years for your child s ed
20. e it is not available to the current database go to the Options gt Multiple databases menu Automatic Field Calculations Pro Elo Calculations Series Automation Options Help Preferences Select a contact calculation or a Multiple New Series Integrate with ACT Choose the records to modify Current record Current lookup O All records In the Multiple databases window you see all calculation and series including those from other databases and you may change the scope of one or more calculation or series by using the radio buttons Multiple Databases Calculation or series settings Freight Series 1 User1 User2 User4 User5 User7 User8 Scope Current database O All databases Other database ACT Demo You are viewing all calculations and series no matter the database Automatic Field Calculations for ACT User s Manual 17 Errors In case the calculation is invalid like 1 divided by 0 or 50 A Automatic Field Calculations will return ERR Note numeric or currency fields in ACT will not allow accept this message therefore the field will remain blank or will be blanked out if it previously had a value Therefore you may want to run tests before you convert your fields to numeric or currency so that you can be made aware of any errors When you are sure the calculations are properly built you could then convert your fields to numeric or curre
21. ecimal places Ex if FieldA 123 4875 then Round FieldA 3 123 488 Automatic Field Calculations for ACT User s Manual 23 If function The syntax of the if function is the following O If logical_test value_if true value_ if false Ex if field FieldB 1 then If FieldB 1 Yes No returns Yes Thevalue if trueand value if false arguments are optional Omit them if you don t want the value of the field to be changed Ex if field FieldB 0 then If FieldB 1 Yes will not modify the target field Ex if field FieldB 1 then If FieldB 1 No will not modify the target field If on the contrary you wish to blank out the target field then use double quotes Ex if field FieldB 0 then If FieldB 1 Yes will blank out the target field Comparison functions O Case reference_value value1 result1 value2 result2 else resultx compares valuel value2 to reference value As soon as it finds a match it returns the associated value If no match is found it returns the value associated with the keyword Else Note the use of to associate a value to a result Ex if Field 2 then Case Field 1 Male 2 Female returns Female If Field J apan then Case Field US Domestic Canada North America Mexico North America Else International Returns International In reference_value value1 value2 compares valuel value2 to reference value As soon as it finds
22. er views To add icons to other toolbars run Automatic Field Calculations and go to the Options gt Integrate with ACT menu Check the views you want to add icons to and restart ACT You will need to re start ACT for the changes to take effect r Integrate with ACT Toolbar button added to the following views pas ie Cancel When ACT re starts whatever icons you asked to be created should be there to launch Automatic Field Calculations Automatic Field Calculations for ACT User s Manual USER S MANUAL Creating a calculation To create a calculation run Automatic Field Calculations from the contact company or group view depending on which type of calculation you want to create Then go to the Calculations menu and choose New contact calculation which would read New company calculation or New group calculation depending on the view 4 Automatic Field Calculations Pro lle Calculations Series Automation Options Help Edit Contact Calculation ulation series Delete Contact Calculation y Sa Choose the records to modify Current record Curent lookup O All records Then followthe wizard In the first screen you need to choose a name for your calculation Calculation Wizard Create a new calculation Choose a name for the calculation The type of records automatically matches the current view Calculation name Target recor
23. eseecesesesseeseseseseessssesseeeses 17 Yes No FEAS io cacao eee A taa 18 Using Automatic Field Calculations on a Network cccsecsscesscesssesseesseeeeseeesseesseeeeeeceseeesaes 18 Registering Automatic Field Calculations ccscecssecssscesseesssessseceseceseeeseeseeeeeseeeseeeseeeseseeeaes 18 A A cba clag sod tae a eae Sede edaeeez E 18 REFERENCE GUIDE vasta ii iii 19 e A A A Alta thee ced pales ululiGee artes ae e tn 19 AO O A enaa a aS 19 Operator precedence rra rios 19 PUNCHONS 0 arado 21 Text String functions iaa 21 Mathematical funcions pitt a a hated E E Aai 22 Tf FUNCH ON is Se ir a NRT A 23 Comparison TIN COS ai AA Lab ainai 23 St tistical fUNCHODS A E A A A A a eee eee 23 A t Nurmber UNC el ia a a a a ET id 24 Date Time LUNCH ONS AA PP aa net n E 25 POLA IICA neuzee ie dis 27 RUN FUNCH O AAA A ER E s 29 MessageBox FITCH O A A e 29 Finan ial fUNCtONS cu co ese s oe ds taeda tek pi aa te cas esse da i Harveanteaneads 30 EX mples CF Cale batons q id 33 Automatic Field Calculations for ACT User s Manual 3 INTRODUCTION Purpose of the add on Automatic Field Calculations for ACT allows you to automatically run calculations involving fields How it works Automatic Field Calculations allows you to run calculations or calculation series A calculation series may contain as many calculations as you want Calculation or calculation series may be run e automatically on changing or exitin
24. eturns a value depending on another value In this example the reference value is the COuntry field If Country is United States the calculation returns Domestic If Canada North America ete Function used Case lt value gt lt valuel gt lt result1 gt lt valuez gt lt result2 gt else lt result3 gt Case Country United States Domestic Canada North America Mexico North America else International
25. fyear Setting Descr Value Descr Value Descr yyyy Year 1 Sunday 1 Start with week in which q Quarter 2 Monday Jan1 occurs m Month 3 Tuesday y Day of year 4 Wednesday 2 Start with the 1st week that d Day 5 Thursday has at least 4 days in the new w Weekday 6 Friday year ww Week 7 Saturday 3 Start with first full week of h Hour the year n Minute S Second Firstdayofweek and firstweekofyear are optional If omitted a value of 1is assumed DateAdd interval number date returns a date to which a specified time interval has been added Ex if FieldB 12 12 2003 then DateAdd d 3 FieldB 12 15 2003 You can use the DateAdd function to add or subtract a specified time interval from a date For example you can use DateAdd to calculate a date 30 days from today or a time 45 minutes from now To add days to date you can use Day of Year y Day d or Weekday w The DateAdd function won t return an invalid date The following example adds one month to J anuary 31 DateAdd bat aa ae 31 Jan 95 In this case DateAdd returns 28 Feb 95 not 31 Feb 95 If date is 31 J an 96 it returns 29 Feb 96 because 1996 is a leap year Interval Setting Descr Year Quarter Month Day of year Day Weekday Week Hour Minute os 73 3 ak la Second Automatic Field Calculations for ACT User s Manual 27 o DatePart interval d
26. g a field e manually from the Automatic Field Calculations main window e manually by clicking a button in your layout Automatic Field Calculations works with Contact fields as well as Company and Group fields The method for creating calculations is the same Automatic Field Calculations will automatically detect the active view Upgrading from TriggerCalc Pro Triggers and Calculations from TriggerCalc and TriggerCalc Pro will be preserved Field assignments have changed so you will have to recreate them Installation procedure Download the program file from our download page and double click it to start the installation An icon should have been added to your Contact toolbar to launch Automatic Field Calculations directly from within ACT Note that contrarily to versions supporting previous ACT versions and because of the changes in ACT add ons are now DLL files and not executable files This means that they are automatically initialized by ACT when placed in ACT plugin folder a subfolder of the main ACT directory and cannot be started from the Windows Start menu anymore This is why you are not given a choice of installation directory during the installation process ACT should automatically add an icon to the Contact Detail window and an item to the Tools menu Icons and menu items are now the only ways to start your Exponenciel add on Automatic Field Calculations for ACT User s Manual Adding icons to oth
27. if there is no character at this position Reverses the order in which and amp are applied ie becomes from left to right Converts all characters to lowercase VIA Converts all characters to uppercase Ex Format AbcD gt ABCD similar to the UCase function Format 4142345678 414 234 5678 Format 2345678 _ 234 5678 Format 2345678 234 567 8 Number formatting blank The digit is returned without formatting 0 A digit is supposed to appear at this position If there is none then 0 is displayed If the Automatic Field Calculations for ACT User s Manual 28 format string contains more 0 than the number to be formatted extra Os are added in front or at the end Similar to O except that nothing is inserted if no digit is to appear Combined with 0 or specifies the number of digits which should appear on each side of the decimal character Multiplies the number by 100 and adds a sign Inserted in series of 0 or indicates the thousand separator A double comma indicates that the number should be divided by 1000 E Ifthe format string contains at least a O or converts the number to scientific notation Ex Format 2343 3 0000 00 02343 20 Format 2343 3 444 00 2 343 00 Format 45 44HHPF 45 Date and time formatting
28. lse If you want to write to a Yes No field you could use True False or a numeric value 0 is false anything else is true Example of reading a Yes No Field If YesNoField value_when_checked value_when_not_checked Example of writing If FieldA True False If FieldA 1 0 If FieldA NumericField1 NumericField2 Using Automatic Field Calculations on a network If you are in a network environment and wish to share your calculations and calculations series among all users go to Edit gt Preferences and choose a directory accessible to all workstations for your data files Repeat the same operation on all networked computers If you already have created calculations on a workstation simply copy the content of the data folder to the server and reference the new data location with the method described above on all workstations By default the data folder is at C Program Files ACT ACT for Windows Plugins Exponenciel Automatic Field Calculationa Data Registering Automatic Field Calculations You may buy licenses from Exponenciel Registration is based on ACT user names so you will have to supply your ACT username when registering Once you get your registration code simply go to the Help About menu Support For support please contact support exponenciel com Automatic Field Calculations for ACT User s Manual 19 REFERENCE GUIDE Operators Basic Operators You
29. may use any of these four basic operators plus minus multiplied by divided by You may use parentheses as well Examples of calculations You may use any combination of the above operators with or without parentheses For instance FieldA1 100 0 05 gt 5 of FieldA 100 FieldA H FieldB FieldC gt FieldA FieldB FieldC FieldA 125 FieldB 5 gt etc FieldA gt Copies FieldA to the target field 0 gt Resets target field to O FieldA H FieldB gt Concatenates FieldA and FieldB w a space between etc Operator precedence Typically the operator precedence the order in which the operators are evaluated is which means that 50 3 1 equals 151 and not 200 you first multiply then add Automatic Field Calculations follows this rule for simple calculations like the one above but for more complicated calculations make sure you use parenthesis to help Automatic Field Calculations determine in which order it should proceed with the calculations in the example above it would mean writing 50 3 1 instead of 50 3 1 Besides the 4 basic operators you may also use Boolean operators Yes is returned if the comparison is true No if the comparison is false o gt lt gt lt lt gt allow to compare values Ex if FieldA 5 and FieldB 10 FieldA gt A FieldB returns No o NOT is used to perform a logical negation Automatic Field Calculations for ACT User s Manual 20
30. ncy Using a function inside another function It is possible to use a function in one or more arguments of another function Make sure you use parenthesis properly though as they are critical to avoid errors For instance you may create a function like this one If FieldA True Avg FieldB FieldC FieldD n a The Avg function is an argument of the IF function Or like this Min if FieldB gt 1000 1000 if FieldB gt 500 500 0 FieldC This example of multi level calculation could be explained like this The target field will be the lowest value of FieldC and a value defined like this if FieldB is higher than 1000 then the value is 1000 If FieldB is higher than 500 but lower than 1000 then the value is 500 If FieldB is lower than 500 the value is 0 Using comments and indentation You may want to use comments and indentation to make your code more readable as in Syntax Editor JE File Edit A A r ex Fields User 1 e Functions If Operators v Calculation syntax If Country United States Domestic S Ifi Country Canada North America Canada If Country Mexico North America Mexico International Other i Target field User 2 See how it helps keeping track of opening closing parentheses Automatic Field Calculations for ACT User s Manual 18 Yes No Fields Yes No fields return a value of True Fa
31. number of values that are not blank Ex if FieldB 1 FieldC is empty blank and 53 2 then CountC FieldB FieldC 53 2 o CountBlank valuel value2 counts the number of values that are blank Ex if FieldB 1 FieldC is empty blank and 53 2 then CountBlank FieldB FieldC 53 1 o CountlIf logical_test value1 value2 counts the number of values that meet the given condition Ex if FieldB Yes FieldC Yes 53 No then CountIf Yes FieldB FieldC 53 2 o Avg value1 value2 calculates the arithmetic mean of the values numeric values only Blank and characters are ignored Ex if FieldB 10 FieldC is empty blank and 53 20 then Avg FieldB FieldC 53 15 o Large k valuel value2 returns the k th largest value numeric values only Blank and characters are ignored Ex if FieldB 10 FieldC 12 and 53 20 then Large 2 FieldB FieldC 53 12 o Small k value1 value2 returns the k th smallest value numeric values only Blank and characters are ignored Ex if FieldB 10 FieldC 12 and 53 20 then Small 3 FieldB FieldC 53 10 AutoNumber function o AutoNumber path_of_file_containing_next_number_to_be_used opens the specified path reads the first line and returns it If it is a number it will automatically increment it by one Ex If you open Notepad type 1000 save it as a text file called AutoNumber txt in the Data folder AutoNumber AutoNumber txt
32. omatic Field Calculations for ACT User s Manual 32 O PV rate nper pmt fv type returns the present value of an annuity based on periodic fixed payments to be paid in the future and a fixed interest rate The rate and nper arguments must be calculated using payment periods expressed in the same units For example if rate is calculated using months nper must also be calculated using months For all arguments cash paid out such as deposits to savings is represented by negative numbers cash received such as dividend checks is represented by positive numbers rate interest rate per period For example if you get a car loan at an annual percentage rate APR of 10 percent and make monthly payments the rate per period is 0 1 12 or 0 0083 nper total number of payment periods in the annuity For example if you make monthly payments on a four year car loan your loan has a total of 4 12 or 48 payment periods pmt payment to be made each period Payments usually contain principal and interest that doesn t change over the life of the annuity fv optional future value or cash balance you want after you ve made the final payment For example the future value of a loan is 0 because that s its value after the final payment However if you want to save 50 000 over 18 years for your child s education then 50 000 is the future value If omitted O is assumed type optional when payments are due Use 0 if payments are
33. rate is calculated using months nper must also be calculated using months For all arguments cash paid out such as deposits to savings is represented by negative numbers cash received such as dividend checks is represented by positive numbers rate interest rate per period For example if you get a car loan at an annual percentage rate APR of 10 percent and make monthly payments the rate per period is 0 1 12 or 0 0083 nper total number of payment periods in the annuity For example if you make monthly payments on a four year car loan your loan has a total of 4 12 or 48 payment periods pmt payment to be made each period Payments usually contain principal and interest that doesn t change over the life of the annuity pv optional present value or lump sum of a series of future payments For example when you borrow money to buy a car the loan amount is the present value to the lender of the monthly car payments you will make If omitted 0 is assumed type optional when payments are due Use 0 if payments are due at the end of the payment period or use 1if payments are due at the beginning of the period If omitted 0 is assumed IPmt rate per nper pv fv type returns the interest payment for a given period of an annuity based on periodic fixed payments and a fixed interest rate The rate and nper arguments must be calculated using payment periods expressed in the same units For example if rate is calcula
34. s If you want all you assignments to be available to all databases check the Make all assignments available to all databases checkbox This will add an X next to all calculations as well as automatically insert an X next to the calculations you add in the future NOTE In the event that in a particular database two calculations have been assigned to the same field one being database specific and one being for all databases only the database specific assignment will run The assignment Report To view a list of your assignments do Automation gt Assignment Report Automatic Field Calculations Elo Calculations Series Automation Options Help Assign to Fields Select a contact i ome Lin User1 Userd Close Choose the records to modify Current record Current lookup O All records Automatic Field Calculations for ACT User s Manual 13 Creating a calculation series If you wish to run more than one calculation in one operation you need to create a calculation series A calculation series may contain as many calculations as you want The method to create a calculation trigger is similar to the one used to create a calculation Go to the Series menu and choose New Contact Calculation Series or company or group depending on the active view to launch the Calculation series wizard Automatic Field Calculations Pro 0 Calculations Series Automation
35. ted using months nper must also be calculated using months For all arguments cash paid out such as deposits to savings is represented by negative numbers cash received such as dividend checks is represented by positive numbers rate interest rate per period For example if you get a car loan at an annual percentage rate APR of 10 percent and make monthly payments the rate per period is 0 1 12 or 0 0083 per payment period in the range 1 through nper nper total number of payment periods in the annuity For example if you make monthly payments on a four year car loan your loan has a total of 4 12 or 48 payment periods pv present value or value today of a series of future payments or receipts For example when you borrow money to buy a car the loan amount is the present value to the lender of the monthly car payments you will make fv optional future value or cash balance you want after you ve made the final payment For example the future value of a loan is 0 because that s its value after the final payment However if you want to save 50 000 over 18 years for your child s education then 50 000 is the future value If omitted O is assumed type optional when payments are due Use 0 if payments are due at the end of the payment period or use 1if payments are due at the beginning of the period If omitted 0 is assumed Automatic Field Calculations for ACT User s Manual 31 O NPer rate pmt pv
36. to run a calculation series make sure you enclose the series name in between brackets as in tc Series Series Automatic Field Calculations for ACT User s Manual 10 Running a calculation in automatic mode To run a calculation in automatic mode you need to assign the calculation to a field Assigning a calculation trigger to a field means that whenever you change the value of this field the calculation will be run Example Generally speaking you would want to assign the calculation trigger to all of the fields involved in the calculation Here is an example to illustrate which fields need to be assigned a calculation trigger Let s say you created a calculation that adds User1 to User2 and writes the result in User3 You would want to assign your calculation to User1 and to User2 so that every time User1 and User2 are changed User3 is automatically re calculated Note in this scenario do not associate the calculation to User3 If you do when the calculation will write the result to User3 it will be automatically re triggered entering an endless loop To assign a calculation to a field go to Automation gt Assign to Fields SH Automatic Field Calculations Pro 1 Calculations Series Automation Options Help Assign to Fields E f Lan l gt Choose the records to modify Current record Curent lookup O All records Automatic Field Calculations for ACT
37. ucation then 50 000 is the future value If omitted O is assumed type optional when payments are due Use 0 if payments are due at the end of the payment period or use 1if payments are due at the beginning of the period If omitted 0 is assumed guess value you estimate will be returned by Rate If omitted guess is 0 1 10 percent Automatic Field Calculations for ACT User s Manual 33 Examples of calculations You can access a number of commented examples by using the File gt Examples menu of the Syntax editor or by clicking the Ex button on the editor s toolbar Examples AutoNumber Example Case Advanced example Case With comparison operators Checkbox Check the value of a che Comparisons Between function Comparisons In function Comparisons Operators Dates Add months weeks days to e Dates Calculate an age Dates Compare 2 dates Dates Convert to Text Dates First Day of Following Month Dates First Day of Following Year Dates Make sure a date is a weekd Dates Today s date Fields Add fields together Fields Blank out a Field Fields Copy the content of a field to Fields Move values down a table of General Nested functions If Advanced example If Simple example Numbers Rounding up a number Operators AND and OR Rev May 5 2008 v 6 0 File Edit a sx Calculation syntax Fields Case Simple example R
38. value start_pos _of_chars returns of chars from the value starting from the start_posth character If of charsis omitted it returns all characters right of the pos Ex Ex if FieldA 414 555 1212 then mid FieldA 5 3 555 o Replace value char s _to_replace replacement replaces the character s to replace by the replacement Ex if FieldA 24 North Avenue then Replace FieldA e i 24 North Avinui Ex if FieldA 24 North Avenue then Replace FieldA nue 24 North Ave o ReplaceWord value word_to_replace replacement_ word replaces the word to replace by the replacement word Ex if FieldA 24 North Avenue then Replace FieldA Avenue Ave 24 North Ave o Initials value returns a string made of the first letters of each word in uppercase Ex if FieldA Chris Huffman then Initials FieldA CH Mathematical functions o Abs returns the absolute value of a number Ex if FieldA 5 32 then Abs FieldA returns 5 32 o Sgn returns 1if the number is gt 0 0 if the number 0 1if the number is lt 0 Ex if FieldA 5 32 then Sgn FieldA returns 1 o Sqrt returns the square root of a number Ex if FieldA 25 then Sqrt FieldA returns 5 o Int returns the integer portion of a number Ex if FieldA 25 52 then Int FieldA returns 25 o Thecos sin tan exp atan and log functions are supported o Round value _of_decimals returns a number rounded to a specified number of d

Automatic Field Calculations - Sage ACT! add-ons

Contents

Download Pdf Manuals

Related Search

Related Contents