Answered

Selecting Field from Get Rows to Feed to Stored Procedure in Flow

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I have a flow which essentially gets the value of a field from an on-premise database and feeds it as a parameter of an on-premise stored procedure. The get rows block (named Get_NL_TRAN_ID) provides the following output:

After, I am feeding CTL_SEQ_NUMBER to a stored procedure by using: body('Get_NL_TRAN_ID')?['value'] and am getting the following:

It seems I am not referencing the CTL_SEQ_NUMBER field appropriately. How can I do this please?

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

Pstork1 69,026 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
If you want just the CTL_SEQ_NUMBER then I think you want to use the following as your parameter.

body('Get_NL_TRAN_ID')?['value']?['CTL_SEQ_NUMBER']

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

I tried it and get the following error: "InvalidTemplate. Unable to process template language expressions in action 'Insert_in_NL_TRN_TEMP' inputs at line '1' and column '2649': 'The template language expression 'body('Get_NL_TRAN_ID')?['value']?['CTL_SEQ_NUMBER']' cannot be evaluated because property 'CTL_SEQ_NUMBER' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'."

I guess the Get Rows block returns an array and one must filter it before using it?

Was this reply helpful? Yes No
Pstork1 69,026 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
I think then you need to use an index something like this. Would need to see the full Json to be sure I'm using the index in the right place. But its something like this.

body('Get_NL_TRAN_ID')?['value']?[0]?['CTL_SEQ_NUMBER']

If you can provide a JSON payload with more than one row I can provide a more accurate answer

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Worked - thanks! Is there any documentation re this syntax so I can understand better?

Was this reply helpful? Yes No
Pstork1 69,026 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

Nothing I've seen specific to using it in Flow. But it is just regular JSON syntax. Here's a tutorial on JSON.

https://www.tutorialspoint.com/json/json_syntax.htm

Was this reply helpful? Yes No