cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbrit2020
Super User
Super User

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

@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.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

@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
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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