Issue with Editable Grid within gallery - Duplicated records and copy not working

[Curioler]

Hi All,

My first post. Generally, I search for the issue and I find some questions already posted and answered. We have a great community!

However, this time I am really stuck. Here's my problem -

I created editable grid using the techniques from the following video of Reza.

https://youtu.be/wI6SHGQ9ATg 

It worked amazing and all the stakeholders were happy. Until some time back, it started malfunctioning. First it repeated all records 5-6 times in the gallery for which I just used not blank of ids to get the original record. But later on the copy option didn't work for me. Even if we make the changes, the original copy would be saved and if I again edit it, it would work well. Any idea, what could be going wrong?

My Data Source is SharePoint Lists!

Here's the screenshot of the editable grid.

My Load Button On Select is 

ClearCollect(
    colGridData1,
    Filter(
        FactAssignment,
        (IsBlank(CmbStudy.SelectedItems) || IsEmpty(CmbStudy.SelectedItems) || StudyNo.Value = CmbStudy.Selected.Result) && (IsBlank(CmbEmp.SelectedItems) || IsEmpty(CmbEmp.SelectedItems) || Employee.Value = CmbEmp.Selected.Result) && (IsBlank(CmbProject.SelectedItems) || IsEmpty(CmbProject.SelectedItems) || BAYNo.Value = CmbProject.Selected.Result) && (StartsWith(
            ProjectDesc,
            SearchBox.Text
        ) || StartsWith(
            FunctionRoles.Value,
            SearchBox.Text
        )) && (If(
            Not(varCWID = "XXXXX"),
            Function.Value = varSubgroup,
            true
        ))
    )
);

My Items for the gallery is as following:

Sort(
    Filter(
        colGridData1,
        Not(IsBlank(ID))
    ),
    ID,
    Descending
)

My Gallery On Select is 

Patch(
    colGridData1,
    ThisItem,
    {
        Employee: Employee.Selected,
        Level: Level.Selected,
        StudyNo: CmbStudyNo.Selected,
        BAYNo: CmbBAYNo.Selected,
        ProjectDesc: LookUp(
            FactStudyList,
            Title = ThisItem.StudyNo.Value,
            Project_Description
        ),
        FunctionRoles: FunctionRole.Selected,
        Year: {Value: Year(Today())},
        Q1: Value(Q1.Text),
        Q2: Value(Q2.Text),
        Q3: Value(Q3.Text),
        Q4: Value(Q4.Text),
        'N Q1': Value(NQ1.Text),
        'N Q2': Value(NQ2.Text),
        'N Q3': Value(NQ3.Text),
        'N Q4': Value(NQ4.Text)
    }
)

And finally the Save Records button On Select is 

If(
    varGridEdit,
    If(
        !IsBlank(
            LookUp(
                colGridData1,
                IsBlank(Employee) || IsEmpty(Employee) || IsBlank(Level) || IsEmpty(Level) || IsBlank(FunctionRoles) || IsEmpty(FunctionRoles)
            )
        ),
        Notify(
            "Please fill required fields",
            NotificationType.Error,
            2000
        ),
        Remove(
            FactAssignment,
            colDelete
        );
        Patch(
            FactAssignment,
            RemoveIf(
                colGridData1,
                ID = Blank()
            );
            UpdateIf(
                colGridData1,
                Created = Blank(),
                {ID: Blank()}
            );
        );
        Patch(
            FactRE,
            colGridData2
        )
    );
    Notify(
        "Changes saved!",
        NotificationType.Success,
        2000
    );
    Clear(colDelete);
    Select(btnLoadData);
    Set(
        varGridEdit,
        !varGridEdit
    );
    ,
    Set(
        varGridEdit,
        !varGridEdit
    );
    
);

Oh wait, Copy icon On Select

Set(
    varNumber,
    varNumber + 1
);
Collect(
    colGridData1,
    Patch(
        varNewRecord,
        {
            ID: varNumber,
            Employee: ThisItem.Employee,
            Level: ThisItem.Level,
            ProjectDesc: ThisItem.ProjectDesc,
            StudyNo: ThisItem.StudyNo,
            BAYNo: ThisItem.BAYNo,
            Function: ThisItem.Function,
            FunctionRoles: ThisItem.FunctionRoles,
            Year: ThisItem.Year,
            Q1: ThisItem.Q1,
            Q2: ThisItem.Q2,
            Q3: ThisItem.Q3,
            Q4: ThisItem.Q4,
            'N Q1': ThisItem.'N Q1',
            'N Q2': ThisItem.'N Q2',
            'N Q3': ThisItem.'N Q3',
            'N Q4': ThisItem.'N Q4',
            Created: Blank()
        }
    )
)

Delete icon On Select is

Collect(
    colDelete,
    ThisItem
);
Remove(
    colGridData1,
    ThisItem
);

And my App On Start is 

Set(
    varGridEdit,
    false
);

Set(
    varNewRecord,
    Defaults(FactAssignment)
);

Set(
    varNumber,
    10000000
);

Set(varCWID,Office365Users.MyProfile().MailNickname);

Set(varSubgroup,LookUp(FactEmployee, CWID=Office365Users.MyProfile().MailNickname, Subgroup).Value);

My Page On Visible is 

Set(
    varGridEdit,
    false
);
Select(btnLoadData);
Reset(CmbStudy);
Reset(CmbProject);
Reset(CmbEmp);
Reset(SearchBox);

Even this might be helpful,

The editable grid in view mode

BAY and Project are the same.

I want to solve this mystery. Not able to sleep well at night. 

Thank you all in advance! 

[Support-PP]

Does your SharePoint list have duplicates. When you do a copy are you also saving the record as soon as the copy is done?

[Curioler]

My SharePoint list doesn't have duplicates. But whenever I load my gallery it automatically adds duplicates in my gallery and these duplicates do not have ids so I could filter them out in Items. This filter isn't really necessary but PowerApps somehow mysteriously adds duplicate records and to deal with it, I had to use this workaround. This was part one of the problem.

Sort(
    Filter(
        colGridData1,
        Not(IsBlank(ID))
    ),
    ID,
    Descending
)

Now, the second is when I do the copy, it adds the copy in a collection called colGridData1 and finally when I save them, they are saved using a patch function to SharePoint list. The idea of the copy button is to copy the record, make changes in the copied record and then save it. But in my case, suddenly after a few days it stopped saving the changes to the copied record and just creates a copy of the record.

Hope this helps. Thank you!

[erf01]

I am having the exact same problem based on using the same tutorial. I have narrowed it down to a problem with the sort function. If I remove sort from "Items" I don't get bogus duplicates. You can see this bug (?) in the power apps monitor function--it performs a sort operation 20 times per record, which makes the reload times atrocious for large datasets. I still have no idea how to fix and don't want to bandaid over it by clearing rows without IDs so for now I only sort based on the "under edit" boolean.

[Curioler]

Thanks for sharing. So, finally, it is a bug and not something that I did wrong. I will for now continue to bandaid with removing duplicates as I need the records sorted in the grid. Kindly share if you find a fix or any other way of doing the same.

[Curioler]

@erf01 Thanks for sharing. So, finally, it is a bug and not something that I did wrong. I will for now continue to bandaid with removing duplicates as I need the records sorted in the grid. Kindly share if you find a fix or any other way of doing the same.

[Nat_SGRE · Accepted solution]

Hi all,

I had the same issue and I tried putting the Patch in the OnChange property of the individual controls of the Gallery instead of the OnSelect.

Here's the link to solved issue, hope it is useful

Solved: Re: Duplicate record getting created on the GRID i... - Power Platform Community (microsoft....