Unanswered

Accessing Append to Array Variable properties from SQL Get Rows v2

(0) Share

Report

Posted on by PhoenixPubInc

I'm new at power automate. Im puzzled about accessing property or value from Append to array variable like the "pr_no" using variables('PRNoRows')[value]['pr_no'] but i can't get it done. Please see below the result of the array. I am gladly happy if someone knows any approaces on how to get the properties of the array. Thank you.

{
"name": "PRNoRows",
"value": {
 "@odata.context": "https://asia-001.azure-apim.net/apim/sql/409448f6fa753b8be39fc/$metadata#datasets('default%2Cdefault')/tables('%5Bdbo%5D.%5BvwCt%5D')/items",
 "value": [
 {
 "@odata.etag": "",
 "ItemInternalId": "906fe260-c67a-49fb-a32e-f6facf75c907",
 "id": 8259,
 "control_number": "P-CR003920",
 "cr_date": "2021-12-13T00:00:00Z",
 "company_id": 1,
 "code_count": 3920,
 "cr_no": "CR# 03320",
 "pr_no": "25342",
 "pr_date": "12/10/2021"
 
 }
 ]
}

Categories:

Using flows

I have the same question (0)

All responses (10)

Answers (1)

Expiscornovus 33,189 Most Valuable Professional on at

Like (1)

Report
Hi @PhoenixPubInc,

It looks like you have two value properties in your array. If that is correct, the second value property also needs to be in the expression.

Because it is an array you need to specify which item you want to retrieve. In this case you have only one item, so an index [0] would be sufficient.

With more items a Select items might be more useful.

Try an expression like below to get the pr_no value for the sample you shared in your post:

variables('PRNoRows')['value']['value'][0]['pr_no']

Was this reply helpful? Yes No
PhoenixPubInc 28 on at

Like (0)

Report

Hi @Expiscornovus,

I'm still getting an error using your solution.

Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language expression 'variables('PRNoRows')['value']['value'][0]['pr_no']' cannot be evaluated because property 'value' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

Here's is the screenshot of the flow

Was this reply helpful? Yes No
Verified answer

Expiscornovus 33,189 Most Valuable Professional on at

Like (1)

Report
Hi @PhoenixPubInc,

Apologies, I now see in your screenshot it is a nested array. I missed that part 😁

In this case you need two index numbers. Try something like below:

variables('PRNoRows')[0]['value'][0]['pr_no']

Was this reply helpful? Yes No
PhoenixPubInc 28 on at

Like (1)

Report

Hi @Expiscornovus,

It worked! Awesome, thank you for this. Been banging my head for this 😁.

Was this reply helpful? Yes No
PhoenixPubInc 28 on at

Like (0)

Report

Question, @Expiscornovus how come it's nested?

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (1)

Report

Hi @PhoenixPubInc,

No problem. Nested arrays are always fun 😁

Btw, the sample I shared earlier only retrieves the pr_no of the first item of your array. In case you want to get the pr_no of all your array items you could use a Select like below.

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (0)

Report

Hi @PhoenixPubInc,

It might be nested because of the configuration in the append to array variable.

Can you show/share a screenshot which fields you are using from the SQL Get Rows (v2) in the Select an output from previous steps field in the Condition action and in the Value field of your append array variable action?

Was this reply helpful? Yes No
PhoenixPubInc 28 on at

Like (0)

Report
Hi @Expiscornovus

Here's the screenshot of my flow from array variable to sql get rows v2 and append to array variable.

Below is the SQL Get Rows v2 Outputs

{ "statusCode": 200, "headers": { "Pragma": "no-cache", "Transfer-Encoding": "chunked", "Vary": "Accept-Encoding", "x-ms-request-id": "d6477811-19cb-425a-a07c-8e43962a07c3", "Strict-Transport-Security": "max-age=31536000; includeSubDomains", "X-Content-Type-Options": "nosniff", "X-Frame-Options": "DENY", "x-ms-connection-gateway-object-id": "07807ecc-61c7-436a-869c-cfcf161f434f", "Timing-Allow-Origin": "*", "x-ms-apihub-cached-response": "false", "x-ms-apihub-obo": "true", "Cache-Control": "no-store, no-cache", "Date": "Tue, 12 Jul 2022 06:09:55 GMT", "Content-Type": "application/json; charset=utf-8; odata.metadata=minimal", "Expires": "-1", "Content-Length": "1374" }, "body": { "@odata.context": "https://asia-001.azure-apim.net/apim/sql/409448f6testtest/$metadata#datasets('default%2Cdefault')/tables('%5Bdbo%5D.%5BvwCRW%5D')/items", "value": [ { "@odata.etag": "", "ItemInternalId": "906fe260-c67a-49fb-a32e-f6facf75c907", "id": 8259, "control_number": "P-CR003920", "cr_date": "2021-12-13T00:00:00Z", "company_id": 1, "code_count": 3920, "cr_no": "CR# 03320", "pr_no": "25342", "pr_date": "12/10/2021", "with_tin": "", "customer_name": "RELIGIOUS OF THE VIRGIN MARY (RVM)" } ] } }

I'm thinking if my flow is redundant for the data, since I have get rows and also make to append the rows to an array variable. I just want to get the "pr_no" and "customer_name". I'm not sure if I can directly access the action SQL Get Rows to get the column I want and put it on my email send action?

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (0)

Report

Hi @PhoenixPubInc,

I see you used the body field in the append to array variable action. I think that is the reason why it is nested. You could use the current item property instead.

And yes, you could also use a Select directly after the Get Rows. Technically it would not be needed to append it to an array variable.

Try something like below.

Double check if the expression with the item() is still valid. You might need to update this.

Was this reply helpful? Yes No
PhoenixPubInc 28 on at

Like (0)

Report

Hi @Expiscornovus

I successfully set an action Select after the Get Rows but I can't seem to extract the values. I'm using body('Select')['Pr No.'] but it wont work. I also tried body('Select')[0] but won't do.

Below is the screenshot for Select body

Was this reply helpful? Yes No