Odd behavior using First & Filter together - getting *second* record not first unless wait long time

[PhilD]

The code I'm using consistently returns the next most recent entry in a SharePoint list as opposed to the most recent one. I built out a whole screen to test this so I could see everything going on. This allowed me to determine definitively that the filter part of the function is what causes this behavior. With the filter included it fails to find the very last qualifying record. Take the filter out and it works perfectly (except I need to have the filter of course).

Sequence is run code > add a record to SP > run code again in a minute or two and see failure. 

I'm at a loss... how can a documented function behave so intermittently? How are we supposed to build reliable code blocks when the simpliest little piece of code only sometimes returns the last record?

The only other thing that helps is waiting a LONG time (like 15 minutes). Then it works the first time but executing again even a few minutes later it fails the first time again. 

The target list is brand new, containing only plain text, number and date columns. Explicitly setting the ID using LookUp easily allows me to return the correct record, just not when I find it based on the filter condition. 

So to clarify, with the filter in place in the example below, I expect to get record ID: 5 but I consistently get ID: 6. If I execute the code again, I then get the correct result (5) which appears to demonstrate that  it is not the terms of the filter but that the last record is somehow not in a state that allows it to be returned in the result set the first time the code executes (even making all the records have filter parameters that qualify does not fix further demonstrating this).

Does not work

Events 

ID	EventClass	TaskID
8	Task	983	I want
7	Task	983	I get
6	Task	983
5	Task	983

   
    //find most recent previous entry matching conditions
    Refresh(Events);
    Set(varPreviousEvent,
        First(
            Sort(
               Filter(
                    Events,
                EventClass = varCurrentEventClass, TaskID = varTask.ID),
            ID, Descending)
        )
    );

Works

Events 

ID	EventClass	TaskID
8	Task	983	I want & I get
7	Task	983
6	Task	983
5	Task	983

    Refresh(Events);
    Set(varPreviousEvent,
        First(
            Sort(
                //Filter(
                    Events,
                //EventClass = varCurrentEventClass, TaskID = varTask.ID),
            ID, Descending)
        )
    );

Some things that didn't work:

• Substituting actual values instead of variables in filter.
• Collecting the list first, then filtering the collection (collection didn't include the very last result so didn't matter).
• Refreshing before, after, before & after.
• Switching the order of the Sort & Filter statements.
• Running the same block of code twice in succession.
• Using only one filter statement, tried both separately.
  
  

Update: I just recreated another SP list and a new app and was EASILY able to recreate this issue... code only works the *second* time you click the button unless you wait several minuts. I need to use this code block on the fly to find and update records and can't have it only work sometimes, when it feels like it. I really don't know what to do... I need this simple function in dozens of places around my app. Is this a bug? 

[erga]

@PhilD Thank you for reaching out to us and going the extra mile to help us diagnose this issue. I have two follow-up questions: 

1. Do you have something external that may be modifying your Sharepoint list (i.e. a Microsoft Flow which triggers when a Sharepoint data item changes)? 
2. The logs indicated that you ran your tests on WebAuthoring Preview Mode.. Can you save the app, publish it, and repeat the process while running the published app from web.powerapps.com? In this mode, you may see red toast errors, hidden in Preview Mode, that may help us with our investigation. 

You may also email me directly at erga (at) microsoft (dot) com.

[PhilD]

There are no Flows in use with this app. 

I will have to build some controls on my test sheet to switch the refresh events but here is a test I performed just now in the published app showing the same results using the code shown below. It did produce an etag mismatch error. Results are illustrated in the images belowthe code

    
 Refresh(Tasks);
 Set(varTask,GalleryCurrentUserTasks.Selected);

//SET EVENT STATUS MINUTES VARIABLES***************************************************************************
 //New
 //set the event class of the current event so we know which type to update later with the minutes in this status. choices are 'Employee' or 'Task' 

 Set(varCurrentEventClass,"Task"); // ** update to match current event class **
 //Set(varPreviousEvent, Blank());
 
 //get most recent *previous* Event entry that matches critieria of current event (will write StatusMinutes to this one later).

 //find last qualifying record by looking up the first one in a sorted, filtered set
 Refresh(Events);
 Set(varPreviousEventFirstPass,
 LookUp(Sort(Events,ID, Descending), 
 EventClass = varCurrentEventClass && TaskID = varTask.ID
 )
 ); 

 //find last qualifying record by looking up the first one in a sorted, filtered set
 Refresh(Events);
 Set(varPreviousEventSecondPass,
 LookUp(Sort(Events,ID, Descending), 
 EventClass = varCurrentEventClass && TaskID = varTask.ID
 )
 ); 



//END EVENT SET STATUS MINUTES VARIABLES***************************************************************************



//WRITE PREVIOUS EVENT STATUS MINUTES ***************************************************************************
 //set the event's time to use in the calculation
 Set(varPreviousEventTime,varPreviousEvent.Time);
 
 // calculated minutes between previous time & now
 Set(varPreviousEventStatusMinutes,DateDiff(varPreviousEventTime,Now(),Minutes));

 //now write calculated number of minutes to previous event row determined earlier
 //Refresh(Events);
 Patch(
 Events,
 varPreviousEvent,
 {StatusMinutes:varPreviousEventStatusMinutes}
 );

