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
Page 1 / 1
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
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.
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.
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.
