Excluding data from one table based on a filter in another table

[Bouwer]

Hi

I have two table Daily Submissions and Branches.

The Daily Submissions table is populated by another app, where branches submit data at end of day.

Now I created a second app for managers to check up on the branches and see who did not submit their data.

Problem:

I want to show in a table which branches did submit and which branches in still outstanding.

I thought of two ways to do it but can't get one of them to work.

Solution #1 - Show to table on with branches that did submit (Filter Daily Submission for Current Date) (Got this
                      one to work)

                    - Show table with branches that did not submit (Exclude Branches that in already in above table) (Can't 
                      get this on to Work)

Solution #2 - Show Table with all Branches and add custom column with values "Submitted" or "Outstanding"

                       (Don't know how to add Custom Columns to Table)

If anyone can help it will be appreciated.

Regards

[Anonymous]

I just faced a very similar situation and wrote up a long explanation of using Filter() with In and Not to get the results you want. Visit this thread and see if it helps:

https://powerusers.microsoft.com/t5/Expressions-and-Formulas/Limit-Combobox-list-to-only-items-not-p...

[Anonymous]

OK, so the link in the previous post goes to the first submission of that write-up of mine... Initially that post was locked in some sort of purgatory where it wasn't showing in the search results, wasn't showing in the thread list, etc. I could only get to it because I had the initial URL.

So I reposted the thread, figuring that would give me a searchable destination to link to in case, like with your post, I needed to reference it.

Carlos replied to the second posting of the thread. But now the initial post DOES show up in the list... which is what I linked to. To see Carlos' response/explanation, you'd have to go to the second posting of the write-up, which is here:

https://powerusers.microsoft.com/t5/Expressions-and-Formulas/Limit-Combobox-items-to-only-items-not-...

[HodgsonIM]

I keep getting "Access Denied" when I try to view this post. I REALLY need help with this. Do you know why this post is Access Denied. And can you post this where I can read it?

[Anonymous]

Hi, @Hodgson...

Even for me, both of the links give that "Access Denied" error. Lucky enough, the post was long enough that I drafted it in document form before I posted it, and I still had it in my temporary files. Here's the text that is now hidden by that "Access Denied" block:

================================================================

(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 (note: link to original post lost between first posting and this repost) 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.

Post Script:

I am tagging @CarlosFigueira to see if he can help clarify something. In this thread (note: link to original post lost between first posting and this repost), 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.

================================================================

(Carlos actually did reply to the original thread... I'm leaving his tag in here in case he feels inclined to post here, too, since the other thread seems to have eaten itself.)

HTH

[HodgsonIM]

Thanks for reposting. That simulation really helped me.