Patching multiple records in a table, using a dropdown within a gallery

[Anonymous]

Hi there,

I am having difficulties patching my table using a gallery dropdown. My aim here is for people to be able to submit their audit incomplete and it is stored into the table like the example below. I the have a patch function where they can go back to their audit and complete it, updating each question "Pass_Fail" which is located within a gallery in image 2. However using the code below is only updating the first record it finds in the table with the call reference lookup, instead of patching every one of them.

ForAll(QuestionGal_edit.AllItems, Patch(Audit_answers, LookUp(Audit_answers, call_reference = reference_edit.Text), {call_reference: reference_edit.Text, call_type: Calltype_dropdown_edit.Selected.'Call Type', date_of_audit: Audit_Date_edit.SelectedDate, BU: BU_dropdown_edit.Selected.Value, advisor: Advisor_dropdown_edit.Selected.Advisor_name, Question: 'Question-text_edit'.Text, Pass_Fail: Pass_dropdown_edit.Selected.Value, Feedback: Feedback_dropdown_edit.Selected.Value, bulk_audit: Bulk_1.Text, Satisfaction: Satisfaction_1.Selected.Value}))

Does anyone have any advice/ideas on how to fix this?

[Anonymous]

Hi, see a PPT attached, I hope this helps

[poweractivate]

@Anonymous 

The first approach I said actually where I said to add the save button inside the Gallery as a control and patch only the one record when it is pressed, is probably the best, as patching every single record that has that call reference even when nothing has changed is inefficient due to the high number of records and possibly error prone.

// When the save button for a specific question is clicked 
// So this is OnSelect for a button INSIDE the Gallery for editing Answers

    Patch(
        Audit_Answers,
        {
            ID: ThisItem.ID
            Pass_Fail: Pass_dropdown_edit.Selected.Value,
            Feedback: Feedback_dropdown_edit.Selected.Value,
            // Include other fields to update as needed
        }
    )

However if you really want to do this, we can just keep the way you have it and Patch all the records even if they haven't changed:

// Filter reference items for the specific reference number
ClearCollect(
    colRef,
    Filter(Audit_answers, call_reference = reference_edit.Text)
);

// Ensure that we have a one-to-one match between questions and answers
If(
    CountRows(colRef) = CountRows(QuestionGal_edit.AllItems),
    
    // Collect changes for each item in the gallery
    ClearCollect(
        colChanges,
        ForAll(
            colRef, // Go through the filtered reference items
            {
                // Collect the changes based on the corresponding question
                call_reference: reference_edit.Text,
                call_type: Calltype_dropdown_edit.Selected.'Call Type',
                date_of_audit: Audit_Date_edit.SelectedDate,
                BU: BU_dropdown_edit.Selected.Value,
                advisor: Advisor_dropdown_edit.Selected.Advisor_name,
                Question: 'Question-text_edit'.Text,
                Pass_Fail: Pass_dropdown_edit.Selected.Value,
                Feedback: Feedback_dropdown_edit.Selected.Value,
                bulk_audit: Bulk_1.Text,
                Satisfaction: Satisfaction_1.Selected.Value
            }
        )
    );
    
    // Patch the changes to the "Audit_Answers" data source
    Patch(Audit_Answers, colRef, colChanges),
    
    // Handle case where there is not a one-to-one match
    Notify("Number of questions and answers do not match!")
);

See if the above helps and works for you @Anonymous 

[Anonymous]

Yes that is me issue, the person marking the questionairre will not want to have to submit every question individually within the gallery, as some questionairres have 90 questions. So I need one button outside of the gallery, to patch the audit_answers table with all of the information on the left, as well as all the items within the gallery

[Anonymous]

Neither are working for me, unsure where it going wrong. the first bit of code I cannot use as stated with needing one button to complete the patch, the second is saying that the if statement has some issues.

[poweractivate]

@Anonymous Let's try a better way that doesn't require the button inside the Gallery but still is somewhat optimal as if it were:

OnStart of App put this formula:

ClearCollect(colChanges, {});
Clear(colChanges);

Then click Run OnStart once in Editor:

OnChange Property:

Whenever a change is made to any dropdown or field you want to track, you can trigger a hidden button inside the gallery to handle the logic.

Hidden Button Inside the Gallery

First, put a hidden button somewhere inside the Gallery (it should appear in each row) called HiddenButton1. Then set its Visible property to False, make it really tiny, and move it off to the side so we don't see it.

Next, use this formula inside its OnSelect property:

With(
    {
        wRecord: {
            ID: ThisItem.ID,
            Pass_Fail: Pass_dropdown_edit.Selected.Value,
            Feedback: Feedback_dropdown_edit.Selected.Value
            // Include other fields to update as needed
        }
    },
    If(
        IsBlank(LookUp(colChanges, ID = ThisItem.ID)),
        Collect(colChanges, wRecord),
        Patch(colChanges, wRecord)
    )
)

OnChange of Dropdown

Then, from the OnChange property of the dropdown control, you'll trigger the hidden button using the Select function:

Select(HiddenButton1)

This should run the formula inside the hidden button's OnSelect property for each change made in the dropdown, thus either collecting a new change or updating an existing one.

You may also use this same formula

Select(HiddenButton1)

on any other controls' appropriate properties inside that same Gallery that you also want to trigger pending changes on the record for in the collection.

Save Button OnSelect Property

When the Save button outside the Gallery is clicked, you can use the Patch function to update the records based on the collected changes:

Generate Base Records: Using ClearCollect to create colBaseRecs based on colChanges. Then Patch Changes: Patching the changes using colBaseRecs and colChanges. Then Clear the Collection: Clearing colChanges to reset it for the next set of updates.

All this below, belongs in the button outside the Gallery - in the OnSelect property

ClearCollect(
    colBaseRecs,
    ForAll(
        colChanges,
        LookUp(Audit_Answers, ID = ID)
    )
);
Patch(Audit_Answers, colBaseRecs, colChanges);
Clear(colChanges);

This sequence should make sure that the base records are matched with the changes, and the updates are applied correctly to the Audit_Answers data source.

See if this way helps you @Anonymous.

It should make it almost as optimal as if there was an individual save button on each row in the Gallery that was explicitly clicked.

Yet it should be convenient to only require pressing the button outside the Gallery instead, and should ultimately only Patch only the stuff that actually has any changes, and uses Patch outside of ForAll as well on top of that.

See if it works for you @Anonymous 

[Anonymous]

Everything seems to be working apart from the clear collect within the save button

[Anonymous]

I am now not getting this error, however the collection colbaserecs is appearing blank with no data inside

[poweractivate]

@Anonymous 

It's possible the ID thinks it is referring to itself in that LookUp as both the ForAll and the LookUp have a ThisRecord scope. By default it may be using the innermost one ID from the LookUp itself instead of using the ID from the ForAll and from the colChanges ForAll's current individual record, so then ID = ID may not be correct.

In case that may be the issue try this version with disambiguation as per below to explicitly specify the one from ForAll:

ClearCollect(
    colBaseRecs,
    ForAll(
        colChanges,
        With(
            { wID: ID },
            LookUp(Audit_Answers, ID = wID[@ID])
        )
    )
);

See if this may help @Anonymous 

[Anonymous]

Hi, this solution also does not like if I change the dropdown answer, it causes an error on the hidden button. if i accidentally click pass then go to fail, it messes up the button

[Anonymous]

This is the error I am also facing