cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
opticshrew2
Helper II
Helper II

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
Helper II
Helper II

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
Helper II
Helper II

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

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

 

 

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

Thanks @opticshrew2! That's really useful.

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (70,138)