IsBlank Lookup Function returns incorrect value after certain time period or refreshing the app

[SH14]

Good Afternoon All, 

I use Edge in private mode to create Apps using Power Apps. First time PowerApp builder and poster. I am having a very odd issue. In my New Starter Power Apps Forum I have an objective to show certain cards properties as DisplayMode = view only or Visibility = false. This condition depends on the User().email that is detected whilst PowerApp is open. If the person logged into or using the App fall into a certain group they can only view certain Text inputs and other Text labels and Text inputs are hidden all together.

I have a SharePoint list called “New Starter Form Access". In the list it has three columns with three entries (rows).

* Title (Single line of text) description of the group

* Approval Level (Choice) selection of HR, Line Manager and IT

* Group (People or Group) selection of Office365 security groups for people. HR Line Manager and IT

The Power Apps form uses this SharePoint list as data connections.

In the Power Apps itself I have the App on Startup property use the following formula. The first three "Isdepartment" is the actual code I used. Me using powerapps and previewing the App should detect that I am part of IT. As a result the Lookup should return as false because I have a record email which matches User().email  in "New Starter Form Access" list under the column "group" for group IT.

Set(currentUser, User());

Set(isIT , IsBlank(LookUp('New Starter Form Access', 'Group (Group0)'.Email = currentUser.Email && 'Approval Level'.Value = "IT" ))); //false

Set(isHR , IsBlank(LookUp('New Starter Form Access', 'Group (Group0)'.Email = currentUser.Email && 'Approval Level'.Value = "HR" ))); //true

Set(isLM , IsBlank(LookUp('New Starter Form Access', 'Group (Group0)'.Email = currentUser.Email && 'Approval Level'.Value = "Line Manager" ))); //true

This is another formula I had tried and had the same issue. It worked originally and then stopped after reloading the page / coming into work the next day / or after a couple of hours. 

Set(VarLookupUser, IsBlank(LookUp('New Starter Form Access', 'Group (Group0)'.Email = User().Email))); // FALSE This is comparing any entries in SharePoint List group column to see if any email entries that equal currently logged in users Email. The IsBlank function tests for a blank value or an empty string returns boolean true or false. If I am logged into the PowerApps it should return false because I am listed in the Groups column for New Starter Form Access list so the returned value for IsBlank is false.

Then depending on the field 

For Visibility property for datacards I have the following formula 

isIT

For DisplayMode for datacards I have the following formula 

If(!IsBlank(VarLookupUser),DisplayMode.View,DisplayMode.Edit)// IsBlank is false user can edit

To try and understand what was happening in the logic I created some validation fields in the form. 

Display User.()email
Display if User.()email was the same as xxxx@xxx.com and out detected or not detected
Display The value of IsBlank for VarLookupUser

Display The value of IsBlank for IsHR
Display The value of IsBlank for IsLM
Display The value of IsBlank for IsIT

I had noticed when I first enter the formula's for OnStartup for App property the Value of the Variable's IsBlank is what I would expect 

For Me it should 

value of IsBlank for VarLookupUser = false

value of IsBlank for IsHR = true
value of IsBlank for IsLM = true
value of IsBlank for IsIT= false

As my email is in listed in IT Group for the SharePoint list for both IsIT and VarLookupUser. 

However, they are all now showing as 

value of IsBlank for VarLookupUser = true

value of IsBlank for IsHR = true
value of IsBlank for IsLM = true
value of IsBlank for IsIT= true

Meaning that its not detecting my users email when looking up in the SharePoint list. Its odd how it works the first time I code the formula then after certain time elapses or I reload the page It for myself IsBlank is being detected as true. Its also like the detection is not being run correctly / is cached or not able to find the entries. Any suggestion would be greatly appreciated. 

[MarkBandR]

Hi @SH14 

I think your IsBlank logic is backwards. Try:

Set(isIT , !IsBlank(LookUp('New Starter Form Access', 'Group (Group0)'.Email = currentUser.Email && 'Approval Level'.Value = "IT" ))); 
Set(isHR , !IsBlank(LookUp('New Starter Form Access', 'Group (Group0)'.Email = currentUser.Email && 'Approval Level'.Value = "HR" ))); 
Set(isLM , !IsBlank(LookUp('New Starter Form Access', 'Group (Group0)'.Email = currentUser.Email && 'Approval Level'.Value = "Line Manager" ))); 

You should have "!" in front of the "IsBlank" to effect "Not".

-Mark

If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up.

[mmbr1606]

hey @SH14 

can u try this:

Set(isIT, IsBlank(LookUp('New CMS Starter Form Access', 'Group (Group0)'.Email = currentUser.Email && 'Approval Level'. Value = "IT")));

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[SH14]

