Creating an IF function for use in formula wizard

Autor: https://www.sagecity.com/members/regularguy

Origen Artículo: https://www.sagecity.com/support_communities/sage_erp_x3/b/sageerp_x3_product_support_blog/posts/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)
(see below screenshot)

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:

The great part of using the description field is that it can be seen throughout the application, as below in the Financial Inquiries on our Accounts Receivable. Even more than that, this formula could be used in any area where we can use formulas.

Brandon Gabert