i am new to powerapps and maybe the solution is easy.
I want to create a prefiltered view on Projects.
If a users opens the app a userId will be looked up( ---> this works)
I store this value in a textbox named varUserId
with this userId Filter a m:n table where are useres assigned to projects so the result will
a set of project ids the user is assigned to
Filter('[dbo].[tblProjectUser]';fk_user_id = Value(VarUserID.Text))
When i display this in a DataTable it works.
How can i use this to prefilter the gally datasource tblProjects to see only the Projects assigned to the user?
Solved! Go to Solution.
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
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).
@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
A place to register all projects and associated project info.
A place to bring together User info and the Project from the Projects List.
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.
we solved it!!!
i tried it like you described
it has german writing, but i think everybody gets it.
You can compare type Collection with the in operator.
Thank you so much for your help
Btw. my datasource is an azure sql server
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.
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. firstname.lastname@example.org. Thanks
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)
Edit. Minor typos.
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.
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!
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.
Check out the on demand sessions that are available now!
Stay up tp date on the latest blogs and activities in the community News & Announcements.
Features releasing from October 2020 through March 2021
Check out the Power Platform Community Highlights