ForAll() PowerApps - Issue

[Sham07]

Hello Everyone, 

I am having trouble with ForAll statement.

I am building a time entries application.

When the employee edits the timesheet, if the record already exists, the system should update the record as per the changes made by the employee; if the record did not exist, the system should add the record. This is the code i am using:

ForAll(
galleryTimeEdit.AllItems,
If(IsBlank('Timesheet ID'),
Patch(
[@'Timesheet_Entries_T'],
Defaults([@'Timesheet_Entries_T']),
{
'Employee Name': User().FullName,
Email: User().Email,
WeekEnding: DateTimeValue(DateToLbl_1.Text),

'Level 1 Approval Status': "Pending",
Monday: Value(MonIn_1.Text),
Tuesday: Value(TueIn_1.Text),
Wednesday: Value(WedIn_1.Text),
Thursday: Value(ThuIn_1.Text),
Friday: Value(FriIn_1.Text),
Saturday: Value(SatIn_1.Text),
Sunday: Value(SunIn_1.Text),
'Project Name': ProjectCB.Selected.'Project Name',
}
),
Patch(
[@'Timesheet_Entries_T'],
{'Timesheet ID':'Timesheet ID'},
{

'Employee Name': User().FullName,
Email: User().Email,
WeekEnding: DateTimeValue(DateToLbl_1.Text),

'Level 1 Approval Status': "Pending",
Monday: Value(MonIn_1.Text),
Tuesday: Value(TueIn_1.Text),
Wednesday: Value(WedIn_1.Text),
Thursday: Value(ThuIn_1.Text),
Friday: Value(FriIn_1.Text),
Saturday: Value(SatIn_1.Text),
Sunday: Value(SunIn_1.Text),
'Project Name': ProjectCB.Selected.'Project Name',

}

)));

The result i am gettting is duplication of records. Looks like the code is just patching as new records than updating with existing record.

Can you please help?

[EddieE]

@Sham07 

Does galleryTimeEdit have a field (label) showing or hidden for Timesheet ID? When using a gallery as a reference you must include the fields you need in the gallery - they cannot just be part of the data source sitting behind the gallery.

Another thing you could try is disambiguation using 'As', for example

