Lookup values not displaying (Excel table in OneDrive as source)

[Zanette]

Hi there;

I realize Excel is not optimal for this, but I am doing this project as a proof of concept.

Nonetheless, my goal is technically pretty simple: if a user is an Admin (based on values "True" or "False"), then a button should be displayed.

I've done my fair share of googling up to this point but I can't get this to work. Currently, my OnStart has:

Set(CurrentUser, User().Email);

If(CurrentUser in tblSalesRep.Email, Set(IsUser,true) ,Set(IsUser, false));

If(LookUp(tblSalesRep, CurrentUser in tblSalesRep.Email, 'Admin') = "True",Set(IsAdmin,true),Set(IsAdmin,false))

The CurrentUser part works; and if just create a label and set its test to "isUser", I am getting "true" (considering my email is one of them in this table/column tblSalesRep.Email).

So technically I could use the "IsUser" variable for displaying the button I am referring to, BUT, I'd like for it to show up ONLY for the people with Admin = True, and I can't get that part to work to save my life.

The data/table is pretty straight forward (i switched it for some fake data, but in this case, pretend I am logged as Carlos Santana, with carlos@test.com and when I type "CurrentUser" I retrieve "carlos@test.com"):

SalesRep	FirstName	LastName	Email	Admin	Approver
SANTANA, CARLOS	CARLOS	SANTANA	carlos@test.com	TRUE	TRUE
ROSE, AXL	AXL	ROSE	axl@test.com	FALSE	FALSE

Do you guys have any idea of why I can't get the Admin = TRUE to return? I suppose it could be related to data types (text vs boolean) but I think Excel only allows text in this case, and I thought I would overcome that by adding quotes around "True" on my Lookup.

I didn't even know PowerApps existed until last week and have been having a blast until now. Any suggestions will be very much so appreciated!

[jhall]

Case?  PowerApps is case sensitive and "True" <> "TRUE"

[Zanette]

My apologies, I failed to mention that. For whatever reason when I pasted the table here, my True/False became TRUE/FALSE, but, in the table itself they are displayed as "True" and "False", and text type.

[jhall]

Well, the way to verify what's going on is to look at the Collection that was created from your Excel document.  Because when you link to an Excel Table, you aren't doing a live link, you're doing an import.  So keep in mind that if you've changed your Excel document since you first started and haven't deleted/recreated your Excel table import (View | Data Sources | Remove | Add data source | Import From Excel...), then the data you think you are querying...isn't.

What happens when you do this is you create a "Collection" (PowerApps' word for essentially a Table).  This Collection is just like you hand-typed all of this and hard-coded it into your application.

This is not the same as when you link PowerApps to SharePoint where the data is live and updates in real-time.

[Zanette]

Thank you for that, I just gave it a try but unfortunately it was a no-go. I had been constantly refreshing the table (which I thought would do the same as removing and adding the table again), but that hadn't been working either.

It sounds like such a simple thing to do, but I've exhausted some 4 hours into this and am losing my mind, haha.

[jhall]

Well, next step might just be to connect your Collection to a Data Table.  Drop in a Data Table, change the Items to your Excel source, Select all of your columns and then look at the data to ensure it is what you think it is.

[v-xida-msft · Accepted solution]

Hi @Zanette,

Could you please share a bit more about your issue?

Do you mean that the LookUp formula within your app could not retrieve proper value from your Excel table?

Further, is there something error with your LookUp formula in your app?

Based on the formula you provided, I think there is something issue with it. It is not necessary to add a single quote to wrap the Admin column. Please consider modify your formula as below (set the OnStart property of the App control to following😞

Set(CurrentUser, User().Email);
If(CurrentUser in tblSalesRep.Email, Set(IsUser,true) ,Set(IsUser, false));
If(
   LookUp(tblSalesRep, CurrentUser in Email, Admin) = "True",  /* <-- Modify your formula here */
   Set(IsAdmin, true),
   Set(IsAdmin, false)
)

Or

Set(CurrentUser, User().Email);
If(CurrentUser in tblSalesRep.Email, Set(IsUser,true) ,Set(IsUser, false));
If(
   First(Filter(tblSalesRep, CurrentUser in Email)).Admin = "True",  /* <-- Modify your formula here , Type Email column directly here rather than  tblSalesRep.Email */
   Set(IsAdmin, true),
   Set(IsAdmin, false)
)

Please take a try with above formula, then re-load your app (fire the OnStart property of the App control), then check if the issue is solved.

Note: I assume that the Admin column is a Text type column in your Excel table.

If the issue still exists, please consider re-create a new connection to your Excel table from your app, then try above formula again, check if the issue is solved. Also please make sure the Admin column is a Text type column rather than a Calculated column.

Best regards,

[Zanette]

Do you mean just creating a table inside the app and pointing it to the data source? If so I did that, and it shows up the same as in the did in Excel (both the online version as well as when I opened the file in my desktop). All versions, except for the one I pasted here showed "True" or "False" (in this format, not all caps).

Nonetheless, thanks again for the help! I am sure the issue here is the user, but I just can't seem to get this one figured out...

---

@jhall wrote:

Well, next step might just be to connect your Collection to a Data Table.  Drop in a Data Table, change the Items to your Excel source, Select all of your columns and then look at the data to ensure it is what you think it is.

---

[jhall]

Always good to check the basics.  😉

Did you try @v-xida-msft 's small change to your formula (e.g. removing the quotes):

LookUp(tblSalesRep, CurrentUser in Email, Admin)

 If you create a Label and put the above code in for the Text value, does it populate as expected (e.g. True/False)?

[Zanette]

@v-xida-msftThank you very much for the thorough response! The field type is indeed Text for the True/False field.

Please pardon my dumbness, but I just didn't fully understand what you meant by "

/* <-- Modify your formula here */

I am not really a coder, so sometimes even the most basic things can baffle me. Nonetheless I am trying this out as we speak.