variable to store a users location looking through 5 SQL views

(0) Share

Report

Posted on by Lefty

1,625

@timl

You helped me loads with previous related questions to what am I further needing help with:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Help-with-filtering-a-gallery/m-p/1878169#M473014

I now need to look at 5 SQL views to store which location the user belongs to. The Ugroup column contains the users region but the logged in user can be in 1 of 5 different DBs.

Previously your suggestion worked brilliantly when it needed to look at 1 view in the single DB:

Set(varUgroup, 
 LookUp(Uview, UserEmail=User().Email).Ugroup
);
Set(varAllowOverride, 
 User().Email in Filter(Uview, DCode = "GT345").UserEmail
);

The team have now changed the structure, and separated the regional data, meaning there are now 5 DBs to search through for the 1 user.

What is the best option for me to find which location the logged in user belongs, can the above code be enhanced to cater for this? Maybe a switch function, I am obviosuly concerned about the performance hit this may have as it will be making a call to 5 DBs.

I would need to search through the same 5DBs to see if the user is an admin where varAllowOverride comes in to play too.

Apologies it's been a while so please feel free to ask any questions to see if you can help me?

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

timl 37,152 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi @Lefty

>> The Ugroup column contains the users region but the logged in user can be in 1 of 5 different DBs.

So just to clarify, there's no reference data that stores the target database for each user by email address, meaning that to find the Ugroup for a user, you'd need to find the first match whilst carrying out a LookUp on up to 5 views?

Was this reply helpful? Yes No
Lefty 1,625 on at

Like (0)

Report
Copy link

Link copied!

hi@timl

Yes precisely that, looking for the first match whilst carrying out look up

Was this reply helpful? Yes No
Verified answer

timl 37,152 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hi @Lefty

I think there are a couple of things you could explore.

One option would be to create a cross database view - a single view that 'unions' the views accross the 5 databases. From a Power Apps perspective, you would then be dealing with a single view so this would involve minimal changes to your app.

https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical?view=azuresql

Alternatively, you could call Coalesce to return the first non-blank Ugroup value. The syntax would look something like this:

Set(varUgroup, Coalesce( LookUp(Uview1, UserEmail=User().Email).Ugroup, LookUp(Uview2, UserEmail=User().Email).Ugroup, LookUp(Uview3, UserEmail=User().Email).Ugroup, LookUp(Uview4, UserEmail=User().Email).Ugroup, LookUp(Uview5, UserEmail=User().Email).Ugroup ) );

Coalesce should quit evaluating when it encounters the first non-blank value. Therefore, if Uview3 returns a result, the LookUps on Uview4 and Uview5 won't run.

Was this reply helpful? Yes No
Lefty 1,625 on at

Like (0)

Report
Copy link

Link copied!

Thank you for that, I will give Coalesce option a try.

I had already asked for the 5 views to be added to one but I got told no, as it was too much data for them to be collecting, like we have a few thousand per view... I will still ask them and send them your link if you think it should be something that is possible as it will make my life easier as my entire app is using this variable to all over the place

I will give it another try and will also try your 2nd suggested option, and report back once I've attempted it

Was this reply helpful? Yes No
Lefty 1,625 on at

Like (0)

Report
Copy link

Link copied!

@timl
Thanks as always for that it works!

Was this reply helpful? Yes No