How to get single value from stored procedure in PowerApps

[VinayakS]

Hi All, I am executing sp from power apps using flow as below

ClearCollect(varName,SpName.Run(Param1,Param2));  // this sp returns the count integer single value..

i want to display this count on page, when i set the varName to either gallary or Datatable then value display as 'true' instead actual integer count single value.  for same if i use SQL views without param i can see the integer value to display

is anything wrong i am doing here in sp? what is the proper way to get value from sp? do i need any type conversion

Please help me experts ... 

attached below all details please have look 

Regards,

Vinayak

[v-xida-msft · Accepted solution]

Hi @VinayakS ,

Could you please share a screenshot about your flow's configuration?

Which action do you use to return data back to your canvas app in your flow? "Respond to a PowerApps or flow" action or "Response" action?

Does the flow run successfully and return a proper number value?

If you just want to return a integer value from your flow back to your canvas app, I think the "Respond to a PowerApps or flow" action is enough to achieve your needs. On your side, you should configure your flow's configuration as below:

Within the "Respond to a PowerApp or flow" action, specify expression to retrieve returned value from above "Execute stored procedure (V2)" action:

length(body('Execute_stored_procedure_(V2)').ResultSets.Table1)

After that, within your canvas app, you need to execute the following formula to get the returned Integer value from your flow:

Set(varName,SpName.Run(Param1,Param2).CountNum)  // you need to replace the CountNum with actual property name specified in your 'Respond to a PowerApps or flow' action

then you could use the varName within your Gallery.

Please check and see if the following blog would help in your scenario:

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

More details about expressions (Workflow definition Language expression) supported in Power Automate flow, please refer to the following article:

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference

Best regards,

[VinayakS]

Hi Kris Dai

thank, Much appreciated......✌️😀 i will try given solution by you and let you know the result ... 

as requested, i am sharing my flow screen shot below 

below the overview screen1 .... 

screen 2 below - i am using 'Response' action instead 'Respond to PowerApp or flow' . i think that is the cause of issue.

 

let me try your solution.. will update you..

Thanks again for your response

Regards, VinayakS

[VinayakS]

Hi Kris Dai,

I tried solution provided by you. In flow i have used 'Respond to a PowerApps or flow' action now my flow is working correctly i am getting expected output.  but when i tried to get value within canvas app i am getting incompatible type error issue. i tried to resolve but no luck.

It would be much appreciated if you check and provide a solution.

i have attached all screenshot for your reference.

Thanks in advance

Regards,

VinayakS

[taczi]

I have a similar problem. I just use a GetRows module instead of executing a Stored Procedure.

The result of the module "Respond to a PowerApp or flow" looks like this:

{
  "varid": [
    {
      "IDNumber": 2
    }
  ]
}

 After executing

Set(varID,myflow.Run(Param1).varid) 

in my canvas app, the global variable varID shows the error "ConstraintViolated". How can I make this work and just get the number into the variable varID?

[taczi]

The solution for me was to read the sql results into a collection and then use

Set(varIDNumber,First(mycollection).IDNumber)

to create a global variable containing only the integer.