cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bouwer
New Member

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

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

 

5 REPLIES 5
Anonymous
Not applicable

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
Not applicable

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-...

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
Not applicable

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. Smiley Wink

 

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

Thanks for reposting. That simulation really helped me.

Helpful resources

Announcements

Community will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!   This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions in the Forums to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Community MembersNumber of SolutionsSuper UsersNumber of Solutions @anandm08  23 @WarrenBelz  31 @DBO_DV  10 @Amik  19 AmínAA 6 @mmbr1606  12 @rzuber  4 @happyume  7 @Giraldoj  3@ANB 6 (tie)   @SpongYe  6 (tie)     Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Community MembersSolutionsSuper UsersSolutions @anandm08  10@WarrenBelz 25 @DBO_DV  6@mmbr1606 14 @AmínAA 4 @Amik  12 @royg  3 @ANB  10 @AllanDeCastro  2 @SunilPashikanti  5 @Michaelfp  2 @FLMike  5 @eduardo_izzo  2   Meekou 2   @rzuber  2   @Velegandla  2     @PowerPlatform-P  2   @Micaiah  2     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Week 3:Community MembersSolutionsSuper UsersSolutionsPower Apps anandm0861WarrenBelz86DBO_DV25Amik66Michaelfp13mmbr160647Giraldoj13FLMike31AmínAA13SpongYe27     Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Week 4:Community MembersSolutionsSuper UsersSolutionsPower Apps DBO-DV21WarranBelz26Giraldoj7mmbr160618Muzammmil_0695067Amik14samfawzi_acml6FLMike12tzuber6ANB8   SunilPashikanti8

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (1,265)