Submit and patch tables

[sanrajbhar]

I am submiting a form and patch two or more table on Button click event. But now its only do the submit part of code and succesffully the a table but patch is failed for another table. I want it should updates all table if no error occurs or otherwise should warn of fails. how can we achive that.  your valuable suggestion would highly appriciated.

for your reference, I am using this code sumbmit 

SubmitForm(frm_service);Patch('[gis].[app_service]',Defaults('[gis].[app_service]'),{appID:ComboBox4_appname.Selected.appID,serviceID:frm_service.LastSubmit.serviceID})

[mr-dang]

Hi @sanrajbhar,

If I understand correctly:

• You have a form that you submit, frm_service
• But in addition to submitting the form, you also perform a Patch()
• Patch() saves information based on the last record submitted by the form

Currently, the button with your formula looks like this:

SubmitForm(frm_service);

Patch('[gis].[app_service]',Defaults('[gis].[app_service]'),
    {appID:ComboBox4_appname.Selected.appID,
        serviceID:frm_service.LastSubmit.serviceID
    }
)

You can find more information on the error of the Patch by using the Errors function:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-errors

In your case, set a data table or gallery to:

Errors('[gis].[app_service]')

and it will show all the errors from the specified table.

My guess is that frm_service.LastSubmit is not showing up properly so the Patch is not going through.

I would set a label to:

frm_service.LastSubmit.serviceID

and see what appears.

Then the next step that you can try is to move the Patch formula to the OnSuccess property of the frm_service form. It might give it more time for the LastSubmit to populate and be successful.

Let me know how it goes.

Mr. Dang

___________________

[sanrajbhar]

Thanks for such wonderful reply. the above formula in my post above works well.  But unfortunately FormSubmit works and patch fails.(I know why this patch fails) So I want it to work like  - Both FrmSumbit and Patch should works.  if any of them fails, it should fails with proper massage warning

[mr-dang]

I think I understand better now:

• You want a way to notify the user if either the SubmitForm or the Patch fails

In this case, I would still use the Errors function. Extract the attached .msapp sample app file from the zip for more information on how to execute it.

1. Insert a toggle. It has a Default property that determines if it is on or off. So we can make it listen for if there are any Errors in saving to your tables:
   

   !IsEmpty(Errors('[gis].[app_service]'))

   This means, "If the errors for your table are NOT empty, switch on, otherwise stay off."
   
   
2. Make the toggle switch a variable to true. The variable will be used to show/hide a dialog to your user.
   

   Toggle.OnCheck:
   
   Set(showErrorMessage,true)

3. Insert a label and type the warning you want to give your users if there is a failure in writing. Make it visible depending on the variable you just made.
   

   Label.Visible:
   showErrorMessage

4. Make the warning label dismiss itself if the user clicks it.
   

   Label.OnSelect: 
   Set(showErrorMessage,false)

For more reading on dialogs, please see these resources:

• https://powerapps.microsoft.com/en-us/blog/creating-dialogs-in-powerapps/
• https://powerusers.microsoft.com/t5/PowerApps-Community-Blog/How-to-build-a-Message-Box/ba-p/30624
• https://www.youtube.com/watch?v=_Dc0wJx9EN0
• https://www.youtube.com/watch?v=3R3BQ5P8X5U

Mr. Dang

_____________________

[sanrajbhar]

Apolgies..! as I am not able to convey my thoughts. sorry for that.

I Just need simple solution - i have a form and two comboxbox1 and combox2. these ComboxBoxes should not blank. User need to prompt to fill it if clicked on sumbit button.So Sumit button should tell user to fill ComboxBoxes if blank or No choice selected. if both of comboxes filled should do Sumbit action.

[mr-dang]

Ah okay, I think I get it now:

• You have two comboboxes
• If either of them are blank, you want the Submit button to warn them
• If they are not blank, go ahead and submit the form and patch

This is an exercise in conditions. There's a few ways to go about this:

1. Disable the button unless both comboboxes are not empty/blank.
2. Show a pop up dialog if a user attempts to submit but either of the boxes are blank.

Which one would you prefer?

[sanrajbhar]

Exactly..! Sorry mr. Dang for troubling you.  I am new to powerapps.

I will prefer showing  "Pop -up" Dialogue. but same keen to know the "Disable Sumbit Button unless both combox populated"

Thank you very much for your time and Help.

[Meneghino]

Hi @sanrajbhar

@mr-dang's suggestion is excellent.  In addition to disabling and in alternative to pop-up you could just consider colouring the border of neede inputs red until filled or something like that.

However, I would like to suggest an alternative approach: leveraging the possibilities of calling stored procedures from PowerApps via Flow.  See here for references:

https://powerapps.microsoft.com/en-us/blog/return-an-array-from-flow-to-powerapps-response-method/

https://baizini-it.com/blog/index.php/2018/05/10/powerapps-concurrently-returning-variables-and-mult...

This solution will be much more robust and can be treated as a transaction by SQL Server, so that if one part fails for some reason then all is rolled back.  Please let me know if you are interested in more details.

[sanrajbhar]

Yes. your apprach seems good for when dealing with SQL server table. would like to know more about it. thanks

[Meneghino]

Scenario: you create a record in TableA with inputs a, b, c then using the Id of the newly created record need to create a record in TableB with inputs d, e, f.

How-to:

1) In SQL Server create a stored procedure with inputs a,b,c,d,e,f that will do the above and return a variable with the status and/or the created record(s)

2) In Flow create a flow that will elicit a,b,c,d,e,f from PowerApps, execute the stored procedure and return the result to PowerApps

3) In PowerApps, connect the flow to your save button using Set(MyResult, MyFlow(a,b,c,d,e,f)) and then use MyResult to give feedback to the user and/or update the data displayed

Please let me know if the above makes sense and which steps (if any) need clarification