Check if User entered Data Today in SharePoint (Lookup and Date Created issue)

[hexarthrius]

Hi All,

There's this issue I'm facing where I would like to do multiple scenarios. Here are my business scenarios:

Data Source: SharePoint. Two Data sources:

SPList1

SPList2

Rules:

Check for TODAY if:

• User already has entry in SPList1
  • If SPList1 Response of user = Choice 1
    • Proceed to do navigate to Screen1
  • If SPList1 Response of user = Choice 2
    • Check first if User already has an entry in SPList2
      • If with no entry, go to Screen2
      • If with entry, go to Screen3
  • If SPList1 Response of user = Choice 3
    • Check first if User already has an entry in SPList2
      • If with no entry, go to Screen2
      • If with entry, go to Screen3

Basically there are three choices and some choices have branching conditional checks that will navigate to a specific screen. What I'm having trouble at is my lookup. I can look up the SharePoint data correctly but it seems that when I want to check if the lookup has value or not, it returns false even if there is a record there.

My Setup: 

In the OnStart, I created a collection in the app to store data since it's from SharePoint and the lookup is non-delegable, AND set a variable to test the true or false of the lookup:

Concurrent(
ClearCollect(colWorkData ,Filter('Employee Workday Data',SPFlowID>=1&&SPFlowID<=2000)),
ClearCollect(colWorkData1 ,Filter('Employee Workday Data',SPFlowID>=2001&&SPFlowID<=4000)));
ClearCollect(colWorkDataCollection,colWorkData, colWorkData1);
Set(varWithWorkData, false)

In my first screen on the OnVisible page of Screen1, I've set up the following:

If(
    IsBlank(
        LookUp(
            colWorkDataCollection,
            Lower('Created By'.Email) = Lower(User().Email)
        )
    ),
    UpdateContext({varWithWorkData: false}),
    UpdateContext({varWithWorkData: true})
)

Issue:

The first time I tried this, the value was always false when I tried without the Lower() function. But after adding the Lower() function, the value is now always true, even if I tried deleting my records in SharePoint (there are no records now there but this value is still true).

For now, I'm stuck with this and am puzzled why this is not working. My next step is to add the date today for the lookup since this is daily. Any help with this would be great.

Thank you

[WarrenBelz]

Hi @hexarthrius ,

Firstly, you have three non-delegable elements in your query - Lower(), 'Created By' and User().Email, but that does not explain your results as your query is valid (at least on smaller data sets). Also you should not need Lower() as 'Created By' should have the same case syntax as User().Email according to your company's naming conventions. A little trouble shooting try this first - at App OnStart

Set(vUserMail, User().Email)

then

If(
   IsBlank(
      LookUp(
         colWorkDataCollection,
         'Created By'.Email = vUserMail
      )
   ),
   UpdateContext({varWithWorkData: false}),
   UpdateContext({varWithWorkData: true})
)

To test the Lower() theory, put two labels in the gallery - one with ThisItem.'Created By'.Email and the other with User().EMail and see if the case structure is the same.
If you have over 2000 records, I have a further strategy.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[hexarthrius]

Hi @WarrenBelz,

No worries, I should have mentioned I'm using a collection at the OnStart of the App:

Concurrent(
ClearCollect(colWorkData ,Filter('Employee Workday Data',SPFlowID>=1&&SPFlowID<=2000)),
ClearCollect(colWorkData1 ,Filter('Employee Workday Data',SPFlowID>=2001&&SPFlowID<=4000)));
ClearCollect(colWorkDataCollection,colWorkData, colWorkData1)

The SPFlowID is automatically updated via Flow to get the ID so I don't face delegation issues. I would've done calculated columns but based on my tests it's not updating correctly always so I used a Flow (just sharing this, no need for us to delve into this concern further for now).

Also, I've tested the Lower() with and without, and yes you are correct that they are the same. I'll remove that in the formula later.

I'm going to try the User().Email to be stored in a variable now, I'll keep you posted in a few minutes.

[WarrenBelz]

@hexarthrius ,

Yes I did see that but was not sure this was the list with the email address.

I suspect the vUserMail will work as there are no Delegation issues using a Variable against a Collection.

You might also find this post of mine on larger collections useful, but if you also want a good old-fashioned solution for your shadow ID, you can do this with a one-line SharePoint Designer Workflow triggered on item creation setting the field to the ID number.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[hexarthrius · Accepted solution]

Hi @WarrenBelz,

Thanks for the response. Is SharePoint Designer still a good option for SharePoint Online moving forward? I'm curious on this as I've stopped using it since when SPO started and I migrated most of our sites to SPO from on-prem.

In any case, I found the issue. It seems that the logic is correct, but it looks to me that it's not following the logic correctly on the app's OnStart. I tried adding in a wait time via Timer and added this on the OnTimerEnd, and it worked perfectly! I'm not sure though why OnStart or OnVisible is not applicable to these kinds of logic formulas. I'm ok with the delegation issues btw since I just patch the ID of the last submitted form in SharePoint via Patch on the OnSuccess of the form earlier.

Maybe before I close the thread, would you be able to know why my code is not working correctly on the OnStart/OnVisible but works after a few seconds of waiting time?

Best,

David

[WarrenBelz]

Hi @hexarthrius ,

I hope workflows keep going as I have dozens still running and doing the job required. As well, they do not count towards Flow run totals and work much quicker than Flows generally.

It is interesting that a timer fixed the issue, though the code you have would take some time to execute and maybe Power Apps "moves on" if the next bit is unrelated to the collection - only a guess as I have not struck it.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.