cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
TimRohr
Level 10

Limit Combobox list to only items not previously used

(As I was putting this together, I stumbled upon the solution, so this is already solved. However, despite the situation being quite common, the solution was very hard to find a solution for and then even more difficult to implement. So I figured I would post my problem along with the solution that I found so that others might benefit, too.)

BASIC IDEA:

I have a situation where each HR Employee can take on a Role for MANY Groups, however they can only have ONE Role for any ONE Group. (If anything changes about what they do for that Group, we want to record it on the same entry, not a new one.) Therefore, when an administrator goes into the app and wants to create a new Role to attach an Employee to a Group, they select the Group (combobox) and then the Employee (combobox). I want to limit the Employees available to pick to those who do not already have Roles for that Group.

DETAILS:

I have three CDS entities that matter to this process. 

HR Employee        (1:N with HR Group Role, N:1 with Regional Group)
Regional Group     (1:N with HR Group Role, 1:N with HR Employee)
HR Group Role      (N:1 with HR Employee, N:1 with Regional Group)

For those, here are the fields that matter: 

HR Employee
HR Employee          GUID
EmployeeName         Text

Regional Group
Group ID GUID
Group Name Text
HR Employee Lookup (HR Employee)

HR Group Role
HR Group Role GUID
HR Employee Lookup (HR Employee)
Regional Group Lookup (Regional Group)

One other minor piece of info to know is that the Group can be chosen from a different screen (ie, the Group screen), so instead of directly referencing the Group combobox, I will reference a GroupID variable that is passed into the form and/or updated by user selection. GroupID will hold the GUID of the Regional Group selection.

 

SOLUTION

I'll present the solution I arrived at, and then break it down. In the Items property of my combobox to hold the Employees available (those who haven't been already attached to this Group), I put the following:

Filter(Choices('HR Group Roles'.'HR Employee'), Not('HR Employee' in Filter('HR Group Roles','Regional Group'.'Group ID' = GroupID).'HR Employee'.'HR Employee'))

Breaking that down...

Filter(Choices(),...)

Choices() is the root of the Items for a combobox, taking a Datasource.Field argument. That result set can be filtered according to a logical test (like Field="Validation Text"). That part was straightforward.

In and Not

In order to get the result of "Employees not in the set of those already used," we have to get creative. Credit @Venxir in this thread for the lightbulb moment that a filter works on true/false, but it doesn't care how we generate that answer. So we can use Not() as the wrapper of our true condition in order to negate it. In other words, if we can test if our Field is in a table column, then we can reverse that to consider those not in. How do we get our set of Employees who have already been used in (or attached to) this Group? We use 'HR Employee' in Filter(...).'HR Employee'.'HR Employee' to compare potential Employees against the set of those used.

Filter('HR Group Roles',...).'HR Employee'.'HR Employee'

We want to filter all of our existing Roles down to those that are attached to this Group. Once we have that, we close the Filter parentheses and use dot notation to get the field (column). This part threw me for a while because of the Lookup fields involved. Look at the logical test of the Filter() statement...

Filter('HR Group Roles','Regional Group'.'Group ID' = GroupID).'HR Employee'.'HR Employee')

Since 'Regional Group' is a lookup field in the HR Group Roles datasource (entity), we have to drill down to get an actual field to return. Using 'Group ID' means we're grabbing the GUID of the associated Group.

The same thing happens trying to get the Employee. We have to reference the field in the 'HR Group Roles' datasource ('HR Employee'), followed by the field from the associated record in the 'HR Employees' datasource that we want to return ('HR Employee').

Filter('HR Group Roles','Regional Group'.'Group ID' = GroupID).'HR Employee'.'HR Employee')

With that in place, we achieve the goal of limiting the combobox entries to only those not previously used.

 

I hope this helps someone, and saves them the hours I spent researching and troubleshooting it. Smiley Wink

 

Post Script:

I am tagging @CarlosFigueira to see if he can help clarify something. In this thread, he talked about using the Choices() function with a Lookup column necessitating the use of "Value" as the comparitor in the Filter's logical statement, but I could not get that to work. I would like to understand what the difference is, and why that didn't apply to what I was trying to do.

1 ACCEPTED SOLUTION

Accepted Solutions
TimRohr
Level 10

Re: Limit Combobox list to only items not previously used

Submitted this as a "knowledge base" sort of post, so I'm marking it as solved, but I'd still like to understand @CarlosFigueira's method and when it might be required.

1 REPLY 1
TimRohr
Level 10

Re: Limit Combobox list to only items not previously used

Submitted this as a "knowledge base" sort of post, so I'm marking it as solved, but I'd still like to understand @CarlosFigueira's method and when it might be required.