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

Re: Filter a filter

Hi nickduxfield,

 

thank you for this great idea. you even helped my by discovering the onStart method.

I tried to use you explanation and it worked fine until point 2. there i have the problem, when i try to filter the projects by the user project i always get an error.

I think the reason is, that the ProjectUserCollection returns more than one value.

Any further ideas?

 

Thank youn and kind regards

nickduxfield
Level 8

Re: Filter a filter

Wow thats great,

Lets try and solve this, it is a data model I know people will have difficulty with because we are aways looking for everything on one table.

 

You need to investigate, what exactly the error says. Hover your cursor around the code or and warnings and see what its saying.

I dont know your data source (SharePoint List, Excel).

 

  • If you dont have a 'Modified' (date) column you'll have to remove the Sort by Columns until you have a column you can use.
  • Maybe you can tell me the column types, text, number etc.
  • Open up the file menu, and look at the collections you created. See if they actually exist and i the way you anticipated.

 

nickduxfield
Level 8

Re: Filter a filter

@grunwald Is your data model fixed? Do you have exisiting data or is this from scratch?

From what I can see without putting some limits on your method, you may have to rethink your data model.

 

You can start with a user, thats solved, you dont need a list.

You need only one or two lists as far as I can see.

Projects and ProjectRegistry

 

Projects list:

A place to register all projects and associated project info.

 

Project Registry:

A place to bring together User info and the Project from the Projects List.

  • You can use an additional textinput with a dropdown on a card to lookup project titles. Other associated fileds like 'Budget' can be autofilled using 'Lookup()'.

Then you will be able to pre-filter.

 

From what I can see, however hopeful we are about collections filtering collections, I'm not certain I can work yet.

Happy to talk more about it.

 

 

grunwald
Level: Powered On

Re: Filter a filter

Hi nickduxfield,

 

we solved it!!!

 

i tried it like you described

it has german writing, but i think everybody gets it.

:

  1. OnStart ClearCollect(varSet;Set(curUserID; LookUp('[dbo].[tblBenutzer]';User().Email = email;ID));Collect(projectCol;'[dbo].     [tblProjekt]');Collect(userProjectCol;Filter('[dbo].[tblProjektBenutzer]';fk_benutzer_id=curUserID)))
  2. Gallery Items Filter(projectCol;ID in userProjectCol.fk_projekt_id)

 

You can compare type Collection with the in operator.

 

Thank you so much for your help

 

 

 

Btw. my datasource is an azure sql server

nickduxfield
Level 8

Re: Filter a filter

Thats amazing, I setup something similar in SharePoint lists to get to the bottom of it but mine failed.

Perhaps delegation for type collection only works in Azure databases. Wow, that really opens doors! Fantiastic!

Il try the IN operator in my setup.

Tried the In Operator, Works!

Can you accept as a solution to help by brownie points.

DRCICI_Dev
Level: Powered On

Re: Filter a filter

 

Hi I am trying to filter a filter. and this is how it looks like. if someone could help on thing issue it would be a great help. thanks in advance. You can also Email me on my personal Email. poliamcrumb@gmail.com. Thanksexport.png

Anonymous
Not applicable

Re: Filter a filter

I'm just having fun with the title. Filter a filter. lol. There's probably a better way of saying that.

 

In any case, @DRCICI_Dev -- 

As suggested in the previous posts, use the in operator to simulate joins in PA. Or AddColumns(). There's a bunch of posts about it in this community.

 

On second thought, I absolutely have no idea what you're trying to do with this bit of code:

Filter(Participant, ID <> 'Attended Event'.ParticipantID && 'Attended Event'.PrimaryId = eventGallery.selected.PrimaryID)

Thoughts:

  • Do you want to get the list of events that a particular participant hasn't attended?
  • Or do you want to get the list of participants that have NOT attended a particular event?

 

 Edit. Minor typos.

DRCICI_Dev
Level: Powered On

Re: Filter a filter

Hi @Anonymous. Thanks for the reply. Sorry for the very funny title.

I am trying to get the the participant who have not yet attended the event.

 

I Just read the documentation of columns on powerapps. I see the best way was ShowColumns.

Filter(Participant, ID <>  ShowColumns('Attended Event', "ParticipantID"))

 

I triend even a simple way to get the Participant. But it did not filter it properly.

 

I also did not include the filter on the event cause it would be better if we have a condition on the ShowColumns.

Thanks

Anonymous
Not applicable

Re: Filter a filter

@DRCICI_Dev --

First of all, the title's nobody fault. No need to apologize over it. I'm actually glad it got me to laugh. So, on to the code!

 

Okay. I admit. I haven't even known there was a function such as ShowColumns(), although I see how it can be useful in certain situations. However, to do the thing you want to do, it's not required.

 

So, given an event -- find out the participants who haven't attended that event. Try this.

Filter( Participant,
    !( ID in Filter( 'Attended Event', 
        PrimaryID = eventGallery.Selected.PrimaryID
    ).ParticipantID )
)

I nested the Filters together just so you could use it directly in a property like Items. This is certainly not going to be delegated (because of the nesting) but then again, I can't think of a way here to ensure delegation and still get what you want (because you definitely need the in operator or some variation of it). 

 

Take the code with a grain of salt, by the way. I don't exactly know what the names are for your objects, columns, and such so I extrapolated. Revise it as needed.

 

Lemme know how it goes.

 

P.S. There's a lot of similar questions posted in this community about this topic. I'm not going to link those posts (frankly because I'm too sleepy right now, lol) but look for the following keywords -- Relational Data, Joins, Parent-Child Relationships, LookUp Fields, etc.

 

P.P.S. Just a suggestion -- Next time you have a problem with PowerApps dev't, create another thread/post. It just seems like your problem isn't getting that much attention and that may be caused by the fact that the problem in the first post is indicated to be solved. @mackenzie_lyng -- Is it possible to migrate a part of this thread onto a new one at the behest of the posters?

 

Edit: Forgot a minor point. Don't snooze and type, guys!

DRCICI_Dev
Level: Powered On

Re: Filter a filter

Thanks @Anonymous it's working but powerapp found some error. I will go check the title you are telling me. so i could see the best or proper way to filter it.  just so everybody will be on the loop.

the warning message

 

" Part of this Filter formula cannot be evaluated remotely due to service limitations. The local evaluation may produce suboptional or partial results. If possible, please simplify the formula. For more information, please see the filter function documentation.

by the way @Anonymous what document are you look at. cause I have never seen the impression "!" or in coding terms "not empty" or "not in" on the documentation of powerapps. I am new at this. I have been working on powerapps for 2-3weeks now. and I have already create a working system but I want to improve it more. 1 example is this question I cannot find a solution on documentation and also I think i had a hard time looking for the proper keywords. So I was not looking hard enough. 

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 (Last 30 Days)
Users online (5,285)