cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbrit2020
Continued Contributor
Continued Contributor

Filter gallery using secondary SQL table

Hello,

 

I'm using filtered values in Gallery 1.AllItems to filter Gallery 2 at the moment, this works fine for now while Gallery1 doesn't go over 100 records, so I've been looking to change my approach and filter Gallery 2 directly from the SQL Table I'm using for Gallery 1.

 

Gallery 2 must display ALL values it looks up from the SQL Table that meet a certain criteria.

 

If i just do a "Filter(Table, Column in Table2.Column) it works and displays all values existing in table 2.

 

But when I introduce a Lookup to the Table2 so I can introduce a Criteria, it only returns the FIRST value found.

 

This is the working code without conditions for Table 2

 

If(IsBlank(ComboBox1_1.Selected),
     
     Filter('Table1',Company in 'Table2'.Meeting,
        
           Status = If(Toggle1_5.Value,"Open","Completed")),

     Filter('Table1',Company = ComboBox1_1.Selected.Meeting,
                 
           Status=If(Toggle1_5.Value,"Open","Completed")))

 

 

When I introduce a lookup on Table2 to search only for items which contain the user's email address, it returns only the first found value.

 

Any idea on how to overcome this?

 

Regards

 

4 REPLIES 4
RandyHayes
Super User
Super User

@jbrit2020 

Please consider changing your Formula to the following:

Filter('Table1',
   (IsBlank(ComboBox1_1.Selected.Meeting) || (Company in 'Table2'.Meeting)) &&
   Status = If(Toggle1_5.Value,"Open","Completed")
)

In general using If statements to choose between different filters will double your work and make the formula more difficult to maintain.

 

Now, you mentioned a Lookup in your post.  A lookup will only return the first record it encounters.  But I am not seeing how you are trying to incorporate that.  Can you expand more on what you are attempting to do with the lookup?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
jbrit2020
Continued Contributor
Continued Contributor

@RandyHayes thank you for the tip.

 

This still doesn't resolve however my issue with the condition for table 2 where "(Company in 'Table2'.Meeting))" should include a condition for Table2.

 

Filter('Table1',
   (IsBlank(ComboBox1_1.Selected.Meeting) || (Company in 'Table2'.Meeting)) &&
   Status = If(Toggle1_5.Value,"Open","Completed")
)

 

Regards,

RandyHayes
Super User
Super User

@jbrit2020 

Yes, the prior response was not necessarily intended to resolve as I had an outstanding question - it was more of a generality.

Can you expand more on what you mean by including a condition for Table2?

You originally stated that something was only returning one record (the first) and mentioned Lookup - so I assumed you were using a LookUp that only returns one record.

 

To get in your first formula - you were in one place doing an in operation to Table2 and then in another doing an equality to a selected company in a combobox.

 

So, trying to understand a little more about what you're trying to get to.  Sorry if I'm not seeing it clearly.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
jbrit2020
Continued Contributor
Continued Contributor

@RandyHayes yes I was using a lookup, which doesn't return all matching existing values, but only 1 value.

 

The Key here is "Company" column in Table1, this Column matches the values on Meeting column in table 2.

 

At the moment with the code I've pasted here my gallery is displaying ALL records that match ALL the records on Meeting Column in table 2, but I don't want compare the whole of Table 2. I only want to compare the records on table 2 that belong to a certain user. The user each record belongs to are on a second column called "MeetingAttendee".

 

Hopefully this makes my problem slightly clearer.

 

Regards

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (1,892)