Using IF THEN Calculations in FRx
You should know that IF THEN calculations are not only possible in FRx, you can use them in both the row and the column.
In the Row, use a CAL format code, then place your IF THEN statement in column D Related Rows.
In the column, use a CALC column type, then place your IF THEN statement in the Calc Formula cell.
The syntax is as follows:
If (true/false statement) Then (formula) Else (formula)
Don’t use commas. The ELSE segment is optional. In the row, row codes are used in the true/false statement and formula. For example, “If B200>0 Then B200 Else C215″. In the column, column letters are used instead: “If B>0 Then B Else C”.
These are simple examples but they can get much more complex.
I use an IF THEN most often in the row to switch intercompany receivables and payables between asset and liability sections depending on whether there’s a debit or credit balance.
Here’s another not-so-obvious trick with calculations in FRx. This can be used for various things, but I’ll use the example of multiplying by a currency rate.
Use IF @UNIT() calculations in the row format to define the rate for each unit, then just use a CALC column to multiply by your rate row. This is a bit harder to explain, but you basically end up with something like this in the row format:
MYRATE My exchange rate CAL 1.5678 “0.000000″ NP
NO_RATE No conversion CAL 1 NP
11 Company1 CAL IF @UNIT(COMPANY1) THEN @MYRATE ELSE @12 NP
12 Company2 CAL IF @UNIT(COMPANY2) THEN @MYRATE ELSE @13 NP
13 Company3 CAL IF @UNIT(COMPANY3) THEN @MYRATE ELSE @14 NP
14 Company4 CAL IF @UNIT(COMPANY4) THEN @MYRATE ELSE @15 NP
15 Company5 CAL IF @UNIT(COMPANY5) THEN @NO_RATE ELSE @16 NP
16 Company6 CAL IF @UNIT(COMPANY6) THEN @NO_RATE NP
You then have one GL column and one CALC column in the column layout. Your CALC column would use a formula of B*B11. When you generate the report, FRx will start at row 11 and then fall through each CAL row until it hits the right one.
You can combine this with XR and other functionality to do some pretty cool stuff. There are probably some good, creative uses of this without @UNIT(), too.
Folks, Jeff is one of the lead developers of FRx. Doesn’t get much better than this! Jan
I don’t get any of this please help!
I’d recommend you go to FRx Help and search for IF/THEN/ELSE. Review that, then come back here. Good luck.