cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CNT
Advocate V
Advocate V

Problem with SubmitForm on AzureSQL

Hello,

 

I am having problems with submitForm() on Azure SQLdb. The code looks like this...

SubmitForm(frm1);
Set(varID1,Text(frm1.LastSubmit.ID));
SubmitForm(frm2);
Set(varID2,Text(frm2.LastSubmit.ID));
SubmitForm(frm3);
Set(varID3,Text(frm3.LastSubmit.ID));
SubmitForm(frm4);

 

Each submit is adding a record to a different table. All the tables are related. The PK of the 1st is the FK in the 2nd and so on.

varID1, varID2 & varID3 are Global Variables whose values are set as Default to TextBoxes for the next form that is to be submitted (they are the FK for the next table).

The problem occurs only with SubmitForm(frm3) and it does not happen always. Mostly the first time.

It comes up with an error message saying, "some required values are missing or not valid" BUT that is not true as all the values are there and they are valid. As I mentioned earlier, the same thing works maybe the 2nd  or 3rd time but not the first time. Why? 

 

Any help would be much appreciated.

 

Thanks

10 REPLIES 10
Mr-Dang-MSFT
Power Apps
Power Apps

If I understand correctly,

  • You're trying to submit multiple forms, one after another. 
  • Each submission saves the primary key to a variable

 

Can you clarify: is the PK of the first form immediately used in the second form?

Yes u r right

  • You're trying to submit multiple forms, one after another. - YES
  • Each submission saves the primary key to a variable - YES

Can you clarify: is the PK of the first form immediately used in the second form? - YES  This PK is the FK for the Next Table(Submit).

 

What I discovered is that the TextField which has the default value set to this variable is not getting updated before the SubmitForm. The code looks like...

Set(varID,Text(form1.LastSubmit.ID));

SubmitForm(form2)

 

SubmitForm gets executed a fraction of a second before the varID sets the value to the FK TextBox and hence the submit fails. And strange enough it does not happen always. But very often and very unpredictable.

 

Thanks in advance for u'r help.

Yes, given that one form is dependent on a label that's determined right before it, I'd use a different pattern here.

 

Each form has an OnSuccess property. I would place the actions you want to follow in there. And instead of using a variable for the FK, I'd make a direct reference to form1.LastSubmit.ID so it's a more 'live' value.

I tried putting it in the OnSuccess() but without much success. The same unpredictable results.

You had said, to...

make a direct reference to form1.LastSubmit.ID

How do I do that along with SubmitForm() ?

Go to the data card in form2 that will contain the foreign key to form1.

 

Change its Update property to form1.LastSubmit.ID. Now when Form2 is submitted, it will pick up the last submitted id in form1.

 

The Patch function can also be very useful here. It can combine many fields into one record. Even though you're using a form, you can still use Patch very easily. @ThatAPIGuy can share more details here.

 

Here's an example of using Patch with a Form control:

Patch(Table,
    Defaults(Table),
    {foreignkey: form1.LastSubmit.ID},
    form2.Updates
)

This means, "Write a new record to the Table where the foreign key is equal to the primary key determined in form1, and include the entries from form2 for the rest of the fields.

 

Note that in the red section, you can include as many fields as you would like. They all get smashed together into one new blank record (Defaults(Table)).

Hi

 

Setting the Update property to form1.LastSubmit.ID seems to have solved Problem1. Thanks.

 

But there is another problem. When I Submit 4 Forms one after the other, sometimes (very unpredictable) Form4 gets saved but with null values (when I use FormSubmit()) and empty values (when I use patch()).

 

Any hint why it is doing that?

v-xida-msft
Community Support
Community Support

Hi @CNT ,

Do you mean that sometimes the Form4 would save empty/blank values into your SQL Table?

 

I have made a test on my side, and don't have the issue that you mentioned. I agree with @Mr-Dang-MSFT 's thought almost.

Please check if you have filled the Form4 properly as your other 3 forms before you click "Submit" button. Please consider take a try with the following workaround:

Setting the Update property of the Data card which contains FK TextBox within the Form2 to following:

form1.LastSubmit.ID 

Setting the Update property of the Data card which contains FK TextBox within the Form3 to following:

form2.LastSubmit.ID 

Setting the Update property of the Data card which contains FK TextBox within the Form4 to following:

form3.LastSubmit.ID 

 

Set the OnSelect property of the "Submit" button to following:

SubmitForm(frm1);
SubmitForm(frm2);
SubmitForm(frm3);
Patch(
'YourForthSQLTable',
Defaults('YourForthSQLTable'),
frm4.Updates
)

In addition, if the issue still exists, please consider take a try to re-create a new app based on your SQL Tables, then take a try with above solution I provided, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-xida-msft 

 

I have done exactly what u have said. It definitely solves problem 1. i.e the FK - PK problem. All the forms have records inserted. But only form4 has a problem. The record gets inserted with the FK that's coming from form3 BUT sometimes the other fields (text input, toggle and drop down) get null inserted. It happens so randomly that I am not able to find when exactly it happens. It looks like something goes wrong with the form. I don't know what. It's not updating the value I had typed.

Is there any way I could walk through the code during execution?

Are there any debug tools in PowerApps?

Is there any way I could see what's happening with these INSERTS in AzureSQL?

 

Thanks

v-xida-msft
Community Support
Community Support

Hi @CNT ,

I could not reproduce your issue on my side. Please modify your formula as below:

Set the OnSelect property of the "Submit" button to following:

SubmitForm(frm1);
SubmitForm(frm2);
SubmitForm(frm3);
Refresh('YourForthSQLTable'); Patch( 'YourForthSQLTable', Defaults('YourForthSQLTable'), frm4.Updates )

Or

Set the OnSelect property of the "Submit" button to following:

SubmitForm(frm1);
SubmitForm(frm2);
SubmitForm(frm3);

Set the OnSuccess property of the Form3 to following:

Refresh('YourForthSQLTable');
Patch(
      'YourForthSQLTable',
       Defaults('YourForthSQLTable'),
       frm4.Updates
)

Or

Refresh('YourForthSQLTable');
SubmitForm(frm4)

In addition, currently there is no way to walk through the code during execution, and there is no debug tools supported within PowerApps.

If you would like to get further help in this issue, please consider submit an assisted support ticket through the following link:

https://powerapps.microsoft.com/en-us/support/pro

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

Top Solution Authors
Top Kudoed Authors
Users online (50,293)