Most Useful Calculated field Expressions

Below are the most commonly used expressions used in calculated fields. Hope it helps.

- Getting System Preference
IIf ([Is Agent]="Y", SystemPreference("AAA Assister ID Prefix") + Right(RowIdToRowIdNum([Id]),7), "")

- Calculate Age
(Today () - [Birth Date] )  / 365

- LOV Lookup
LookupName("AMS_LOV_APPLTYPE", [Applicant Type])
LookupValue('COMM_BOOLEAN_VALUE', IIf([COB Flg] ='N' or  [COB Flg] Is NULL,'No','Yes'))

- Getting Profile Attribute
GetProfileAttr("ApplicationName")

- Division example
[Assessment Value]/25
IIF([Hire Date] IS NOT NULL,(Today()-[Hire Date])/30,0)
[Equity Value] / [Book Value]

- Multiplication example
[Cost/Share] * [Shares Held]

- ToChar example
IIF([Fax Phone #] IS NOT NULL, '"/fax='+ToChar([Fax Phone #])+'/" <qatest50@qa.test.com>', "")

- Append example
[First Name] + " " + [Last Name]
"" + [Type] + ": " + [Description]
[First Name] + " " + [Middle Name] + " " + [Last Name]

- Getting Application Name
IIF(GetProfileAttr("ApplicationName") = "Siebel Public Sector", "N", [Protect Internal Employee Flag] )

- Getting Parent BC name
IIf (ParentBCName () = "LOY Member", ParentFieldValue ("Member Type"), "")

- Getting Parent BC Field name
IIf (ParentBCName () = "FINS cBanking Request", ParentFieldValue("Company Group Id"), "")
ParentFieldValue("Id") + "_" + [Id]

- Row Id to Number
RowIdToRowIdNum([Id])

- Simple IF example
IIF(IsManagerPosition(), 'N', 'Y')

- Date Time functions
Timestamp ()
IIF(Timestamp()>[Due] AND [Done] IS NULL, "Overdue", "Not Overdue")
Today ()

- Sum the values in MVF
Sum([Client Deposit Balance MVF])

- Count records in MVL
Count("Dealer Trade In")

- Calling business service from calculated field
InvokeServiceMethod ("AAA Calculate Enrolled Product CSR Level Service", "CalculateCSR", "PrimContactId="+[Primary Contact Id]+","+"PlanMedal="+[Plan Primary Coverage Level]+","+"ProductId="+[Product Id],"CSRLevel")

InvokeServiceMethod ("AAA Calculate Applied Premium Assistance Breakdown", "CalcBreakdown", "PID="+[Asset Id]+","+"TPA="+[PD Total Premium Assistance]+","+"PPA="+[Applied APTC],"SPAUsed")

- Link in calculated field
"<a href=# onClick=""window.open('http://10.10.10.79:8080/alfresco/d/d/workspace/SpacesStore/bffe76c5-3d18-4d9d-930a-50b9cd10e477/" + [App PDF Link] + "','_blank');return false;"">" + [App PDF Link] + "</a>"

- Other Examples
GetNumBCRows ("FINS Health Individual Policy", "FINS Member Benefits", '[Policy Coverage Id] ="'+[Id]+'"',"All")

GetNumBCRows ("HLS Case", "HLS Case", "[Status]= 'Closed'", "Sales Rep")

Max([PD Member End Dates])

IfNull([Graphical Policy Status Green], IfNull([Graphical Policy Status Yellow], IfNull([Graphical Policy Status Red], "Current Outage")))

Mid(Today(), 4, 2)

Left(Today(), 2)

Right(Today(), 2)

IfNull([Graphical Policy Status Green], IfNull([Graphical Policy Status Yellow], IfNull([Graphical Policy Status Red], "Current Outage")))

IfNull(Count ("Case QA Template"), 0)

IIf (LPDinId() <> [Created By],"Y","N")

(1.0 - [Cost]/IfNull([Promotional Price],[List Price]))*100.0

IIf (ParentBCName () = "Admin Price List" OR ParentBCName () = "Price List", ParentFieldValue ("Currency Code"), "")

ToChar([Service Length])+ " " +[Service Length UoM]

IIF(ParentFieldValue ("VerItm") LIKE "Inc*","Income", IIF(ParentFieldValue ("VerItm") LIKE "Exp*","Expense", IIF(ParentFieldValue ("VerItm") LIKE "Household C*","Household Composition", IIF(ParentFieldValue ("VerItm") LIKE "Res*","Resource" , "Field"))))

BCHasRows("FINS Health Individual Policy","FINS Member Benefits","[IHP Product Id]='" + [IHP Product Id] + "' AND [Insured Id]='" + [Insured Id] + "' AND ( [PD Policy Status] <> 'Terminated' AND [PD Policy Status] <> 'Lapsed')" ,"All")

LookupName("FINCORP_PROD_ADMIN_CLASS_MLOV", [Product Type])

IIf (([End Date] >= Timestamp()) AND ([Start Date] <= Timestamp()),Y,N)

IIF(ParentBCName() = "FINS Member Benefits", ParentFieldValue("IHP Product"), ParentFieldValue("Product Name"))

Left([Street Address],[Street Address Len])+ [Calculated Address Comma0.5] + [Street Address 2] + [Calculated Address Comma1]  + [City] + [Calculated Address Comma2] + [State]