Hello,
(Had this in the Connecting to Data forum, but figured this was a more appropriate forum). I'm trying to use PowerApps to pass parameters to Flow for a SQL server stored procedure execution. However, I'm running into an issue with how Flow is passing the parameters (best guess at this point honestly, but only thing I can come up with). Here's what I know:
Below are some snapshots of my button.select and Flow details (varFilter* values are boolean variables within PowerApps that are based on checkboxes, but only get updated when the filtering selections are complete and 'applied' within the app menu):
Note: "FlowName" is generic and replaced in this example. Thanks in advance!
FlowName.Run( If(varFilterAsOf,Text(dtpHomeAsOfDate.SelectedDate,ShortDate),Text(Today(),ShortDate)), If(varFilterWorkerStatus,drpHomeWorkerStatus.Selected.Value,"~"), If(varFilterWorkerType,drpHomeWorkerType.Selected.Value,"~"), If(varFilterEmployeeType,drpHomeEmployeeType.Selected.Value,"~"), If(varFilterContWorkerType,drpHomeContWorkerType.Selected.Value,"~"), If(varFilterSOALevel && "0" in lstHomeSOALevel.SelectedItems.Value,"0","~"), If(varFilterSOALevel && "1" in lstHomeSOALevel.SelectedItems.Value,"1","~"), If(varFilterSOALevel && "2" in lstHomeSOALevel.SelectedItems.Value,"2","~"), If(varFilterSOALevel && "3" in lstHomeSOALevel.SelectedItems.Value,"3","~"), If(varFilterSOALevel && "4" in lstHomeSOALevel.SelectedItems.Value,"4","~"), If(varFilterSOALevel && "5" in lstHomeSOALevel.SelectedItems.Value,"5","~"), If(varFilterSOALevel && "6" in lstHomeSOALevel.SelectedItems.Value,"6","~"), If(varFilterSOALevel && "7" in lstHomeSOALevel.SelectedItems.Value,"7","~"), If(varFilterSOALevel && "8" in lstHomeSOALevel.SelectedItems.Value,"8","~"), If(varFilterEmployeeEnum,txtHomeEmployeeENum.Text,"~"), If(varFilterSupervisorEnum,txtHomeSupervisorENum.Text,"~"), If(varFilterTermDtFrom,Text(dtpHomeTermDtFrom.SelectedDate,ShortDate),"~"), If(varFilterTermDtTo,Text(dtpHomeTermDtTo.SelectedDate,ShortDate),"~"), If(varFilterOrgSearchTerms,txtHomeOrgSearchTerms.Text,"~"), If(varFilterJobSearchTerms,txtHomeJobSearchTerms.Text,"~"), User().Email )
Flow (all fields were populated with "Ask in PowerApps"):
Example SP execution call (that works):
EXEC [dbo].[SPNAME] '4/17/2018', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~', '~'
This produces the same results as the above (I did the "~" characters due to the potential for null values to cause issues...the SP logic takes care of the dummy characters):
EXEC [dbo].[SPNAME] '4/17/2018', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', ''
Proof of non-issues during independent SP call:
Flow Execution results:
Failure Summary:
Error code screenshot:
Error code text:
{ "status": 400, "message": "Microsoft SQL: Incorrect syntax near 'WHERE'.\r\n inner exception: Microsoft SQL: Incorrect syntax near 'WHERE'.\r\nclientRequestId: 76dd8643-d248-42c5-8093-19637b9267da", "source": "sqlconnectionprovider-westeurope.am2-ase-001.p.azurewebsites.net" }
Hi @mrb783
Hi
I just stumbled on your request. You've been waiting a while for a reply.
Were you able to solve it? Can you post the solution so that we can all learn from it?
If you still need help, please let me know what changed.
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
Cheers
Manuel