Thank you for the suggestion. I have tried changing to !IsBlank and copying your code / replacing mine. However  it also does not produce the correct result. It changes all the variable test output from True (using IsBlank) to all being False (using !IsBlank)

value of IsBlank for VarLookupUser = false

value of IsBlank for IsHR = false

value of IsBlank for IsLM = false

value of IsBlank for IsIT= false

When I run the PowerApp in editor mode. Select App in tree view > right click > run on start you can see sometimes for a split second it will change to the correct values then change to the incorrect values. This seems to happen when using !IsBlank or IsBlank. Which reflects my earlier point that the code seems to work until the form is reloaded or certain amount of time as elapsed then it just stops working.

value of IsBlank for VarLookupUser = true

value of IsBlank for IsHR = false

value of IsBlank for IsLM = false

value of IsBlank for IsIT= true 

OR

value of IsBlank for VarLookupUser = false

value of IsBlank for IsHR = true

value of IsBlank for IsLM = true

value of IsBlank for IsIT= false

I have created another column in my SharePoint list with just people rather then groups and see if that makes any difference referencing people rather then groups.

[SH14]

Thank you for your suggestion - I have tried your code removing the "CMS" section to the SharePoint list. Unfortunately I still get the same incorrect results with all users being displayed as true. 

[mmbr1606]

hey @SH14 

thanks for trying. can u give this modification a chance:

Set(isIT, Not(IsBlank(LookUp('New Starter Form Access', 'Group'.Email = User().Email && 'Approval Level'.Value = "IT"))));

Let me know if my answer helped solving your issue.

If it did please accept as solution and give it a thumbs up so we can help others in the community.



Greetings

[SH14]

Hi - Thank you helping with this with that amendment I still get the same issue.

I had to change your code slightly because this section 

 'Group'.Email 

Was not being recognised so changed to what I had 'Group (Group0)'.Email. The Group0 I will look into to see what that is referencing. It came up with the auto filler for the formula.

As you can see the amended code is below 

And you can see in the output everything is false on the validation. IT should be different to both HR and LM

[MarkBandR · Accepted solution]

Hi @SH14 

After reading thru all of this, I have some questions.

1) When is this code being called to set the "isIT","isHR","isLM"? Is it called from "App" - "OnStart" or a button click? While troubleshoot, try placing the code in "OnSelect" of a button on the Form you are displaying the values on.

2) How are the People Picker fields set up in your New Starter Form Access list? Are you allowing Groups? Or are you limiting it to "People Only".

You should be using "People Only" as the comparison for "'Group (Group0)'.Email = currentUser.Email" will not work with a group. I just want to verify that you were not expecting that Power Apps would check that a user is a member of a group if a group is selected for the People Picker.

2) Could your variables be getting set or reset from code some where else in the App? Use the Search to find all instances or references to "currentUser", "isIT", "isHR", and "isLM".

This is a very strange issue you are having.

-Mark

If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up.

[SH14]

1) The Set variables with the Lookup condition is set during the App > “OnStart” property

2) The “Group” column for SharePoint list for the “New Starter Form Access” uses the following properties

• Allow selections of = “People and Groups”
• Choose from = “All users”

So, these are the same settings you have mentioned in your post.

3) The variables are not being reset from else where in the App. I have searched for all the Set instances.

Solution

I did some testing on the Friday with a colleague, and we had come to a similar conclusion as you have mentioned. Apologies for not posting sooner.

Instead of using groups I changed the SharePoint List to include individual users under the “Group” column instead of the groups (New Starter Form IT Group, New Starter Form Line Manager Group and New Starter Form HR Group). To identify if it was an issue with using groups.

The same formula then kicked in and started to work as expected. The validation fields all showed the correct result whilst I was signed into PowerApps.

IsHR !IsBlank = false

IsLM !IsBlank = false

IsIT !IsBlank = true

I thought when using these Microsoft 365 groups the formula was checking for an email against current users email in the group for the “Groups” column.

However, if it’s a group it may have been looking at the groups email (New Starter Form IT Group, New Starter Form Line Manager Group and New Starter Form HR Group) rather than a member of the groups email.

This was possibly the root cause. Hence changing it from a group to individual user worked.

Now that you have said

“You should be using "People Only" as the comparison for "'Group (Group0)'.Email = currentUser.Email" will not work with a group. I just want to verify that you were not expecting that Power Apps would check that a user is a member of a group if a group is selected for the People Picker.”

This has solidified my theory. I’m guessing there is no way to compare within a group for the code to Lookup against a “person/member within a group” rather than I think what the code was saying “look up a group”.