Similar to this, you might also interested in “Lookup Values...” functionality which can be invoked using key board shortcut “Ctrl + K”. This is useful if you want to filter the LOV using already entered value for that particular field. For example, if you want to enter a supplier via LOV but if you already typed “ABC” for the supplier field, then “Ctrl + K” will show you the LOV only with suppliers starting with “ABC”.

DIPAKPATEL,

Not sure if you mean the following:

Normally one can use F8 or its connected icon to get a list of values (in this example for field Market in customer):

It will retrieve all valid market codes in this example.

If you enter only a C and then press Ctrl+k the following will show:

See how the title of the window for searching market codes now contains C%. So it performs a C% search. If it helps you can use the contains version by entering %1 in the Market field and press Ctrl+k. This gives:

All market codes containing a ‘1’.

Steve

Hi Tharindu & eqbstal,

Thank you for your quick reply.

Actually I am aware about CTL+K and F8 functionality. 

This is not want I want. 

From parent screen, I want to use any field value into SQL expression of LOV Advance search screen.

Thank you.

DIPAKPATEL,

According to me that would be a modification to be done. It is not standard IFS.

Steve

You can use data from the form to pass to a SQL query. In the example below I’m passing the value in Item Class into the where clause to find objects linked to said item class when I open the Object ID LOV.

If I then save the query and set start-up behaviour to always launch the LOV with the where clause above, I get this. Not sure if that was what you were looking for?
 

:i_hWndFrame.frmFunctionalObject.dfMchCode
Green: Fixed value
Red: The form you are working in (frame in sysinfo)
Orange: The field (control in sysinfo, for Apps 10 at least)
 

anmise,

This seems to be a healthy solution. However, I quote ‘...and set start-up behaviour to always launch the LOV...’. Where do you save this, so it always launches the LOV with the search. Is it IFS version related?

Steve

You save the query for the LOV first. Then RMB and go to Properties and change the start-up behaviour to start with a saved search.

 

​
@anmise ‘s solution is fantastic. Thank you.

I'd like to share a work-around I previously developed at the database level. It's definitely not better, but here it is:

Function description:
Whenever you load the base object, it updates a cache. When you load the LOV, it reads that cache to know how to filter the choices.

Technical description:
1. Create a custom logical unit (CLU) to hold the cache. The primary key is the username, and the secondary columns hold the primary key of the base object.
2. Create a package with a function. The function creates and commits an autonomous transaction. Inside of this transaction, it inserts or updates the CLU for this user based on the function arguments. The function returns some unimportant value to prove it works.
3. Create a read-only custom field on the base object. The custom field calls the function, passing in the base object's primary key. (This updates the CLU every time a record is loaded.)
4. Create a saved search on the LOV. The saved search reads the CLU to get the cache value, then does something with it to filter the LOV choices.
5. Set the saved search as the default for the LOV, and set this in base profile(s) as desired.
 

Known limitations:
1. This slows down the base object’s _CFV view.
​2. The record has to be saved to update the cache, so the LOV filtering only works when modifying a record, not when inserting it.
3. The user will see strange results if they work in multiple screens at once.

There’s a chance this could work in Aurena, but I haven’t played with it yet.

You can use data from the form to pass to a SQL query. In the example below I’m passing the value in Item Class into the where clause to find objects linked to said item class when I open the Object ID LOV.

If I then save the query and set start-up behaviour to always launch the LOV with the where clause above, I get this. Not sure if that was what you were looking for?
 

:i_hWndFrame.frmFunctionalObject.dfMchCode
Green: Fixed value
Red: The form you are working in (frame in sysinfo)
Orange: The field (control in sysinfo, for Apps 10 at least)
 

 

Hi André @anmise 

This is very very helpful. Thanks a lot for this.

Hallo

It was really helpful all the repalies. I have to add a custom field “Payment Terms” to the Service Quotation. 

I was able to add a filter “Site” of the payment terms.

&AO.PAYMENT_TERM.COMPANY = :i_hWndFrame.frmServiceQuotation.dfsContract

But the site is not the company. 

Is there any possibilty to integrate “&AO.Company_Site_API.Get_Company(contract)”  the get correct Company.

I try  

&AO.PAYMENT_TERM.COMPANY = :&AO.Company_Site_API.Get_Company(i_hWndFrame.frmServiceQuotation.dfsContract)

But it is not working. I got the following error:
 

Best Regards,

André

Hallo 

I could solve my problem. The correct search is:

&AO.PAYMENT_TERM.COMPANY = &AO.Company_Site_API.Get_Company( :i_hWndFrame.frmServiceQuotation.dfsContract)

Best Regards,
André

You can use data from the form to pass to a SQL query. In the example below I’m passing the value in Item Class into the where clause to find objects linked to said item class when I open the Object ID LOV.

If I then save the query and set start-up behaviour to always launch the LOV with the where clause above, I get this. Not sure if that was what you were looking for?
 

:i_hWndFrame.frmFunctionalObject.dfMchCode
Green: Fixed value
Red: The form you are working in (frame in sysinfo)
Orange: The field (control in sysinfo, for Apps 10 at least)
 

 

Dear @anmise ,

Is it possible to use the parameter in a custom field please ? I need to create a custom field in the screen below and i need to get the Week date

I have a problem because i do not have the value V.