Is there a better way to do this data formatting

Wappler General Need Help
1

I am working on an application that needs to plug into an accounting system. I accountancy for a business in my country, each company has a different tax or vat year end, so in my case this company has their year end on the last day of September.

With this in mind, my application is writing a csv file that needs to be imported into their accounting program, and when the invoice is processed in July I need to show that July which is the 7th month is actually the accountancy period of 10.

I have done this with a data formatter but im pretty sure there must me a better way.

{{serverconnect_invoice_main.data.query_invoice_main[0].inv_date.formatDate("MMMM").replace('January','4').replace('February','5').replace('March','6').replace('April','7').replace('May','8').replace('June','9').replace('July','10').replace('August','11').replace('September','12').replace('October','1').replace('November','2').replace('December','3')}}
2

can you sent sample csv

3

Here is the contents of the CSV file, you will novice in the Header row, the 6th field has a number 6, thats the financial period relative to the seventh field which is the month of March which is actually the 3rd month of the gregorian calendar. This is the CSV file required for Pastel Accounting.

“Header”,“P9678”," “,“Y”,“OA0007”,6,“13/03/2018”,“PROVULO_OASIS_9678”,“N”,0,“BANK: ABSA”,“ACC NR:0000000000”,“BRANCH: 000000”,“OASIS WATER - GROBLERSDAL “,“21 Barlow st “,“Groblersdal “,” - 0470”,“013 262 4420”,””,0,“13/03/2018”,“013 262 4420”,“013 262 4420”,””,1,”",""," "
“Detail”,0,2,912.83,1040.63,“Item”,1,3,0,“APPLE20L”,“Apple 100% 20 LT (1+6)”,4,"",“001”
“Detail”,0,6,38.52,43.91,“Item”,1,3,0,“F1LTORA”,“Orange 100% 1 LT (1+5)”,4,"",“001”
“Detail”,0,6,32.30,36.82,“Item”,1,3,0,“F1LTPA”,“Tropical Punch 100% 1 LT (1+4)”,4,"",“001”
“Detail”,0,12,23.32,26.58,“Item”,1,3,0,“F1LTSDB”,“Sports Drink 1L (1+4) Berry Flavoured”,4,"",“001”
“Detail”,0,6,37.38,42.61,“Item”,1,3,0,“F1LTSTRA”,“Strawberry 100% 1 LT (1+4)”,4,"",“001”
“Detail”,0,2,698.00,795.72,“ITEM”,1,3,0,“F20LCRAN”,“Cranberry 100% 20lt (1+5)”,4,"",“001”
“Detail”,0,1,664.77,757.84,“Item”,1,3,0,“F20LGRENA”,“Grenadilla 100% 20 LT (1+3)”,4,"",“001”
“Detail”,0,1,478.23,545.18,“Item”,1,3,0,“F20LGUHA”,“Guava 100% 20 LT (1+3)”,4,"",“001”
“Detail”,0,1,648.31,739.07,“Item”,1,3,0,“F20LLIA”,“Litchi 100% 20 LT (1+4)”,4,"",“001”
“Detail”,0,2,549.84,626.82,“Item”,1,3,0,“F20LMAA”,“Mango 100% 20 LT (1+3)”,4,"",“001”
“Detail”,0,2,681.89,777.35,“Item”,1,3,0,“F20LORA”,“Orange 100% 20 LT (1+5)”,4,"",“001”
“Detail”,0,2,560.34,638.79,“Item”,1,3,0,“F20LPIALA”,“Pineapple/Carrot 100% 20 LT (1+3)”,4,"",“001”
“Detail”,0,2,597.82,681.51,“Item”,1,3,0,“F20LTPA”,“Tropical Punch 100% 20 LT (1+4)”,4,"",“001”
“Detail”,0,2,399.00,454.86,“20lt”,1,3,0,“F20LTSDB”,“Sports Drink 20L (1+4) Berry”,4,"",“001”
“Detail”,0,2,929.73,1059.89,“Item”,1,3,0,“GRAPE20L”,“Grape 100% 20 LT (1+5)”,4,"",“001”
“Detail”,0,5,344.64,392.89,“48 B”,1,3,0,“OZONE”,“Ozone Energy Drink”,4,"",“001”

4

please try data formatter

Screenshot_1
Screenshot_1.png988Ă—370 14.4 KB

Screenshot_2

You can convert your date field like this before insert / export…

If I understood true

1 Like
5

Ok that might help. Thanks never thaught of doing it that way. Going to play with it a little and see if I can set a conditional step in there of if now less than 10 then add 3 else subtract 7. Might work better. Thanks