cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
OneWSon
Level: Powered On

Filter a Gallery based on record in Form

I can't get my head around this.

 

I've an app based on two Sharepoint Lists; 

 

  1. tblJobs
  2. tblJobNotes (refers to tblJobs by JobID)

 

You can have lots of notes within a Job. You can see the Gallery underneath the Special requirements box. It points to tbljobNotes, I want to filter it so it only shows the notes for that job:

 

Capture.PNG

 

I've tried all sorts of : filter(tblJobNotes, JobID = [[[control on main form]]) but I just can't get it to work.

 

Any pointers?

 

thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filter a Gallery based on record in Form

Yup, I'd say it's the correct way to do it, but there might be other more advanced users who disagree with me.

 

I thought that was the case. For me, I find PA to be more "current record geared", if you understand what I mean, and not so bothered by the underlying data structure - there are no ways to do automatic links between related tables for instance, you have to set that up by yourself, like you do in your example.

 

Another example: if you want to delete a record and its related records, as fasr as I know, you would have to do that all by yourself in code, there is still no cascading delete you can do. Granted, it would take only two lines of code, Remove and RemoveIf, but still, you'd have to do that yourself.

 

 

You do have an underlying numerical value, true, but what you actually want to display is the text value from the related table, so you only use (not so only perhaps) the numeric ID-field to do the lookup. You don't bind the ID, you bind the related text value.

 

Think of the data you want to display, not what's actually in the table/record. You want to display text, so you have to get a text value, even if it comes to you via a call to lookup.

 

I'm rambling a bit, but I hope you understand what I'm trying to say.

View solution in original post

8 REPLIES 8
Super User
Super User

Re: Filter a Gallery based on record in Form

Hi!

 

Try this: Filter(tblJobNotes, JobId = [Mainform].DataCardXXValue.Value), or Filter(tblJobNotes, JobId = [MainscreenGallery].Selected.Id)

 

You have to fill in the correct control names. So, you have to link the JobId from tblJobNotes to the current record in the screen's form, or currently selected record's ID in the main gallery. It should work like a charm I, use it all the time.

OneWSon
Level: Powered On

Re: Filter a Gallery based on record in Form

Thanks for replying.

 

In the first example, my syntax is:

 

 

Filter(tblJobNotes, JobID = JobDetailsForm.jobCard.value)
Filter(tblJobNotes, JobID = DetailForm1.jobCard.value)

 

The = sign is saying it's an invalid argument and the .jobcard.value an invalid name

 

Filter(tblJobNotes, JobID = DetailForm1.Selected.Id)

Is giving me similar results. I even tried storing the record JobID in the main form in a label called jobID. 

 

Here's the screens names. Gallery4 is the sub gallery in DetailForm1, all on JobDetailsForm

 

 

 

Capture.PNG

OneWSon
Level: Powered On

Re: Filter a Gallery based on record in Form

The following syntax works:

 

Filter(tblJobNotes, JobID = SelectedJob.Text)

SelectedJob is simply a label on the main form showing the unique index from tblJobs

 

But I'm comparing a number and a text field, rather than a record in the underlying table connected to the main part of the form. (PowerApps complains about it, but lets it happen).

 

SO while this works, I don't think it's the right "way".

Super User
Super User

Re: Filter a Gallery based on record in Form

Good morning!

 

I din't think "simply a label" is a very accurate description - it's a label bound to a column in the database, containing a value from the currently displayed record, and that's why you need to use that value instead of referring to the column name; the column name doesn't contain the actual data.

 

On a conceptual level it is correct that you connect the two tables via the two column names, but in your case, and usually in PA, you want to get a specific record, hence the need for a specific value, i.e. the value from the bound control.

OneWSon
Level: Powered On

Re: Filter a Gallery based on record in Form

Thanks, BitLord.

 

It's probable I'm confusing terminology, being a bit more used to VB and Access, Labels aren't bound controls there and, but I'm slowly trying to pick together how PowerApps works and see what you mean.

 

So getting the record from a bound control (as I have done here) is the right way... is there a way to satisfy PA that I'm compariing two numerical values, rather than a number and a text value, or is that nitpicking? All my relational tables/lists use unique, numerical IDs; so if I'm binding them to a control, it makes sense for that to be stored as a number and not text.

 

Or I am still confusing how PowerApps handles data?

 

Many thanks,

Super User
Super User

Re: Filter a Gallery based on record in Form

Yup, I'd say it's the correct way to do it, but there might be other more advanced users who disagree with me.

 

I thought that was the case. For me, I find PA to be more "current record geared", if you understand what I mean, and not so bothered by the underlying data structure - there are no ways to do automatic links between related tables for instance, you have to set that up by yourself, like you do in your example.

 

Another example: if you want to delete a record and its related records, as fasr as I know, you would have to do that all by yourself in code, there is still no cascading delete you can do. Granted, it would take only two lines of code, Remove and RemoveIf, but still, you'd have to do that yourself.

 

 

You do have an underlying numerical value, true, but what you actually want to display is the text value from the related table, so you only use (not so only perhaps) the numeric ID-field to do the lookup. You don't bind the ID, you bind the related text value.

 

Think of the data you want to display, not what's actually in the table/record. You want to display text, so you have to get a text value, even if it comes to you via a call to lookup.

 

I'm rambling a bit, but I hope you understand what I'm trying to say.

View solution in original post

OneWSon
Level: Powered On

Re: Filter a Gallery based on record in Form

Thanks Bitlord.

 

I'm thinking I'm confusing "text" with "string" (and so conversely integer).

 

It's irking me that PowerApps is pointing out to me I'm comparing different sorts of data. This makes me think I've got it all wrong. (Even if it does work)

 

Untitled.png

 

Super User
Super User

Re: Filter a Gallery based on record in Form

You can avoid the error message by wrapping the SelectedJob.Text in a call to Value, like this:

 

...=Value(SelectedJob.Text)

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 181 members 6,291 guests
Recent signins:
Please welcome our newest community members: