Solved: Re: Calling Stored Procedure From a Button

KarenF · ‎08-10-2022

Hi There

Hopefully someone can help me as i've not done this before and I am struggling to resolve.

I have a form in an app and users can make changes and submit the form which then writes back to the SQL DB. What I need to do is once the write back has compelted I then want to run a stored procedure which then does some actions to the row they have submitted to.

Is there a way in one button of saying, ok write back to the db and then execute the stored procedure. I have created a flow for this (not duer if that works either 🙂

Any help would be really appreciated

Thanks Karen

iAm_ManCat · ‎08-10-2022

Yes sounds like the sk_key value is not being read - what does your flow run say for the value passed to it?

Might be able to do a LookUp before to ensure the sk_key is pulled by doing something like this? That way you can also check the LastSubmitSK variable's value in the editor to see if it is pulling it correctly

Set(LastSubmitSK, LookUp(DatasourceName, ID = YourFormName.LastSubmit.ID).sk_key);
'ExecuteSQLstoredprocedureonPowerAppsbuttonclick-2'.Run(LastSubmitSK);

@iAm_ManCat

My blog

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

View solution in original post

iAm_ManCat · ‎08-11-2022

That's ok, sorry for assuming! 💜

I'll explain below but I'll post this link to the learning resources for all levels that I recommend anyone at any level take a look at:

Microsoft Power Platform: Learning Resources | Microsoft Power Apps

So, the flow runs:

In your maker portal (https://make.powerapps.com/) you will see some links along the left rail, one of these is for flows, click that to see all your flows you currently have access to (they are separated by environment, so if you have more than one environment you will need to select it from the Environment in the top-right-hand corner of the screen)

Once in the flows, click on the name of your Power Apps Triggered flow (or tick it and hit the ellipsis [three dots] and choose view details)

Now you are looking at the detail view for your flow - you can see along the bottom it has a number of flow runs, you can select any of these to view the exact steps taken during that particular run and what errors it had:

You can click on any of the actions to see what was passed into it as input and what its output was, as well as any errors - the errors will also get a pane on the right-hand side of the screen so that you can easily debug issues with any run:

As I said above, I do highly recommend going through the learning resources link I shared, and if possible also taking a look at https://learn.microsoft.com as there are many free courses on there that can help you learn flow basic and that will make your journey through this tech much smoother 🙂

@iAm_ManCat

My blog

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

View solution in original post

iAm_ManCat · ‎08-10-2022

Use the OnSuccess property of the form - this will only run when a successful submission has occurred - you can run your flow from in there, it must use Power Apps as the trigger for the flow or else it won't show up in Power Apps.

Cheers,

@iAm_ManCat

My blog

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

KarenF · ‎08-10-2022

Hi there

Thanks for getting back to me. Brilliant idea, however I cant get it to work, maybe something in my flow. I should be able to add the below to the OnSuccess but its underlining it all in red and doesnt recognise the 'ThisItem' Oh well back to the drawing board unless you can help me again 🙂

'ExecuteSQLstoredprocedureonPowerAppsbuttonclick-2'.Run(ThisItem.sk_key);

KarenF · ‎08-10-2022

These are the steps I have put in, im wondering if I am missing one

iAm_ManCat · ‎08-10-2022

Ok yeah, not a problem, there's no ThisItem in this context, you need to use FormName.LastSubmit

'ExecuteSQLstoredprocedureonPowerAppsbuttonclick-2'.Run(FormName.LastSubmit.sk_key);

@iAm_ManCat

My blog

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

KarenF · ‎08-10-2022

Thanks again 🙂

Unfortunately its not working, in terms of the table isnt being updated. Wonder if its a timing issue or it just cant find that new sk key in the new row. Once again thank you

iAm_ManCat · ‎08-10-2022

Yes sounds like the sk_key value is not being read - what does your flow run say for the value passed to it?

Might be able to do a LookUp before to ensure the sk_key is pulled by doing something like this? That way you can also check the LastSubmitSK variable's value in the editor to see if it is pulling it correctly

Set(LastSubmitSK, LookUp(DatasourceName, ID = YourFormName.LastSubmit.ID).sk_key);
'ExecuteSQLstoredprocedureonPowerAppsbuttonclick-2'.Run(LastSubmitSK);

@iAm_ManCat

My blog

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP

KarenF · ‎08-11-2022

HI there

Thanks again for getting back to me. Just added your gsuggestion but unfortunately no luck. I received an email late yesterday saying all my attempts to run the flow failed

You asked me this 'what does your flow run say for the value passed to it' Not sure i understand what you mean. Sorry. Creating flows is very new to me

Thanks

Karen

KarenF · ‎08-11-2022

Could it be a licensing issue? When I go to power automate site it says my license has expired and to select continue with free trial. Waiting for work to sort me a per user license.

iAm_ManCat · ‎08-11-2022

ohhhh! Yes the SQL connector is a premium connector, so its possible that the issue might be licensing - was there any indication of errors in the flow runs?

@iAm_ManCat

My blog

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!

Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP