Patch edits and new records from collection to SQL table

[BPNJMC1]

Hello,

I'm creating a canvas app to record student attendance at our two nursery schools.  The attendance statuses are accumulated in a collection (colAttendanceRoster) and I have a button to save the data all at once to the Azure SQL database.

I tried patching in bulk first, but had problems because colAttendanceRoster has both records to edit and records to add to the SQL table.  (This line works for edits, but errors if there are records to add.)

Patch('bpn.AcademyAttendance',

    ShowColumns(colAttendanceRoster, "ProgramSubTypeKey", "ClassroomKey", "PersonKey", "AttendDate", "AttendStatus"));

So instead, I tried to separate it by creating a collection of records to edit (colPatchEdit) and a collection of records to create (colPatchAdd)... and then do two patch statements.

It seems to work when I play it - both edits and adds are reflected in the Azure SQL table.

However, when I return to the app, there is an error warning on the save icon:

And the line to edit existing records is underlined in red:

With the error "The data returned by the service is invalid."

Does anyone know what is causing this error?   Or if there a more efficient way to do this?

I've pasted the code below.

Thanks,

Jill

// collect records to be edited (if person key exists in colAttendanceTaken)
Clear(colPatchEdit);
Collect(colPatchEdit,
    ForAll(
        Filter(colAttendanceRoster, PersonKey in colAttendanceTaken.PersonKey),     
        {
            ProgramSubTypeKey: ProgramSubTypeKey, 
            ClassroomKey: ClassroomKey,
            PersonKey: PersonKey,
            AttendDate: AttendDate,   
            AttendStatus: AttendStatus                  
       }
    )
);  

// edit existing records
Patch('bpn.AcademyAttendance', colPatchEdit);

// collect records to be added (if person key does not exist in colAttendanceTaken)
Clear(colPatchAdd);
Collect(colPatchAdd,
    ForAll(
        Filter(colAttendanceRoster, !(PersonKey in colAttendanceTaken.PersonKey)),     
        {
            ProgramSubTypeKey: ProgramSubTypeKey, 
            ClassroomKey: ClassroomKey,
            PersonKey: PersonKey,
            AttendDate: AttendDate,   
            AttendStatus: AttendStatus
        }
    )
);  

// create new records
ForAll(colPatchAdd,
    Patch(
        'bpn.AcademyAttendance', 
        Defaults('bpn.AcademyAttendance'),
        {
        ProgramSubTypeKey: ProgramSubTypeKey, 
        ClassroomKey: ClassroomKey,
        PersonKey: PersonKey,
        AttendDate: AttendDate,   
        AttendStatus: AttendStatus
        }
    ) 
);

[RandyHayes · Accepted solution]

@BPNJMC1 

If you are storing your primary key in the collection for edit records and a blank for the primary key for the new records, then you only need one collection.

Also, your ForAll's are backward in that you are trying to use them as a For/loop rather than as a Table function which they are.  These will perform poorly in your app.

I cannot tell from your formulas what your primary key is, so I will assume for the purposes of this response to be ID.

Please consider changing you Formula to the following:

Patch('bpn.AcademyAttendance', colAttendanceRposter)

Again, if the column names are identical to the datasource and the primary key is blank for new records and a valid number for existing, then this is all you need.

 I hope this is helpful for you.

[BPNJMC1]

Hi @RandyHayes,

colAttendanceRoster has a few extra columns that are not saved to the SQL table.  So I had originally tried:

Patch('bpn.AcademyAttendance', 
    ShowColumns(colAttendanceRoster, 
        "ProgramSubTypeKey", 
        "ClassroomKey", 
        "PersonKey", 
        "AttendDate", 
        "AttendStatus"
    )
);

If(!IsEmpty(Errors('bpn.AcademyAttendance')),
    Notify("Save error." & Concat(Errors('bpn.AcademyAttendance'), Column&": "&Message), 
        NotificationType.Error, 5000),
    Notify("Successfully Saved.", 
        NotificationType.Success, 2000);
);

However, it was only saving the edits to the table and was not adding the new records.

(and it showed no error notification to the user)

The primary key is composite:

Could the fact that the primary key is composite be causing the problem?  It sounds like I need to create an Identity Column (GUID) for the primary key and change the current composite primary key to be a unique index instead.

Jill

[RandyHayes]

@BPNJMC1 

To be honest, I am not sure how it will respond to a composite key.  I would suggest a autoincrementing primary key.

Then, if your primary ID key is blank, it will create the records, and if it is supplied, then it will update them.

[BPNJMC1]

@RandyHayes ,

Changing the primary key (so that it was no longer composite) worked - thank you very much for your help!

J