Mastering SQL Queries for SAP Business One

Utilize the power of SQL queries to bring Business Intelligence to your small to medium-sized business

The User-Defined Values function enables SAP Business One users to enter values, originated by a predefined search process, for any field in the system (including user-defined fields). This function enables the user to enter data more efficiently and – perhaps most importantly – more accurately. In fact, the concept is sort of a "Workflow Light" implementation. It can both save user time and reduce data double entries.

In this article by Gordon Du, author of Mastering SQL Queries for SAP Business One, we will see how to work with User-Defined Values.

(For more resources on Microsoft, see here.)

How to work with User-Defined Values

To access the User-Defined Values, you can choose menu item Tools | User-Defined Values. You can also use the shortcut key Shift+Alt+F2 instead. Another option is to access it directly from a non-assigned field by using Shift+F2. This will be discussed later.

You must notice that the option will not be available until you brought up at least one form. This is because the UDV has to be associated with a form. It can't stand alone.

The following screenshots are taken from A/R Down Payment Invoice. It is one of the standard marketing documents. From the UDV point of view, there is no big difference between this and the other types of documents, namely, Sales Order, Purchase Order, Invoice, and so on.

After a form is opened, a UDV can be defined. We will start from an empty screen to show you the first step: bringing up a form.

When a form is opened, you can define or change any UDV. In this case, we stop our cursor on the Due Date field and then enter Shift+F2. A system message will pop up as shown in the following screenshot:

If you click on Yes, it will bring up the same window in the manner you select the menu item mentioned earlier from the Tools menu or press Shift+Alt+F2.

When you get the User-Define Values-Setup screen, you have three options. Apart from the default option: Without Search User-Define Values, you actually have only two choices:

• Search in Existing User-Define Values
• Search in Existing User-Define Values according to Saved Query

Let's go through the last option first: Search in Existing User-Define Values according to Saved Query. The topic related to query will always be assigned with the top priority. There are quite a few screenshots that will help you understand the entire process.

Search in existing User-Defined Values according to the saved queries

The goal for this example is to input the due date as the current date automatically.

The first thing to do for this option is to click on the bottom radio button among three options. The screenshot is shown next:

After you have clicked the Search in Existing User-Defined Values according to Saved Query radio button, you will find a long empty textbox in a grey color and a checkbox for Auto Refresh When Field Changes underneath. Don't get confused by the color. Even though in other functions throughout SAP Business One, a gray colored field normally means that you cannot input or enter information into the field. That is not the case here. You can double-click it to get the User-Defined Values.

When you double-click on the empty across-window text box, you can bring up the query manager window to select a query.

You can then browse the query category that relates to Formatted Searches and find the query you need. The query called Auto Date Today in the showcase is very simple. The query script is as simple as this:

SELECT GetDate()

This query returns the current date as the result.

You need to double-click to select the query and then go back to the previous screen but with the query name, as shown in the following screenshot:

It may not be good enough to select only query because if you stop here you have to always manually trigger the FMS query run by entering Shift+F2.

To automate the FMS query process, you can click on the checkbox under the selected query. After you check this box, another long text box will be displayed with a drop-down list button.

Under the text box, there are two radio buttons for Auto Refresh When Field Changes:

• Refresh Regularly
• Display Saved User-Defined Value

Display Saved User-Defined Values will be the default selection, if you do not change it.

When you click on the drop-down list arrow button, you will get a list of fields that are associated with the current form.

You can see in the following screenshot that Customer/Vendor Code field has been selected. For header document UDV, this field is often the most useful field to auto refresh the UDV.

In theory, you can select any fields from the list. However, in reality only a few fields are good candidates for the task. These include Customer/Vendor Code, Document Currency, Document Number, and Document Total for document header; Item Code and Quantity for document lines. Choosing the correct data field from this drop-down list is always the most difficult step in Formatted Search, and you should test your data field selection fully.

Now, the text box is filled with Customer/Vendor Code for automatically refreshing the UDV.

Between two options, this query can only select the default option of Display Saved User-Defined Value. Otherwise, the date will always change to the date you have updated the document on. That will invalidate the usage of this UDV. The Refresh Regularly option is only suitable to the value that is closely related to the changed field that you have selected.

In general, Display Saved User-Defined Value is always a better option than Refresh Regularly. At least it gives the system less burden. If you have selected Refresh Regularly, it means you want to get the UDV changed whenever the base field changes.

The last step to set up this UDV is by clicking Update. As soon as you click the button, the User-Defined Values–Setup window will be closed. You can find a green message on the bottom-left of the screen saying Operation Completed Successfully.

You can find a small "magnifying glass" added to the right corner of the Due Date field.

This means the Formatted Search is successfully set up. You can try it for yourself.

Sometimes this "magnifying glass" disappears for no reason. Actually, there are reasons but not easy to be understood. The main reason is that you may have assigned some different values to the same field on different forms. Other reasons may be related to add-on, and so on.

In order to test this FMS, the first thing to try is to use the menu function or key combination Shift+F2. The other option is to just click on the "magnifying glass". Both functions have the same result. It will force the query to run. You can find that the date is filled by the same date as posting date and document date.

You may find some interesting date definitions in SAP Business One, such as Posting Date is held by the field DocDate. Document Date however, is saved under TaxDate. Be careful in dealing with dates. You must follow the system's definition in using those terms, so that you get the correct result.

A better way to use this FMS query is by entering the customer code directly without forcing FMS query to run first.

The following screenshot shows that the customer code OneTime has been entered.

Please note that the DueDate field is still empty.

Is there anything wrong? No. That is the system's expected behavior.

Only if your cursor leaves the Customer Code field, can the FMS query be triggered.

That is a perfect example of When Field Value Changes. The system can only know that the field value is changed when you tab out of the field. When you are working with the field, the field is not changed yet.

Be careful to follow system requirements while entering data. Never press Enter in most of the forms unless you are ready for the last step to add or update data. If you do, you may add the wrong documents to the system and they are irrevocable.

The previous screenshot shows the complete process of setting up search in Existing User-Define Values according to Saved Query. Now it is time to discuss the $ sign field.