Formula Examples

MayFlower develops specialty software products for Lotus Notes & Domino.

Chapter 7 - Formula Examples

The following are actual examples taken from the various Task Document, of formulas that were useful.

Creating New fields using a Calculation
This example shows a metric conversion from volume to costs:

Formula
Field FeedStockCosts := FeedStockFactor*Volume/220.462;
Field VariableCosts := VariableCostFactor*Volume/220.462;
Field FixedCosts := FixedCostFactor*Volume/220.462;
Field TotalCosts := TotalCostFactor*Volume/220.462;
Field PBT := NetRevenue-TotalCosts;
SELECT @All

Creating a new Key field from Three Separate Fields.

The import record had the fields ALPHAPREFIX, NUMBER and DELIVERYPOINT. These had to be concatenated into a new Keyword, called AccountNumber, that was more commonly used. Notice also that the field NUMBER was padded with three zeros "000".

Formula
FIELD AccountNumber := ALPHAPREFIX +
@Right("000" + @Text(NUMBER);3) + DELIVERYPOINT;

Calculating Derived Fields During an Import.
This example demonstrates calculating the field CompanyAddress from 5 separate address lines. If there in no information in any field, no @NewLine functions are entered.

Formula
FIELD Sector := "Main Sector";
FIELD Status := "4. Customer - Active";
FIELD CompanyAddress :=
@If(@Length(adr1) > 0; adr1; "") +
@If(@Length(adr2) > 0; @NewLine + adr2; "") +
@If(@Length(adr3) > 0; @NewLine + adr3; "") +
@If(@Length(adr4) > 0; @NewLine + adr4; "") +
@If(@Length(adr5) > 0; @NewLine + adr5; "");
FIELD AccountType := @If(CustomerType = "D"; "Distributor";
CustomerType = "G"; "Global"; CustomerType = "I"; "Intercompany"; CustomerType = "P"; "Preferred";
CustomerType = "T"; "Trade Customer"; "Customer");
FIELD DataSource := "Europe";
FIELD LastComputerLoad := @Now;
FIELD CompanyAffiliation := CustomerGroupCode;
SELECT @All

Consolidating Data from Many Records into One Notes Record

The import record only had the fields MN and LoadVolume. Each record represented a different month. There were 12 volume fields on the form, one for each month. The formula puts the volume information into the proper field, using the holding field LoadVolume temporarily.

This example also demonstrates Initiating Field Values. This is necessary so that form formulas that roll up quarterly information do not return errors.

Formula
MN := @Right(FiscalYearMonth; 2);
FIELD V01 := @If(MN = "01"; LoadVolume; @IsAvailable(V01); V01; 0);
FIELD V02 := @If(MN = "02"; LoadVolume; @IsAvailable(V02); V02; 0);
FIELD V03 := @If(MN = "03"; LoadVolume; @IsAvailable(V03); V03; 0);
FIELD V04 := @If(MN = "04"; LoadVolume; @IsAvailable(V04); V04; 0);
FIELD V05 := @If(MN = "05"; LoadVolume; @IsAvailable(V05); V05; 0);
FIELD V06 := @If(MN = "06"; LoadVolume; @IsAvailable(V06); V06; 0);
FIELD V07 := @If(MN = "07"; LoadVolume; @IsAvailable(V07); V07; 0);
FIELD V08 := @If(MN = "08"; LoadVolume; @IsAvailable(V08); V08; 0);
FIELD V09 := @If(MN = "09"; LoadVolume; @IsAvailable(V09); V09; 0);
FIELD V10 := @If(MN = "10"; LoadVolume; @IsAvailable(V10); V10; 0);
FIELD V11 := @If(MN = "11"; LoadVolume; @IsAvailable(V11); V11; 0);
FIELD V12 := @If(MN = "12"; LoadVolume; @IsAvailable(V12); V12; 0);
FIELD R01 := @If(MN = "01"; LoadNetRevenue; @IsAvailable(R01); R01; 0);
FIELD R02 := @If(MN = "02"; LoadNetRevenue; @IsAvailable(R02); R02; 0);
FIELD R03 := @If(MN = "03"; LoadNetRevenue; @IsAvailable(R03); R03; 0);
FIELD R04 := @If(MN = "04"; LoadNetRevenue; @IsAvailable(R04); R04; 0);
FIELD R05 := @If(MN = "05"; LoadNetRevenue; @IsAvailable(R05); R05; 0);
FIELD R06 := @If(MN = "06"; LoadNetRevenue; @IsAvailable(R06); R06; 0);
FIELD R07 := @If(MN = "07"; LoadNetRevenue; @IsAvailable(R07); R07; 0);
FIELD R08 := @If(MN = "08"; LoadNetRevenue; @IsAvailable(R08); R08; 0);
FIELD R09 := @If(MN = "09"; LoadNetRevenue; @IsAvailable(R09); R09; 0);
FIELD R10 := @If(MN = "10"; LoadNetRevenue; @IsAvailable(R10); R10; 0);
FIELD R11 := @If(MN = "11"; LoadNetRevenue; @IsAvailable(R11); R11; 0);
FIELD R12 := @If(MN = "12"; LoadNetRevenue; @IsAvailable(R12); R12; 0);
Select @All