How to modify a form LOV using forms personalization

A List of Values (LOV) within a form can be modified using Forms Personalization. By LOV we mean that LOV that is embedded within the form. This LOV was created within the development phase.

Let us first look at the Invoice form.

Navigate to the Payables Invoice form in an AP responsibility

Click on the Supplier field and the LOV opens. Search for all suppliers by searching for %.

All the suppliers are displayed in the LOV. We can select a supplier from this list and proceed working on Invoice form.

Requirement


We would like to make a modification to this list. The idea is to modify the list based on the responsibility so that different responsibilities get access to different suppliers. So we have decided to show all suppliers whose names start with A if the Oracle user has logged into the responsibility that starts with EY

Personalization Steps


On the AP Invoice form click on Help > Diagnostics > Custom Code > Personalize

Enter a new personalization as shown below

Condition tab

Logic:

The triggering condition is when the user clicks on the Supplier Name field on the form. Hence the Trigger Event is WHEN-NEW-ITEM-INSTANCE and the Trigger Object is INV_SUM_FOLDER.VENDOR_NAME. The condition of personalization checks whether in the logged in responsibility starts with EY or not.

Actions tab


Action 1: Create a new data set for the LOV

The query used is,

1 2 3 4 5

SELECT   vendor_name, vendor_number, vendor_id, vendor_type_lookup_code, num_1099, type_1099, vat_registration_num, employee_id,          awt_group_id, allow_awt_flag, hold_all_payments_flag, num_active_pay_sites     FROM po_vendors_ap_v    WHERE active_flag = 'Y' AND enabled_flag = 'Y' AND num_active_pay_sites > 0 AND vendor_name LIKE 'A%' ORDER BY UPPER (vendor_name)

Action 2: Set the new data set to the LOV

The previously created data set is now attached to the LOV which is called from the Supplier Name field.

Important:


How do we know the query of the record group that needs to be changed? The structure of the query has to be perfect else the form will throw an error each time we try to access the LOV.

To find the original structure of the query we need to open the form in Forms builder. We have come to know the name of the form from Personalization form itself. It is APXINWKB.fmb. You will find the form under $AU_TOP/forms/US folder on the server.

We need to know the name of the original record group being used on the form. On Action 2 of the personalization step above we shall remove the Value as shown below,

Save the data on the form. Close the form and reopen it and come to this step. Now click on Get Value button.

You will get the name of the record group, i.e. ACTIVE_VENDORS, used in the form. This value is overwritten to suit the requirement. Now the value can be changed to XX_VENDORS_NAME so that the personalization remains intact. We shall extract the query of this record group from the form and then modify the query as per the SQL structure.

Now download and open the form, APXINWKB.fmb, in Forms Builder

Open the record group

The query of the record group is,

SELECT   vendor_name, vendor_number, vendor_id, vendor_type_lookup_code, num_1099, type_1099, vat_registration_num, employee_id,         awt_group_id, allow_awt_flag, hold_all_payments_flag, num_active_pay_sites    FROM po_vendors_ap_v    WHERE active_flag = 'Y' AND enabled_flag = 'Y' AND num_active_pay_sites > ORDERBY UPPER (vendor_name)

This query has been modified and saved in personaliztion in Action 1 above.


Test the form


Reopen the Payables Invoice form.

Open the Supplier LOV and search for all suppliers by typing % in the search field. The LOV automatically changes the condition to A% and displays the list of all suppliers whose names start with A.

Now you have overwritten the seeded LOV using personalization.