cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
opticshrew2
Level: Powered On

Duplicate Entry Submissions Azure SQL Database

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

1 ACCEPTED SOLUTION

Accepted Solutions
opticshrew2
Level: Powered On

Re: Duplicate Entry Submissions Azure SQL Database

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

View solution in original post

5 REPLIES 5
opticshrew2
Level: Powered On

Re: Duplicate Entry Submissions Azure SQL Database

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

View solution in original post

Super User
Super User

Re: Duplicate Entry Submissions Azure SQL Database

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
Level: Powered On

Re: Duplicate Entry Submissions Azure SQL Database

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

Super User
Super User

Re: Duplicate Entry Submissions Azure SQL Database

Thanks @opticshrew2! That's really useful.

opticshrew2
Level: Powered On

Re: Duplicate Entry Submissions Azure SQL Database

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

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 267 members 4,573 guests
Please welcome our newest community members: