Creating an IF function for use in

formula wizard
The company I work for is implementing the new Sage X3 PU11 and we are moving our
customizations over, so I thought I'd take an opportunity to share a solution that we are
moving from X3V6 to PU11. This solution will work for anyone with v6.5 through PU11.

Occasionally, we’ve wanted to use some logic in our autojournals’ description field. This will
produce journals with a specific bit of text in the description field to assist accounting
managers with data aggregation, or provide additional info for our auditors.

In my specific environment, I wanted to identify the document source for an invoice (usually
a shipment) and if one doesn't exist I wanted to specify that one wasn't found. The formula
wizard in X3 is great, but it lacks the ability to do IF statements. We approached our partner
with the idea and this is what they helped us create.

We knew it was possible to use custom functions written in 4GL within formulas and they
are used extensively in X3. The syntax looks like:

func ZGC.IFFS(CRITERIA, TRUEPART, FALSEPART)

The ZGC portion is the filename is the process editor, the CRITERIA is a string which X3
will evaluate (more on this later), the TRUEPART is what is returned when the CRITERIA
statement is true, and the FALSEPART is what is returned when CRITERIA is false.
Because we are limited to 250 characters in our formulas, it’s important to keep the function
names and filename as short as possible.

To start off, we must define our function. This one is really easy. Start by going to the
Development / Script Dictionary / Script Editor. Press New in the top right, and then in the
File Name field enter ZGC.
The function itself is below and, to anyone with a programming background, it should be
pretty easy to see what's going on. For those who don't, just follow along. It's more
important to see the usage of this later on anyway.

In the large text field at the bottom, enter the following text (as in below screenshot):

Funprog IFFS(CRITERIA,TRUEPART,FALSEPART)
Value Char CRITERIA
Value Char TRUEPART
Value Char FALSEPART
Value Char RETVAL

If(evalue(CRITERIA))
RETVAL=TRUEPART
Else
RETVAL=FALSEPART
Endif
End RETVAL

Once this is entered, ensure you press the Compile button at the right. Now let’s use this
function in our autojournal.
Navigate to Setup / Financials / Automatic journals

In our case, we wanted to modify the Sales Invoice autojournal used when our invoices are
posted so we went to the SIHI autojournal, then pressed the Lines button on the right. The
line we were interested in modifying was Line 10 - the line that relates to the receivable.
Since we only ship complete per order, our invoices will either have only have one shipment
on them, or no shipment at all.

This enhancement would enable us to look at a receivable journal and see which shipment
a particular invoice was created for.

Go to the formulas tab, and scroll down until you see the description is presently set to
[F:SIH]DES(0) and replace it with the following:

func ZGC.IFFS("'"+ [F:SIV]SIHORINUM+"'=''","Non-stock invoice",[F:SIV]SIHORINUM)

Let’s break down the formula. The func ZGC.IFFS() is the function we defined earlier.
The first parameter contains the CRITERIA. It wraps the SIHORINUM field in quotes and
compares it to an empty string. If that comparison is TRUE, then it returns the string, "Non-
stock invoice". If that comparison is false, it returns the shipment number. The reason we
are wrapping the SIHORINUM in quotes is because we are passing in a single string as our
CRITERIA, and that string needs to be evaluated as a piece of code.

Now save the changes to that line and press the Close button, proceed to create a non-
stock invoice (as below), and then post it.

When we look at the journal, it has the new description. In this case, our CRITERIA field
would have been ''=' which returns true, so TRUEPART is used.
Now let’s post one which has an origin document no. In this case, our CRITERIA field would
have been 'SHP036107'='' which returns false, so FALSEPART is used.

And looking at the resulting journal entry:

Brandon Gabert
Information Systems Manager
Guardian Chemicals Inc.