Skip to main content
Solved

Multiple lookups filtered on different value to same table

H
Sidekick
Forum|alt.badge.img+6

Hi, 

I’ve a scenario with a sales order with a sender company with related location and receiver company with related location.
What is the best approach to make lookups for the both locations?
For sender location, it should filter the available locations on the sender company and for the receiver location, it should filter on the receiver company.
Both sender and receiver are retrieved from company table and location from the related company_location table.
Can I use same lookup or should I create different ones? 
I have this scenario for more tables in the application.

Thanks in advance.

 

HJ van Dalfsen

Best answer by Mark Jongeling

The way to achieve that is by setting company_id in the company_location table as PK too. That will mean that company_location has a PK consisting of company_id and company_location_id (order is important). The way this data model is created means that a company can have multiple locations so it should be a child of the company table.

When the PK is made, you can adjust the sender_location and receiver_location references to include the company_id. Then upon opening the lookup, the list is filtered on the given company_id.

Hope this clears it up for you!

Kind regards,
Mark Jongeling

View original

    Mark Jongeling
    Administrator
    Forum|alt.badge.img+23

    Goodmorning HJ van Dalfsen,

    If I understand correctly you are making lookup pop-ups for multiple fields that happen to look at the same table. It's not possible to reuse the same lookup/reference for two fields. To make a lookup for each desired field you have to create two references namely: between the sales order sender company and the location + between the sales order receiver company and the location.

    The second reference needs a addition to be created, otherwise the names of the references would be the same. For convenience you could give them both an addition. Like this:
     

    Two references from address to the subject

    Hope this helps!

    Kind regards,
    Mark Jongeling

    "Wake me up before you low-code"
    H
    Sidekick
    Forum|alt.badge.img+6

    Hi Mark,

    I already applied the references.

    I've combination of 3 tables:

    Both sender_id and receiver_id have reference to company table. 

    After filling the sender_id, I have to select a record from company_location in the sales_order, but then the lookup should be filtered on the location records of the related sender company_id.

    Same is there for the receiver_id and receiver_location_id.

    Kind regards,

    HJ v Dalfsen

    Mark Jongeling
    Administrator
    Forum|alt.badge.img+23

    The way to achieve that is by setting company_id in the company_location table as PK too. That will mean that company_location has a PK consisting of company_id and company_location_id (order is important). The way this data model is created means that a company can have multiple locations so it should be a child of the company table.

    When the PK is made, you can adjust the sender_location and receiver_location references to include the company_id. Then upon opening the lookup, the list is filtered on the given company_id.

    Hope this clears it up for you!

    Kind regards,
    Mark Jongeling

    "Wake me up before you low-code"
    H
    Sidekick
    Forum|alt.badge.img+6

    Thanks, got it working now.

    Especially the order of the columsn for the primary key is important. Otherwise, no lookup is available for the both location fields and in the validation, there is an error for multiple GUI references.

    Reply


    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie settings