Answered

Error using FORMAT in CREAT CSV TABLE

(0) Share

Report

Posted on by David Brandman

294

I am trying to format a few number fields (only starting with one so far) from JSON data brought in. I PARSE JSON to get the fields to use in the subsequent actions. I then use the FORMAT NUMBER action to get the number into Currency format. I then want to use that formatted field in my output when issuing the CREATE CSV TABLE action. But I get the error below. I am lost on how to do this. Any help would be great. Thanks!

Error:

(x) Flow save failed with code 'invalidTemplate' and message The template validation failed: 'The inputs of template action 'Create_CSV_table' at lire 1 and column '3984' is Invalid. Action 'Apply_to_each' must be a parent 'foreach' scope of actior 'Create_CSV_table' to be referenced by 'repeatltems' cr 'items' functions.'.'.

My flow:

Categories:

General topics

I have the same question (0)

All responses (17)

Answers (1)

Sort by

David Brandman 294 on at

Like (0)

Report
Copy link

Link copied!

Reallyhoping someone has some ideas on this. Still stuck. Thanks!

Was this reply helpful? Yes No
NiloferA on at

Like (0)

Report
Copy link

Link copied!

Hello @dBrand !

I notice that you have added your action of Create CSV Table outside of the Apply to Each action and still trying to refer the "Current item" which is coming from Apply to Each.
Try moving Create CSV Table action within Apply to Each after the Format action and it should work for you.

Hope this helps! Please give a Kudo and mark this Reply as solution if you found it helpful.

Was this reply helpful? Yes No
David Brandman 294 on at

Like (0)

Report
Copy link

Link copied!

I tried this, but now get the error:
BadRequest. The 'from' property value in the 'table' action inputs is of type 'Object'. The value must be of type 'Array'.

Here is the flow:

Additionally, I am trying to get the columns in the CSV to be from the original parsed Json as well as some fields from the Format steps (since they will provide a formatted dollar amount, where the Parsed data does not).

Still stuck on this for days, and can't really find an answer to this, and my deadline is Monday.

Was this reply helpful? Yes No
NiloferA on at

Like (0)

Report
Copy link

Link copied!
Hello @dBrand ,
I got your problem statement working. Please make below changes to your flow to achieve the requirement,
Initialize an Array (MyArray) Variable
Then in Apply to Each action, after Format Number, add a Compose action and add the expression in input as follows
replace(string(item()), item()['ro'] (name of the field you are trying to replace with formatted number), (Output from Format Number Dynamic Content))
Then add Append to Array Variable action and add the following expression in Value
json(outputs('Compose'))
Outside of the Apply to Each action, add the Create CSV Table action and pass the Array to it.
This should solve your issues. Now the problem with your Flow is that
You are traversing JSON Body and passing each item (which is of type Object) to Create a CSV Table action, this action accepts an Array not an Object
You are formatting some field from the JSON but you are never updating the current item thus your updated format will not appear in the final result.
Hope this helps you out and you should be good for your deadline! If you still face any issues, feel free to post a reply and I will be happy to help!
Please give a Kudo and mark this Reply as solution if you found it helpful.

Was this reply helpful? Yes No
David Brandman 294 on at

Like (0)

Report
Copy link

Link copied!
Thanks, sorry, but I'm stuck. In step 2a -
replace(string(item()), item()['ro'] (name of the field you are trying to replace with formatted number), (Output from Format Number Dynamic Content))
I replace it with my data, below, but I am getting an invalid expression. Not sure of the syntax.
replace(string(item()), item()['PayTotalSum'], (outputs('Format_number_Total_Pay')?['body']))

But, I do understand the concept of the solution you provided, which seems to be the answer, if I could just get past the syntax issue I am having.

Thanks!

Was this reply helpful? Yes No
NiloferA on at

Like (0)

Report
Copy link

Link copied!

No problem @dBrand , so your expression should look like this
replace(string(item()),item()?['PayTotalSum'], outputs('Format_number_Total_Pay')?['body'])
Get rid of the brackets near Format Number Output.

Was this reply helpful? Yes No
David Brandman 294 on at

Like (0)

Report
Copy link

Link copied!

Just about there. Now getting past that and getting error creating the CSV table, which I am doing from the array (or so I believe):

Was this reply helpful? Yes No
NiloferA on at

Like (0)

Report
Copy link

Link copied!

Hello @dBrand ,
It looks like you are passing string and not array to the CSV table action. Can you please confirm the type of the variable you have initialized?

Also, if possible please post screenshots of your flow here for me to assess the problem better! 🙂

Was this reply helpful? Yes No
David Brandman 294 on at

Like (0)

Report
Copy link

Link copied!

From what I see, I am passing an Array, which is why I am confused. But below are screenshots to help better see my flow, as requested. Thanks!!!

The Apply to each breakout:

Was this reply helpful? Yes No
NiloferA on at

Like (0)

Report
Copy link

Link copied!

Ok, I got your issue.
In Append to array variable, Please add the value as an expression and not as text in the field.

Was this reply helpful? Yes No