//END WRITE PREVIOUS EVENT STATUS MINUTES ***************************************************************************

If(CheckboxOperation1.Value=true,
//WRITE NEW EVENT ***************************************************************************
 //New
 //write timestamped event for current update
 //Refresh(Events);
 Patch(
 Events,
 {ID: Blank()}, // faster than using "Defaults" as only one call is needed
 {Title: varThisAppName & " New Test Event Operation 1"},
 {EventClass:varCurrentEventClass}, // set earlier, no need to change
 {EventType:"New Event Test operation 1"}, // ** update this to indicate current context **
 //{EmpID:368}, //plain text campus key from Employee table
 {TaskID:varTask.ID}, // auto number row ID from SharePoint list
 {TaskStatus:"New Event Test Assigned"}, // ** update to appropriate value, if this is a Task Event, value in "Status" = previously used "TaskStatus"
 {EmpStatus:"New Event Test Assigned"}, // ** update to appropriate value, if this is a Emp Event, value in "Status" = previously used "EmpStatus"
 {Time:Now()}
 )


//END WRITE NEW EVENT ***************************************************************************

);

Session ID: 1bac6e7d-9b10-49e5-8c73-06b63338d16f
PowerApps 3.18111.12

[erga]

@PhilD ETAG mismatch errors usually indicate a conflict with the current write request and the server data, which may have been updated to a newer version. If anything outside of your app modifies your Sharepoint table, then your app will throw an error asking you to reload your local data. The ETAGs feature is simply a mechanism that detects these conflicts. 

I see in our logs that there are some Flows that modifying your Sharepoint table on the server. These Flows don't necessarily need to be used in yout PowerApp. Can you help us figure out which Flows may be modifying the Sharepoint table data, independent of PowerApps? I am looking for something like: 

Thanks!

Eric

[PhilD]

Hmmm... there is  definitely not a Flow that makes changes to any of the tables involved. Becuase this app was designed for multiple users I always avoided using Flow to do anything to the data for just this very reason.

There are two Flows (one now deleted) on the same SP site as described and shown below.

Flow 1 sends me an email when someone requests access (using a different app altogether). The app I am testing on does not have this SP list as a data source and the SP list that triggers the notification is not linked to any of the tables used in the app. Also, the Flow does not write to anything, it is just an out of the box "send email" flow.

Flow 2 was disabled during all of this testing and was designed to send me an email when a new error was written to a SP list. Again, this Flow didn't write to anything, was disabled and has been deleted today just to be sure.

Since deleting  Flow 1 I retested with the same results just now (session ID at the bottom of this message). No errors, same behavior as before... the only way it works is if I go for coffee and come back - somewhere around 5 minutes or so seems to "reset" this and make it work. 

Flow 1

Flow 2

(now deleted)

Session ID: 1e43a0b3-c5e5-4ed5-b64d-b3aa39ca4790
PowerApps 3.18111.12

New information

I have discovered that refreshing the app completely makes this work reliably the first time (without waiting the 5 minutes).  Consider the scenarios below... maybe this provides a clue?

Scenario 1

• Open the app, run the operation - works fine. 
• Run the operation again without waiting 5 minutes - finds second to last record.
• Run the operation again without waiting 5 minutes - finds second to last record.
• Run the operation again without waiting 5 minutes - finds second to last record.
• Run the operation again without waiting 5 minutes - finds second to last record.

Scenario 2

• Open the app, run the operation - works fine. 
• Refresh browser/app to get new session ID.
• Run the operation - works fine. 
• Refresh browser/app to get new session ID.
• Run the operation - works fine. 
• Refresh browser/app to get new session ID.
• Run the operation - works fine. 

ID from last session where it worked

Session ID: ee9709c1-dcaf-4e75-955e-fc389be0dc7b
PowerApps 3.18111.12

[PhilD]

I have definitively ruled out "Improved app rendering" as having any connection to this issue. Same results either way. I also created indexes on all SharePoint list columns in the Events list which made no difference, again same results either way.

I have a stripped down version of my original app and can still easily and reliably reproduce this problem.

[PhilD · Accepted solution]

I think I discovered something important related to what is causing this!!!

In my test app, stripped of all unrelated and unneeded screens, datasources, etc. If I uncheck the feature "Use longer data cache timeout and background refresh" the lookup suddenly works as expected... ever time, reliably. If I turn it back it on, the problem immediately returns. Turn it back off, it is fixed.

Obviously I need to do some more testing on my actual production app but this is the first time I have found a reliable way to toggle this problem from fixed to broken and back again. 

Another thing, I have felt from the beginning that a lookup function should not have to depend on a Refresh() before or after it as this is what the lookup does... in my testing, when the feature is turned off, my lookup works without any refresh of the Events table anywhere in the entire routine!

[Paul_C]

Super glad to hear you're unblocked!

Thanks for letting us know, and for letting us know what was behind it! Knowing that it's related to this experimental setting will be hugely helpful as we continue investigating.

[Drrickryp]

@PhilD

Double kudos to you for figuring this problem out!  I am accepting your post as the solution to the original problem.

[kenji_gka]

@PhilD 

Thanks a lot! I was facing this issue for several weeks and now all works perfectly. Thats the only solution that solved my problem.