You're reading...
Forms Personalization

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,

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 > 0
ORDER BY 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.

Cheers!

About these ads

About Abhijit

I love sleeping, watching Hollywood blockbusters, my Wii, road trips and watching my 4 year old son grow up. In between I try to squeeze in some time to go to work.

Discussion

3 thoughts on “How to modify a form LOV using forms personalization

  1. Guys, thanks for putting this up. Do you find the when-new-item instance-trigger is more effective? I ususually use when-new-forms-instance on the basis that it only has to be done once.

    Today I was personalizing the payments form in AP and in this case there is a parameter PAY_IN_FULL_VENDOR_ID you can use if you want to limit the users to only paying one vendor. You can set this via object type Parameter and set the Target Object to PAY_IN_FULL_VENDOR_ID.

    Posted by Alan Telford | September 28, 2012, 1:36 pm
  2. Great Notes ,thanks allot.

    Posted by Sagar | July 10, 2014, 7:55 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Traffic

Site Stats

  • 707,054 views since Feb 2012

Archives

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 209 other followers

Flags

Free counters!
Follow

Get every new post delivered to your Inbox.

Join 209 other followers

%d bloggers like this: