cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
omi18
Post Patron
Post Patron

Run Stored Procedure with Flow

Hello,

 

I have stored procedure set up to run from Flow. What I'm trying to to do is Update or Edit the person's details in PowerApps. 

 

Let's say I have 3 text input fields in Powerapps. FName, Lname and Address. Also a button to run the flow associated with stored procedure. Now: if I have a person with Fname, Lname values and empty address field. and I hit the button to run the flow it gives me and error: "The Method run has an invalid value for parameter Address." 

 

And if I fill the address field and hit the button flow will work. 

 

Going back again with the same person, and delete the address and hit the button, flow will run successfully. 

 

On the database side I have setup address field as a null. So, I'm trying to find out why powerapps is giving me an error on the first attempt? 

 

Any suggestions? 

 

Thanks,

Omi

1 ACCEPTED SOLUTION

Accepted Solutions
reedbg
Advocate I
Advocate I

I've run into similar issues with PowerApps and writing nulls to the database in general.  I don't know if it applies to passing parameters to flow.  Try replacing your last parameter with something like
IF(IsBlank(Address.Text),Blank(),Address.Text)

 

If that doesn't work, I believe there's a setting (in advanced settings) you can turn on that could make a difference.  I don't remember for sure what it's called; but, it might be Advanced Settings --> Formula Level Error management.  If that's not it, check the forum for issues with database NULLs in general.

View solution in original post

4 REPLIES 4
reedbg
Advocate I
Advocate I

I've run into similar issues with PowerApps and writing nulls to the database in general.  I don't know if it applies to passing parameters to flow.  Try replacing your last parameter with something like
IF(IsBlank(Address.Text),Blank(),Address.Text)

 

If that doesn't work, I believe there's a setting (in advanced settings) you can turn on that could make a difference.  I don't remember for sure what it's called; but, it might be Advanced Settings --> Formula Level Error management.  If that's not it, check the forum for issues with database NULLs in general.

View solution in original post

Pstork1
Dual Super User III
Dual Super User III

I've seen this issue before.  When you invoke the stored procedure in the flow you used Ask in PowerApps to insert the parameters in the appropriate fields.  That creates three parameters that you pass from Power Apps to the Flow. Even if these can be null in the Stored Procedure they can't be null when you pass them from Power Apps to the flow.  The way I normally get around this is by creating a single JSON object parameter that I pass from Power Apps to the flow.  Then in Flow I parse the JSON and use those dynamic fields to populate the Stored Procedure.  Here's a sample of the run button to encode the JSON object.

UpdateContext(
    {
        params: {
            Customer: Concatenate(
                txtCustomer.Text,
                "%"
            ),
            EndDate: Text(
                dteEndDate.SelectedDate,
                "[$-en-US]yyyy-mm-dd"
            ),
            MaxValue: 50000,
            MinValue: 40000,
            StartDate: Text(
                dteStartDate.SelectedDate,
                "[$-en-US]yyyy-mm-dd"
            )
        }
    }
);
    StoredProcJSON.Run(
        JSON(
            params,
            JSONFormat.IndentFour
        )
    )

And a screenshot of the flow.

image.png



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi @reedbg ,

 

It worked. 

Thanks for the update @omi18.  I'm going to have to setup a few search procedures soon that will have nullable parameters.  I'm glad to know the same pattern will work.

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!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (58,499)