cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver III
Resolver III

Re: Get emails from excel database and make a dropdown editable if they match emails with the user

Hi @TimoMigchielsen ,

 

I would not use the in function but a lookup and see if this lookup is empty or not.

 

If(!Empty(LookUp(ExcelDataSource, User().Email = EmailAllowedUser)).Displaymode.Edit,Displaymode.View)

I also use !Empty instead of Empty because I always assume that you mostly want to trigger the true of the if statement (just a habit)  

 

Hope this helps.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Post Prodigy
Post Prodigy

Re: Get emails from excel database and make a dropdown editable if they match emails with the user

Thank you for your answer,

 

What does the empty function do? Does it check if they row is empty? Cause basically I want to have the email adresses in there of my admins and I want to have a few icons only visible for them. So why would you use the empty function for? 

@KennethNicasens 

Highlighted
Resolver III
Resolver III

Re: Get emails from excel database and make a dropdown editable if they match emails with the user

@TimoMigchielsen ,

 

Sorry the function is IsEmpty.

 

This will check if your table is empty ( if you do a lookup you will have 1 or no result). Based on this you get a true(it is empty) or false(I found a record) that you can use in a If statement to show icons or not.

 

If you want to show multiple things you can also put this in a variable so you won't need to check it every time.

Set(_UserIsAdmin,!IsEmpty(LookUp(ExcelDS,User().Email = EmailColumn)))

 

Highlighted
Post Prodigy
Post Prodigy

Re: Get emails from excel database and make a dropdown editable if they match emails with the user

I'm still a bit confused, what is the reason to check if the table is empty? It will never be empty so you will always get a true statement? There will always be an administrator in there. I just want to check if the email from the person starting the app equals one of the emails in my table and then show the icon. 

@KennethNicasens 

Highlighted
Resolver III
Resolver III

Re: Get emails from excel database and make a dropdown editable if they match emails with the user

Example:

TableName: AdminUsers

NameEmail
Test01Test01@domain.com
Test02Test02@domain.com
Test03Test03@domain

 

First case: I am Test02 user.

If I do Lookup(AdminUsers,User().Email = Email) I will get:

NameEmail
Test02Test02@domain.com

 

So If I do the code:

!IsEmpty(Lookup(AdminUsers,User().Email = Email)) I will get true

 

Second case: I am Test04 user.

If I do Lookup(AdminUsers,User().Email = Email) I will get:

NameEmail
  

 

So If I do the code:

!IsEmpty(Lookup(AdminUsers,User().Email = Email)) I will get false

 

You can use this to show or don't show other controls.

 

Instead of using LookUp function you could also use Filter (an email is a unique id so you know that there only be max 1 result after filter).

 

Highlighted
Super User II
Super User II

Re: Get emails from excel database and make a dropdown editable if they match emails with the user

It would appear as though the formula is resolving blank as true.

Can you post the formula(s) you're using? This is Excel online, via the OneDrive Connector, correct? I'll replicate and test for you.
Highlighted
Post Prodigy
Post Prodigy

Re: Get emails from excel database and make a dropdown editable if they match emails with the user

Yes, I am indeed using Excel and One Drive. Here is the formulas I'm using. 

Onstart of the app: Set(isUserAllowed, User().Email in Admins.Administrators)

Visible on the icon: If(isUserAllowed=true, true, false)

@GarethPrisk 

Highlighted
Super User II
Super User II

Re: Get emails from excel database and make a dropdown editable if they match emails with the user

Admins is the Excel table name, and Administrators is the Column name? Just double-checking.
Highlighted
Post Prodigy
Post Prodigy

Re: Get emails from excel database and make a dropdown editable if they match emails with the user

https://gyazo.com/794b7f88e98e5ebdf59ba85e4c0c3e18


This should explain it, but yes, you're right 🙂

@GarethPrisk 

Highlighted
Super User II
Super User II

Re: Get emails from excel database and make a dropdown editable if they match emails with the user

For both troubleshooting, and to ensure things are running in the correct order, I would recommend that you update your OnStart to be a bit more explicit. Meaning, let's call three functions in a series, which build upon one another.

  1. Set the global User's Email variable
  2. Collect the Admin Emails into a Collection
  3. See if the User's Email is in that Collection
Set(gblUser, User().Email);
Collect(colAdminList,Admin.Administrators);
Set(
    gblIsAdmin,
    !IsBlankOrError(
        LookUp(
            colAdminList,
            Administrators = gblUser
        )
    )
)

 That last function is doing a Lookup. That will check explicitly for the email, so it must match exactly. If nothing is found, it will return a Blank() object, that's why we are checking for Blanks or Errors, and then inverting (!) that result. Meaning, if we find a row in the collection that meets the criteria, then it will return a record and not be blank, which makes the IsBlankOrError check false, and our person is an admin (true).

The only real difference in this approach compared to others is the explicit ordering of the functions. This guarantees that we the values we need, and a local collection (for troubleshooting/reference), to set our ultimate Boolean variable.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,133)