Solved

Pre-filtered lookup?

Alex Kerschkamp
Rank
Userlevel 2
Badge +3

Hello everyone,

We're running into an interesting problem. We have an entity (say, a group) that has contacts (persons). The group is part of an organization. The organization is the owner of all the contacts, but you want to select a contact with a group, but only from a list of contacts within the organization.

We use a lookup to select the contact with the group. But, we only want to select a contact from the contacts of the organization that the group is part of. How do we tackle this?

Best regards,

- Alex

icon

Best answer by Mark Jongeling 8 July 2022, 14:32

View original
This topic has been closed for comments

5 replies

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hi Alex,

You can put a default On Hidden Prefilter on the lookup that ensures that the list of Persons is filtered on the Organization of the logged-in user.

Most likely your end-product contains a place where the logged-in user is stored, and this User also is within an Organization. This data you can use to make the Prefilter work like for example:

-- GUI generated code
select t1.name
from persons t1
where
-- End
-- Prefilter code under here
exists (select 1
        from [application_user] t2
        where t2.organisation_id = t1.organisation_id
          and t2.tsf_user_name = dbo.tsf_user()
)

Hope this gives you a good idea 😀

On holiday May 6 - May 31
Alex Kerschkamp
Rank
Userlevel 2
Badge +3

Thanks for the fast reply, Mark! We're not filtering per logged on user. We're filtering on selected group within an organization, and select only contacts within that organization. You've helped us realize that we're having the exact same issue as discussed here. We're gonna try to use Erwin's answer for our case.
 

Alex Kerschkamp
Rank
Userlevel 2
Badge +3

Mark, we have a similar case as Corné's case. The difference is, though, that our first lookup is already filled up front.

We're trying to filter lookup #2 based on the value of lookup #1. Lookup #1 is filled with an organization. We want lookup #2 filled with contacts from the organization as selected in lookup #1. See the screenshot below.
 


So, “Het schaapje” is selected in the Organisatie lookup. We would like to see "contact Schaapje” as the only value in the contact person lookup, and not all contacts.

We tried views and pre-filters. Yet, somehow we can't seem to grab the value of lookup #1 to use in lookup #2. What are we not seeing? The remarks about references and expression fields in Corné's topic are a bit vague to us. What is done with them, and how?

Best regards,
- Alex.

Mark Jongeling
Rank
Userlevel 7
Badge +23

Hi Alex,

Are Organizations and Contact persons two separate entities? If so, you can create a "link table” in which Contact persons and Organization comes together. (This could be a View but does not have to be)

This Data model ensures that a created school group can have an Organization (from the base table) and an Organization person. The person_id column has a lookup which will only show person that are known within the selected Organization. If the Organization is not filled in yet, no person can be selected.

Does this solve the case?

On holiday May 6 - May 31
Alex Kerschkamp
Rank
Userlevel 2
Badge +3

This works, Mark! Thank you very much! 😀

Terms & ConditionsCookie settings