Duplicate Entry Submissions Azure SQL Database

[opticshrew2]

Hey Folks,

I have a PowerApp using an Azure SQL Database as the data source. I have a patch statement that input records into a table in the database.

ClearCollect(
        NewRecord,
        Patch(
            '[dbo].[TABLENAME]',
            Defaults('[dbo].[TABLENAME]'),
            {
                Column1: TextInput.Text,
                Column2: Dropdown.Selected.ID,
                Column3: Dropdown.Selected.ID,
                Column4: DatePicker.SelectedDate,
                Column5: DatePicker.SelectedDate,
                Column6: Dropdown.Selected.ID,
                Column7: TextInput.Text,
                Column8: TextInput.Text,
                Column9: TextInput.Text
            }
        );

The reason I've included "ClearCollect" is so I can retrieve the database generated ID. Once this is submitted two records are created in the table. (e.g I create the record, the database shows a record with ID 24 and ID 25. ClearCollect returns ID 25).

When I go to edit the record I run the following patch statement:

Patch(
            '[dbo].[TABLENAME]',
            {ID: SelectedRecord.ID},
            {
                Column1: TextInput.Text,
                Column2: Dropdown.Selected.ID,
                Column3: Dropdown.Selected.ID,
                Column4: DatePicker.SelectedDate,
                Column5: DatePicker.SelectedDate,
                Column6: Dropdown.Selected.ID,
                Column7: TextInput.Text,
                Column8: TextInput.Text,
                Column9: TextInput.Text
            }
        );

There are other bits in the full statement, I'll post them below. From the looks of things I don't think it's anything to do with the query but maybe the connector between PowerApps and Azure SQL. Below is my full code, I've tried to anonimise it. 

If(
    Status = "NEW",
    ClearCollect(
        NewRecord,
        Patch(
            '[dbo].[TABLENAME]',
            Defaults('[dbo].[TABLENAME]'),
            {
                Column1: TextInput.Text,
                Column2: Dropdown.Selected.ID,
                Column3: Dropdown.Selected.ID,
                Column4: DatePicker.SelectedDate,
                Column5: DatePicker.SelectedDate,
                Column6: Dropdown.Selected.ID,
                Column7: TextInput.Text,
                Column8: TextInput.Text,
                Column9: TextInput.Text
            }
        )
    );
    Set(
        SelectedRecord,
        First(NewRecord)
    );
    Patch(
        '[dbo].[UPDATES]',
        Defaults('[dbo].[UPDATES]'),
        {
            Column1: Value(SelectedRecord.ID),
            Column2: Today(),
            Column3: TextInput.Text,
            Column4: "Comment"
        }
    ),
    If(
        Status = "EXISTING",
        Patch(
            '[dbo].[TABLENAME]',
            {ID: SelectedRecord.ID},
            {
                Column1: TextInput.Text,
                Column2: Dropdown.Selected.ID,
                Column3: Dropdown.Selected.ID,
                Column4: DatePicker.SelectedDate,
                Column5: DatePicker.SelectedDate,
                Column6: Dropdown.Selected.ID,
                Column7: TextInput.Text,
                Column8: TextInput.Text,
                Column9: TextInput.Text
            }
        );
        Patch(
            '[dbo].[UPDATES]',
            Defaults('[dbo].[UPDATES]'),
            {
                Column1: Value(SelectedRecord.ID),
                Column2: Today(),
                Column3: TextInput.Text,
                Column4: "Comment"
            }
        )
    )
);
Set(
    Status,
    "EXISTING"
);
Reset(TextInput.Text);
UpdateContext({SaveVisible: false})

Does anyone have any advice?

J

[opticshrew2 · Accepted solution]

Hi Folks,

Update!

So, I'm no developer but this is what has been describe to me (external to the forums). When a button is selected the OnSelect statement generates an event. The connector is subscribe to that event. If the connector over subscribes essentially the onselect action (event) is triggered as many times as it's been subscribed to. In my case it was twice hence two submissions.

The fix is to re-create the button (essentially identfying a new event).

Hopefully that helps anyone.

J

[timl]

Hi @opticshrew2 

Thanks for the update. This an interesting case because it's something that I've not seen.

When you had this problem, was it occurring all the time or was it just intermittently? Could you share any more about the source of your explanation?

When you say that you recreated the button, I'm assuming this means that you deleted the orginal button from the designer, added a new button and re-added the formula to your new button?

Many thanks

[opticshrew2]

Hi @timl 

The problem first started last week, it got to Friday and I thought, "Let's leave it until Monday". Monday arrived and I still had the same issue.

I spoke with one of our developers who spends quite a bit of time working with the Microsoft platoform. I raised it with them out of curiosity. Turns out this issue happens on the "real" side of development to. Applying the same logic to PowerApps we were able to resolve the issue.

Yes, I create a new button control and added the same code as above. The button experienceing the issues was removed.

Thanks,

J

[timl]

Thanks @opticshrew2! That's really useful.

[opticshrew2]

Hi @timl 

Update!

Although this fix worked for one button it appears to happen often across multiple buttons. These appears to be quite an annoying bug that is likely to cause more harm than good.

Regards,

J