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, andDocument 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.
Where do the $ values come from?
Many users have this question when they first read a FMS query script with system variables, because not all users have the chance to access SAP's formal document.
A $ sign in the query means a system variable. There are few variables available all the time such as$[user]. This is the variable to represent the current log in user sign. However, most variables will be form dependent. To get detailed information regarding the variable, you need to activate the System Information function as shown in the next screenshot:
This menu option is under the Views menu. You can use the shortcut key Ctrl+Shift+I to get the same result. This menu option is an On/Off switch. You can change it from off to on or from on to off if you select it. A check in front of the System Information means it is on.
When the system information switch is on, all system information related to the current mouse position will be shown on the status bar at the bottom left of the screen. It is irrelevant to the cursor position.
The following example shows that the mouse is over the top first field Customer. The information related to it is as follows:
[Form=65300 Item=4 Pane=0 Variable=1 OPDI,CardCode]
This is a header field so only two parts are useful to the FMS query:
- The first one is Item=4. Item here means form element; it has nothing to do with item master data.
- The second one is OPDI, CardCode. It is the table and field name for this item.
In the FMS query, the usages can be either of the following:
- $[$4.0.0] that reflects Item=4
- $[OPDI.CardCode] that only adds $[] to the field name
The other similar screenshot, as follows, looks exactly the same but the mouse is over the Item No. field in the line detail of the form.
The information that relates to this field is:
[Form=65300 Item=38 Pane=1 Column=1 Row=1 Variable=11 PDI1,ItemCode]
This is a line field and three parts are useful to the FMS query:
- Item=38: This item stands for the whole matrix of the line detail
- Column=1: This refers to the field in the table
- PDI1,ItemCode: Again it is table and field name
In the FMS query, the usages can be in two equivalent formats:
- $[$38.1.0] that reflects Item=38 and Column=1
- $[PD1.ItemCode] that only adds $[] to the field name
The syntax for a form based line level variable is $[$Item.Column.Type].
For header items, columns are always zero because all items on the header represent a field in a table if it has a datasource. Column is only useful for line details.
The last portion is the Type. It is not related to system information found on the screen. It is the type of value you need to return, and can be any of the following:
- 0-String(text)
- Number
- Currency
- Date
For example, a value returned by $[$22.0.0] as N'USD 100' when a marketing document total is 100 US Dollars. If by $[$22.0.number], it will return 100. It returns only N'USD' if you use $[$22.0.currency]. As a default selection of type 0, it can often be omitted.
While in the form based variable, the column represents the order number of the column in the form. For example, $[$38.11.number] stands for quantity field in the marketing document. It is the eleventh column within the line detail matrix.
The syntax for a table based variable is $[Table.Field.Type].
The field in this syntax is the actual field code within the table. It is the last part of the system variables. It can be a UDF starting with U_. If you use a table based variable, the form based variable $[$38.11.number] is equivalent to $[RDR1.quantity.number].
In general, you can always use a form based variable to reduce the trouble for one query that has to be written multiple times based on the different tables for different forms.
How to get the value you need from, and for, the FMS query
To get any values from the FMS query, the first thing to remember is: only the first field can be returned to the field if you select more than one field in your query. All fields from the second one will be ignored no matter how many fields you have selected for the query. The additional fields can only be functioned as references.
To assign any values to a FMS query, the principle is: only a table available to the current form can be based to set a variable. If you open a new form, the base form variable will become inaccessible. For example, all variables on the main form of the marketing document will not be available to a freight form and vice versa.
If you create a query using the $ variables and have a form open that uses the variables then the query replaces the $ values with the current values in the form. This is an important step to test FMS query before assigning. Make sure you save the query first. Otherwise, the variable will become a hard value. You cannot get it back to $ variable.
Can you run FMS queries directly?
Can you run FMS query directly under query manager? The answer is partially yes and partially no. It depends on whether you have form variables or dollar sign field values.
If a FMS query has a variable, then this query must be saved under one of your categories. Preferably, you create an exclusive folder with the name related to Formatted Search. It is considered as the best practice.
You will get an error message if you select run query from query manager or user queries directly without any related form open. This is due to the undefined form. A $ sign will be meaningless without current form support. You will get a red "Internal error" message immediately on the status bar.
What is the negative sign's function in FMS query?
We often find that certain FMS queries have a negative sign for variables. For instance:
$[$-4.0.0]
What does the negative sign stand for?
A negative sign for a form variable means your FMS query is related to header level User-Defined Fields. The form number will have the same value as the main form but with the opposite sign.
For example, form 139 is a sales order form. UDF form for a sales order will be -139. Form 142 is a purchase order form. UDF form for a purchase order will be -142. The difference between those two related forms is only a negative sign.
When we refer the main form variable to the UDF form, a negative sign is always needed. The actual form number is not important to FMS query. Only the negative sign matters. $[$-4.0.0] refers to the BP code in the main document form. It will be used for a FMS query assigned to the UDF form.
Search in existing User-Defined Values only
The simplest form of a search for UDV is to define a list of values, which the system proposes as input values. These input values are triggered when the user selects Shift+F2 in the relevant field. The procedures to define this simplest search will be shown next.
Although this function may not be directly linked to SQL query, it is worth going through the detailed process to get one more options under the same menu function with FMS query for UDV.
The same process will be discussed as from the breaking point that showed the Without Search User-Define Values option previously. With one exception: the cursor is not on the DueDate but the Remarks field.
Instead of selecting the bottom option, the middle option, Search in existing user- define values only, will be selected as follows:
In the right-most screen of this middle selection, you can find a button with three dots. It is the access path for predefined values.
This button needs to be clicked to assign a list of predefined values. There is nowhere else you can find it.
After clicking on the button, a new form will pop up with the title: Field Values– Setup. There is only one field in the form. The field title is Field value. Your cursor will remain on the first line.
You can input any letter or number here. In the example, High is inputted on the first line. You will notice the button OK has changed to Update. This Update is not only for the form but for every line. You only need to press Enter to move to the next line. It acts as a click on the Update button.
After pressing Enter, the cursor moves down to the second line. The button becomes OK again. A green message is shown on the bottom-left of the screen: Operation Completed Successfully. You can input any characters just like in the first line.
Low is entered on the second line. The button now changes from OK to Update. As before, you just need to press Enter to move the cursor down again.
When the line is empty and the button is OK, you can close the form by pressing Enter or clicking on theOK button. This will complete the value list input. You can always go back to Update or add new lines to the list.
Just like the other example, you should find that the magnifying glass has been added to the right corner of the Remarks field from the last screenshot. This means that the UDV has been assigned successfully.
When you press Shift+F2, a list of values will pop up. All values that were entered on the list will be available.
You can select any line from the list. Then press Enter to select one of the values required or click theChoose button. Click on the Cancel button if you do not want to assign any values from the list. There is an additional button besides these two buttons. It is the New button. This button allows you to edit or to add values on the list.
As you can see, since the value selected is that of the first line, High has been returned to the field. This value can be very long. That may save you a great amount of time.
The previous screenshots conclude the process to set up Search in existing user-define values onlyfunction.
Summary
In this article we saw how to work with User-Defined Values.
