cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jlarques
Frequent Visitor

Add new row with new number in Excel table

Dear community,

 

I have an Excel Table with all my invoices in the NFACTURA column and others columns with additional information about the invoice(Amount, disccount, taxes, Grand Total, detail, etc.). This information comes to the SharePoint item list and it seems that the flow works, because a new row appears with all the data.

 

The problem is that I want to add 1 to the NFACTURA column to increase my invoice number with this format: New number/Actual year. For instance, if my last invoice has the number 12 in the column NFACTURA, the new invoice will be 13 and the new invoice number will be 13/2022.

 

In the NFACTURA column appears all the array values when the flow passes through the column, as you can see in the image below, and I don't know how to get the last invoice value, add 1 and convert the invoice number with the format number/year.

 

Do you have any idea to fix this issue?

 

Thanks everybody for your help.

 

 

 

José Luis Error array.pngError invoice number.png

1 ACCEPTED SOLUTION

Accepted Solutions

No worries ! 😁 ...Please read my second post in this topic:

gabibalaban_0-1643716088049.png

 

View solution in original post

18 REPLIES 18
gabibalaban
Dual Super User
Dual Super User

@jlarques ,

Can you share the formula you used to populate NFACTURA column ? Also a screenshot with the flow will be helpful.

Thx

Hi @gabibalaban ,

 

for sure!

next steps create an invoice in Word and convert it to PDF format.

Thanks for your help.

 

First part Flow.pngSecond part Flow.png

 

@jlarques ,

I think you over complicate the flow a little bit.

First of all, if is supported, you need a separate column in Excel to keep the year for each invoice. Then the flow will be as follow:

1.Get the biggest invoice number record for current year. (please notice the Odata Query to filter the Excel table by year, the sort Odata Query to order the invoices desc and a top count property set to 1 because we need only the biggest invoice number):

gabibalaban_2-1643654091621.png

2. Increment by 1 the biggest invoice number and keep it in a variable:

gabibalaban_3-1643654363447.png

3. Add a new row to Excel table among other values:

gabibalaban_5-1643654813656.png

 

Of course, if you want you can remove the step 2 and put the formula in NFACTURA property in step 3 using add dynamic content -> expression.

Hope it helps !

 

Mister_Shaik
Super User
Super User

Hi @jlarques ,

 

Instead of using multiple actions to get the new NFACTURA number value, you can use the below expression directly in the NFACTURA field in the "Add a row into a table" action,

 

add(int(last(outputs('List_rows_present_in_a_table')?['body/value'])?['NFACTURA']),1)

 

What this basically does is that it'll fetch the last row NFACTURA value, convert it to int and then increment it's value by 1.

 

Hope this helps 🙂

 

Kind Regards,

Shaik Sha
________________________________________________________________________

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

Hi @Mister_Shaik ,

 

thanks for your help. I'm starting now in Power Automate and I don't have any knowledge of this tool. Every help from the community will be appreciated.

 

I change as you told me but I have an error message:

Unable to process template language expressions in action 'Add_a_row_into_a_table' inputs at line '0' and column '0': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'

 

Thanks again for your help.

 

Hi @gabibalaban,

 

let me try tomorrow morning and I'll tell you if my flow it's working or not.

 

Thanks again.

Hi @gabibalaban,

 

In the Filter Query I wrote Year eq 'formatDateTime(utcNow())' but I have an error:

Syntax error at position 37 in 'Year eq'2022-02-01T07:02:23.2652000Z''.
inner exception: Syntax error at position 37 in 'Year eq'2022-02-01T07:02:23.2652000Z''.

 

I understand that the expression takes all the date including hours. Is there any way to takes only  year?

 

Thanks for your help.

Hi @gabibalaban,

 

I changed the formula to formatDateTime(utcNow('yyyy')) but I have another error:

Unable to process template language expressions in action 'List_rows_present_in_a_table' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '2022' was not valid. The datetime string must match ISO 8601 format.'.

 

Thanks,

 

gabibalaban
Dual Super User
Dual Super User

You need to write the formula (only formatDateTime ()) using add dynamic content -> expression 

or 

write in down somewhere else in the following format: @{formatDateTime(utcNow(),'yyyy')}​

and then copy - paste on its place.

Hi wrote the formula formatDateTime() on the expression field, as you can see in the image below, but another error appears, telling me that the formula expects a timestamp as a first parameter.

 

Thanks again.

 

Formula formatDateTime.png

😊 ...This is the formula: formatDateTime(utcNow(),'yyyy') , I didn't wrote the entire formula. 

I tried before your answer, but another error appears. Maybe you can help me with this:

No function signature for the function with name 'year' matches the specified arguments. The function signatures considered are: year(Edm.DateTimeOffset); year(Edm.DateTimeOffset Nullable=true); year(Edm.Date); year(Edm.Date Nullable=true).
inner exception: No function signature for the function with name 'year' matches the specified arguments. The function signatures considered are: year(Edm.DateTimeOffset); year(Edm.DateTimeOffset Nullable=true); year(Edm.Date); year(Edm.Date Nullable=true).

 

Thanks,

I think in my formula is 'yyyy' not 'year'.

I did it, as you can see in the image below, but year is string and the formula seems to need ISO 8601 format. How can I convert a string value in datetime value? 

Unable to process template language expressions in action 'List_rows_present_in_a_table' inputs at line '0' and column '0': 'In function 'formatDateTime', the value provided for date time string '2022' was not valid. The datetime string must match ISO 8601 format.'

 

Formula formatDateTime.png

Thanks 

Please take a good look at my formula !

It is:

formatDateTime( utcNow() , 'yyyy' )

 

You're right!

Excuse me for wasting your time in many messages. Now the system tells me that the column year doesn't exist, and it's true. I don't have any year column in my Excel Spreadsheet. Which column or formula do I have to write to compare it with the current year and apply the filter?

No worries ! 😁 ...Please read my second post in this topic:

gabibalaban_0-1643716088049.png

 


It works perfect! Now I only have to join the invoice number with  year, as formatted number/year, but I prefer to try it myself to see if I can get it.

Thank you very much for your invaluable help and to all those who have sent me messages to help me.

Helpful resources

Announcements
 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (1,382)