Filter on datasource

[leemex]

Hey there

I'd like to use the filter function and filter on a datasource. Is this somehow possible?

E.g. when User().Email = john.doe@powerapps.com use datasource "john_doe".

I hope this makes sense. The reason I'm asking is that each of our users has his own table/datasource. Whenever someone is using the PowerApps application it should only show his/her table. 

[DavesTechTips]

Good day.

Unfortunately this is not possible that I am aware of.

Why does every user have his\her own datasource? Can you not use the same data source and filter the data based on the logged in user?

Kind regards

twitter: @DavesTechTips

youTube: https://www.youtube.com/channel/UCIMe_ErQZP5chbI9UyW0xLw

[leemex]

Awww, that kinda sucks.

Unfortunately not. The DB schema is given (SAP) and I can't change it. Maybe my thinking is wrong and there might be another solution, but I want to display a set of data in a gallery based on the logged in user. This "set of data" is always a table and it varies based on the logged in user. 

[DavesTechTips]

Hi @leemex

Will this always be used for reading data, or do you want to update data through it as well?

You could use a custom HTTP connector, or Flow to retrieve some dynamic data.

[leemex]

It is only used to read data, yes.

The custom HTTP connector I can only create with the help of Logic Apps (Azure), am I right?

[DavesTechTips]

You can use it from PowerApps and Flow as well.

You could also use Flow to issue SQL queries to SQL which could be dynamic based on that is passed to it from PowerApps. 

See below very cool video from Mr Dang on how this can be done.

https://www.youtube.com/watch?v=K_H5r0nzwy4&t=385s

This is an example of how we have used it in the past.

Issue SQL queries from Flow

[Drrickryp]

Hi Lee,
If you put all of the tables in one excel file, (different sheets) you could import them all as static data files. Then, use a global variable as an alias for the datasource, ie. Set(var, DatasourceForUser1) as the data source for various controls. Then have a complex If() statement assigning each user's table to the global variable depending on the user's login credentials. If(user.email=xxx,Set(var,Table1,user.email=yyy, Set(var,Table2,etc.

[leemex]

Thanks for that suggestion. Is the filter function working with a variable? I already tried to use a label field containing the right name like so Filter(label, ....), but without success.

[v-monli-msft]

Hi @leemex,

You could try to add all these data sources to this app, and then use If function to make controls to use different data sources according to the login user. For example, you have SharePointList1 and SharePointList2 data sources connected to this app, then for a gallery control, set below formula in the Items property:

If(User().Email="xxx",SharePointList1,User().Email="xxxxx",SharePointList2)

and so on..

Regards,

Mona

[leemex]

Hi @v-monli-msft

I see your point. Thanks...

However, there might be hundreds of users (leaving the company, joining the company) which is why a hardcoded name isn't a solution.

It would need to be more something like:

If(user().Email = Lookup(...)

to check a table for the right email address assigned to that user. As each other will get his own table I can't always rewrite PowerApp to add the new tables as data sources.