ForAll(
   galleryTimeEdit.AllItems As _myPatch,
   If(IsBlank('Timesheet ID'),
   Patch(
   [@'Timesheet_Entries_T'],
   Defaults([@'Timesheet_Entries_T']),
   {
      'Employee Name': User().FullName,
      Email: User().Email,
      WeekEnding: DateTimeValue(_myPatch.DateToLbl_1.Text),
      'Level 1 Approval Status': "Pending",
      Monday: Value(_myPatch.MonIn_1.Text),
... and so on

[poweractivate]

@Sham07 

I see where the issue may be occurring in your PowerApps formula.

The ForAll function in PowerApps is intended to be used to evaluate a formula for all records of a table.

It is not the same as the 'For' loop in traditional coding.

In this scenario, the ForAll function should not be used as a loop to Patch data to your data source but should be used when you want to perform operations on all items in a collection or a table and you want to return a table as result.

The reason you're probably seeing duplicates is that the Patch function is executed for every record in the 'galleryTimeEdit.AllItems'. This means that for every record, it's either creating a new entry (when 'Timesheet ID' is blank) or updating an existing entry (when 'Timesheet ID' exists). However, the condition to update an existing record is not accurately captured in your current formula.

Specifically, If(IsBlank('Timesheet ID') doesn't really make any sense and I don't think you are accurately capturing this condition. Besides this, that whole condition should be moved into a two argument version of patch, and perform the If only on the primary key, to avoid repetition of the entire thing. Patch inside ForAll is also a bad idea, Patch should be outside the ForAll.

Here is an improved version of your formula:

Patch(
   [@'Timesheet_Entries_T'],
   ForAll(
      galleryTimeEdit.AllItems,
      {
        'Timesheet ID': If(IsBlank(ThisRecord.'Timesheet ID'), Blank(), ThisRecord.'Timesheet ID'),
        'Employee Name': User().FullName,
        Email: User().Email,
        WeekEnding: DateTimeValue(DateToLbl_1.Text),
        'Level 1 Approval Status': "Pending",
        Monday: Value(MonIn_1.Text),
        Tuesday: Value(TueIn_1.Text),
        Wednesday: Value(WedIn_1.Text),
        Thursday: Value(ThuIn_1.Text),
        Friday: Value(FriIn_1.Text),
        Saturday: Value(SatIn_1.Text),
        Sunday: Value(SunIn_1.Text),
        'Project Name': ProjectCB.Selected.'Project Name'
      }
   )
)

Replace 'Timesheet ID' above with the actual name of the column that is your primary key of that Timesheet Entries table of your data source. 

Make sure the setting "Formula-level error management" is turned on in your Power Apps (for fairly recent Power Apps this should be on by default) or else it may not know when to create the new values using Blank() for signaling to patch to create a new one instead of modify an existing one, as patching blank value is not allowed with that setting off.

Actually, in the above, If(IsBlank(ThisRecord.'Timesheet ID'), Blank(), ThisRecord.'Timesheet ID') doesn't make that much sense, as that condition should never be true, no blank primary key should be possible for record of your gallery.

So just replace this line with just ThisRecord.'Timesheet ID'. ThisRecord is the current record from the ForAll, is what ThisRecord means.  Then even if  "Formula-level error management" is turned off it may not matter then.

See if it helps @Sham07 

[Sham07]

Hello, I tried your solution but the same error:

Timesheet ID is a primary column in Timesheet entries table. I am not able to pull into the gallery. So created an auto generated number in the timesheet entries table " AutoTest".

This is my code now:

ForAll(
galleryTimeEdit.AllItems As _myPatch,
If(IsBlank(_myPatch.AutoTest),
Patch(
[@'Timesheet_Entries_T'],
Defaults([@'Timesheet_Entries_T']),
{

'Employee Name': User().FullName,
Email: User().Email,
WeekEnding: DateTimeValue(DateToLbl_1.Text),
Monday: Value(_myPatch.MonIn_1.Text),
Tuesday: Value(_myPatch.TueIn_1.Text),
Wednesday: Value(_myPatch.WedIn_1.Text),
Thursday: Value(_myPatch.ThuIn_1.Text),
Friday: Value(_myPatch.FriIn_1.Text),
Saturday: Value(_myPatch.SatIn_1.Text),
Sunday: Value(_myPatch.SunIn_1.Text),
'Project Name': _myPatch.ProjectCB_1.Selected.'Project Name',
}
),
Patch(
[@'Timesheet_Entries_T'],
{AutoTest:_myPatch.AutoTest},
{
'Employee Name': User().FullName,
Email: User().Email,

WeekEnding: DateTimeValue(DateToLbl_1.Text),
Monday: Value(_myPatch.MonIn_1.Text),
Tuesday: Value(_myPatch.TueIn_1.Text),
Wednesday: Value(_myPatch.WedIn_1.Text),
Thursday: Value(_myPatch.ThuIn_1.Text),
Friday: Value(_myPatch.FriIn_1.Text),
Saturday: Value(_myPatch.SatIn_1.Text),
Sunday: Value(_myPatch.SunIn_1.Text),
'Project Name': _myPatch.ProjectCB_1.Selected.'Project Name',
}

)));

[poweractivate]

@Sham07 For optimization purposes Power Apps is not guaranteed to return all rows in a Gallery and loads them in iteratively as you scroll. So Gallery.AllItems may not return all your records.
You can use a Collection however note that whether your function is delegable or not, a single function call e.g. ClearCollect can only return at most the record limit (500 by default, raise-able to max of 2,000).

A non-delegable function on your innermost Filter for example will be inaccurate on a large data set say 10,000 because it will process the first 2,000 only and stop there. However, even a delegable Filter (or any other delegable function of any kind), while more accurate here, while it may process beyond the 2,000th record on the data source side, will still return only the 1st 2,000 processed records, stopping on whichever is the 2,000th filtered one. Keep this in mind.

No escape is possible from the record limit. Design so it returns less than record row limit. 
Remember it's record limit per function call.

It's possible to use tricks like batching to go beyond this limit, but it's not recommend, as large collection sizes may be bad for performance, and except in a few select cases, may be a sign of not using Power Apps correctly. 

[poweractivate]

@Sham07 What's your error?
What's being duplicated exactly?

Again, using ForAll outside of Patch increases the likelihood of you getting that error exponentially, and it is also bad for performance and ForAll is not a for development loop.

[Sham07]

I am getting duplicate rows. 

This is a timesheet entry application. So, at most an employee will be retrieving not more than 25 lines of data for a particular week. So, delegation should not be an issue.

Here is the code now as per your suggestion:

Patch(
[@'Timesheet_Entries_T'],
ForAll(
galleryTimeEdit.AllItems,
{
'Timesheet ID': If(IsBlank(ThisRecord.'Timesheet ID'), Blank(), ThisRecord.'Timesheet ID'),

'Employee Name': User().FullName,
Email: User().Email,
'Position Title': PositionTtle_txt_1.Text,
WeekEnding: DateTimeValue(DateToLbl_1.Text),

'Approval Status': "Submitted for Approval",
'Level 1 Approval Status': "Pending",

Monday: Value(MonIn_1.Text),
Tuesday: Value(TueIn_1.Text),
Wednesday: Value(WedIn_1.Text),
Thursday: Value(ThuIn_1.Text),
Friday: Value(FriIn_1.Text),
Saturday: Value(SatIn_1.Text),
Sunday: Value(SunIn_1.Text),
Notes: NotesIn_1.Text,

'Project Name': ProjectCB_1.Selected.'Project Name',

}
));

[poweractivate]

@Sham07 

'Timesheet ID': If(IsBlank(ThisRecord.'Timesheet ID'), Blank(), ThisRecord.'Timesheet ID'), should be just ThisRecord.'Timesheet ID' because no timesheet id should be blank.
See below. If you get an error with the below, you have a blank timesheet ids on some records in the Gallery - which is odd and you may have to double check why that is. You may need to use ShowColumns on the Gallery or somewhere to show the id in the Gallery side like in this recent similar issue: Row id hidden from data table, how to use it for form 

Patch(
   [@'Timesheet_Entries_T'],
   ForAll(
      galleryTimeEdit.AllItems,
      {
         'Timesheet ID':ThisRecord.'Timesheet ID',
         'Employee Name': User().FullName,
Email: User().Email,
         'Position Title': PositionTtle_txt_1.Text,
WeekEnding: DateTimeValue(DateToLbl_1.Text),

         'Approval Status': "Submitted for Approval",
'Level 1 Approval Status': "Pending",

         Monday: Value(MonIn_1.Text),
         Tuesday: Value(TueIn_1.Text),
         Wednesday: Value(WedIn_1.Text),
         Thursday: Value(ThuIn_1.Text),
         Friday: Value(FriIn_1.Text),
         Saturday: Value(SatIn_1.Text),
         Sunday: Value(SunIn_1.Text),
         Notes: NotesIn_1.Text,

         'Project Name': ProjectCB_1.Selected.'Project Name',

      }
    )
);

[Sham07]

The blank timesheet IDs are when the employee is creating a new line item in the application and is not appended in the table yet. 

[Sham07]

As far as i am progress is the same: New rows are created rather than updating the records if it is already present in the table.