Filter on collection not working consistently

[AaronW1]

My app has a gallery for users to enter time worked for project tasks, it uses the patch function to update the records in the collection when the user changes values on the screen. Records are saved to a SQL Server DB with patch, show columns and a filter to only save records with some value entered, for some users the save is not working consistently (not all records are saved to DB). This is not consistent for all users, I developed the app and am also an end-user and have never experienced this problem myself. I have witnessed this though by observing other users and haven't seen any differences in user activity that might be the cause. Below is my code to update the collection and save the records, any ideas what might be causing this? Thank you in advance.

** OnChange property for time entry **
Patch(
    colTimeEntryBulk,
    ThisItem,
    {WorkHours: Value(txtGlyTimeEntryBulkHours.Text)}
);

** patch records to DB **
Patch(
    ResourceTimeTracking,
    ShowColumns(
        Filter(
            colTimeEntryBulk,
            WorkHours > 0
        ),
        "TaskMilestoneID",
        "TeamMemberID",
        "CalDate",
        "WorkHours",
        "dCreated",
        "CreatedBy"
    )
);

[Nogueira1306]

Hey!! If you have multiple registers you should use ForAll function

https://www.spguides.com/powerapps-forall-function/

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,
Gonçalo Nogueira

Check my LinkedIn!

Check my User Group (pt-PT)!

Last Post on Community

My website!

[RandyHayes]

@AaronW1 

Consider changing your formula to the following:

Patch(ResourceTimeTracking,
    ForAll(Filter(yourGalleryName.AllItems, Value(txtGlyTimeEntryBulkHours.Text)>0),
        {primaryKeyName: primaryKeyName,  //change to your PK name or exclude if you are trying to create new records
         TaskMilestoneID: controlWithTaskMilesStoneID.Text, // replace as needed
         TeamMemberID: <same as above>,
         CalDate: <same as above>,
         WorkHours: <same as above>,
         dCreated: <same as above>,
         CreatedBy: <same as above>
        }
    )
);

If you are trying to create records, then exclude the primary key.  If you are trying to update, then change to the primary key column name.

Each column being patched back will get its value from the control in the Gallery.  Provide those control names and properties accordingly.

I hope this is helpful for you.

[AaronW1]

Thank you for taking the time to review. I've tried ForAll with Patch as well and the same problem persists, I should've mentioned this in the original post but I'm using Patch with ShowColumns to create multiple records since in my experience it is faster than ForAll. When it works, it does create multiple records in the data table as expected.

[RandyHayes]

@AaronW1 

It will depend on how you are using the ForAll...if you are using as intended as a function that creates a Table, then it should all be very fast.  If you try to use it as a ForLoop, then it will be very slow.  This is a very common mistake for many!

Since I was guessing at a lot of the parts of the formula I provided and you have tried to take what I provided and make it fit to your app...can you share the formula that you ended up with?  That will help me see the issues better.

And, you also mentioned that the same problem persists...are you able to duplicate the problem consistently and is there a pattern? (like only 10 out of 20, etc)

[AaronW1]

@RandyHayes 

Thanks for the reply, just getting back to this. Below is the new code I'm using and it seems to be working more consistently although it is slow for some users. I can see in the data table that it's taking about one second to create each record, any ideas on making this more efficient? This is creating new records in ResourceTimeTracking.

ForAll(
Filter(
glyTimeEntryBulk.AllItems,
Value(txtGlyTimeEntryBulkHours.Text) > 0
),
Patch(
ResourceTimeTracking,
{
TaskMilestoneID: Value(glyTimeEntryBulkTaskMilestoneID.Text),
TeamMemberID: Value(glyTimeEntryBulkTeamMbrID.Text),
CalDate: dtGlyTimeEntryBulkCalDate.SelectedDate,
WorkHours: Value(txtGlyTimeEntryBulkHours.Text),
dCreated: Now(),
CreatedBy: varUserID
}
)
)

[RandyHayes · Accepted solution]

@AaronW1 

Yes, you will *always* have performance issues with ForAll when you use it backwards!  You are trying to use it like a ForLoop in some development language - which PowerApps is not.  It is a function that returns a table of records based on your iteration table and record schema.

It is more efficient to use the function as intended and will provide better performance.  In your formula, Patch needs to instantiate for each iteration of a row in your source.  When used as a ForAll as intended, it will create a table to pass to the Patch statement which will instantiate itself once and use the table as its record source to create or update (all depending on the Primary Key provided).  

Regardless of how you use or misuse the ForAll, it will STILL do what it was designed to do - create a table!  So in your formula, you are not using the resulting table, so the effort and time it took to create the table is wasted.  However, it still did it and slowed down your app.  And in the case of your formula, the table would have been a table with a single column called Value that would have contained records from each patch statement result.  So again...all performance hungry and then wasted.

So, your formula should be:

Patch(ResourceTimeTracking,
    ForAll(
        Filter(glyTimeEntryBulk.AllItems, Value(txtGlyTimeEntryBulkHours.Text) > 0),
        {
          TaskMilestoneID: Value(glyTimeEntryBulkTaskMilestoneID.Text),
          TeamMemberID: Value(glyTimeEntryBulkTeamMbrID.Text),
          CalDate: dtGlyTimeEntryBulkCalDate.SelectedDate,
          WorkHours: Value(txtGlyTimeEntryBulkHours.Text),
          dCreated: Now(),
          CreatedBy: varUserID
        }
    )
)

[AaronW1]

@RandyHayes 

Thank you for that thorough explanation, I missed that piece from your first reply. Performance is much improved now.

[RandyHayes]

@AaronW1 

Yes...get used to ForAll as a table producer and not a ForLoop.  You will find a multitude of awesome things you can do with it.  And, in regard to data interactions, always go with a "table of things to do", rather than a one-at-a-time action.