Change date format in json string

Questions & Answers
, ,
1

  • Hi all. I have a scenario that converts a txt file to json string, and should send it in to my accounting software through api. I got valuable help here from Samliew, and was able to make it right, but in the end i get fault code when sending, due to wrong date format. The date in the string is YYYYMMDD, and the api expects YYYY-MM-DD.

  • I have tried date parser and format date, and somehow got it right with the parser, except the time is also in the dateline when this is done. I didn`t get as far as testing if this was okay, because some of the lines lacks date, and the parser gives an error that the date " is wrong.

  • I have searched the forum and tried various versions of parser, ifempty and so on, but i struggle with the fact that the field is empty from time to time. This gives me a variable i am not able to handle by myself.

  • When i ran the senario today it gave me fault 400, and from what i can see there was date in all the required fields then, only with time added, so i need to remove time from date too

  • Link: Convert to json from txt - #17 by Mathias_Kringelen

  • module configurations and outputs : Blueprint and screen below.

  • Error message : I can`t really get any real error message, since i have not been able to find out where they come. My api provider tested the script and got the date error. Screenshot from make below.
    Unfortunately they only support the system they deliver, so formatting before sending to them is my problem :slight_smile:

image
image814Ă—194 21.4 KB

image
image1736Ă—418 49 KB

image
image738Ă—1488 73.3 KB

Google drive is only for fetching the .txt file when testing.

Blueprint :

blueprint.json (52.6 KB)

(Disregard the modules not connected. I will use them later when api accepts my request.

txt input :
´´´[
{
“value”: “@IMPORT_METHOD(1)\r\n\r\n@Actor (=CustNo,SupNo,Nm,Ad1,Ad2,Ad3,PNo,PArea,Phone,Fax,MailAd,BsNo,BGiro,Ctry,Lang,Cur,CPmtTrm,SPmtTrm)\r\n"10429";"0";"Amundsen Geir";"Haukenes 88";"";"";"8450";"Stokmarknes";"91130417";"";"geir.amundsen58@gmail.com";"969155621";"";"47";"47";"";"15";"15"\r\n"10464";"0";"Viggo Thomassen";"Bl�heiveien 105";"";"";"8415";"Sortland";"";"";"";"";"";"47";"47";"";"15";"15"\r\n\r\n@IMPORT_METHOD(3)\r\n\r\n@WaBnd (ValDt, SrcTp, Descr)\r\n"20240410" "12" "Quick3 Import"\r\n@WaVo (VoNo,VoDt,ValDt,VoTp,Txt,DbAcNo,DbTrnCl,DbTxCd,CrAcNo,CrTrnCl,CrTxCd,Am,InvoNo,DueDt,VatAm,R1,R2,R7,CID,Txt2,AGRef)\r\n"100497";"20240410";"20240410";"11";"Utg.faktura";"10429";"0";"0";"0";"0";"0";"48129.00";"100497";"20240420";"";"";"";"";"001042901004979";"";"";;\r\n"100497";"20240410";"20240410";"11";"Utg.faktura";"0";"0";"0";"3000";"0";"3";"21093.75";"";"";"";"1";"";"";"";"";"";;\r\n"100497";"20240410";"20240410";"11";"Utg.faktura";"0";"0";"0";"3001";"0";"3";"27035.25";"";"";"";"1";"";"";"";"";"";;\r\n"100498";"20240410";"20240410";"11";"Utg.faktura";"10464";"0";"0";"0";"0";"0";"396.00";"100498";"20240420";"";"";"";"";"001046401004983";"";"";;\r\n"100498";"20240410";"20240410";"11";"Utg.faktura";"7740";"0";"0";"0";"0";"0";"0.25";"";"";"";"";"";"";"";"";"";;\r\n"100498";"20240410";"20240410";"11";"Utg.faktura";"0";"0";"0";"3001";"0";"3";"396.25";"";"";"";"1";"";"";"";"";"";;\r\n”
}
]´´´

json output :

´´´[{
“Description”: “Faktura {{3.VoNo}}”,
“Date”: “{{now}}”,
“Type”: 1,
“ImportLines”: [
{
“DocumentDate”: {{parseDate(3.ValDt; “YYYYMMDD”)}},
“DocumentNumber”: {{3.VoNo}},
//“Description”: “Varer ihht. utsendt faktura”,
“DueDate”: {{parseDate(3.ValDt; “YYYYMMDD”)}}, “Amount”: {{3.Am}},
“CustomerCode”: {{2.CustNo}},
“CurrencyCode”: “NOK”,
“InvoiceNo”: “{{3.VoNo}}”,
“Reference”: “{{3.AGRef}}”,
“DepartmentCode”: “{{ifempty(3.R1; 1)}}”,
},
{
“DocumentDate”: {{parseDate(3.ValDt; “YYYYMMDD”)}},
“DocumentNumber”: {{3.VoNo}},
“AccountNumber”: {{3.CrAcNo}},
“Description”: “Varer ihht. utsendt faktura”,
“Amount”: -{{3.Am}},
“Quantity”: 1.0,
“CurrencyCode”: “NOK”,
“ProductCode”: “1”
}
],
“SalesOrders”:
}]´´´

´´´[{
“Description”: “Faktura 100497”,
“Date”: “2024-04-24T09:12:40.706Z”,
“Type”: 1,
“ImportLines”: [
{
“DocumentDate”: 2024-04-09T22:00:00.000Z,
“DocumentNumber”: 100497,
//“Description”: “Varer ihht. utsendt faktura”,
“DueDate”: 2024-04-09T22:00:00.000Z, “Amount”: 48129.00,
“CustomerCode”: 10429,
“CurrencyCode”: “NOK”,
“InvoiceNo”: “100497”,
“Reference”: “”,
“DepartmentCode”: “1”,
},
{
“DocumentDate”: 2024-04-09T22:00:00.000Z,
“DocumentNumber”: 100497,
“AccountNumber”: 0,
“Description”: “Varer ihht. utsendt faktura”,
“Amount”: -48129.00,
“Quantity”: 1.0,
“CurrencyCode”: “NOK”,
“ProductCode”: “1”
}
],´´´

2

You could try and wrap your {{parseDate(3.ValDt; “YYYYMMDD”)}} sections , in a formatDate() function.

The parseDate() will transform your text ValDt to a date format, and formatDate() will then throw it into the correct format for your API:

image

image

{{formatDate(parseDate(4.ValDt; "YYYYMMDD"); "YYYY-MM-DD")}}

If you also want to catch empty dates, you can combine the if() and length() function. I made an example below, which will check if the date field is not empty (aka: length > 0). If the field is not empty, we’ll use the ValDt field. If it is empty, it’ll feed today’s date.

Note, this will only work, if your ValDt field is always there.

image
image

{{if(length(4.ValDt) = 0; formatDate(now; "YYYY-MM-DD"); formatDate(parseDate(4.ValDt; "YYYYMMDD"); "YYYY-MM-DD"))}}

Hope that sends you in the right direction!

Thomas
Nola Digital

2 Likes
3

Hi. And thank you so much :slight_smile:
Is it possible to take date {{3.ValDt}} and add 10 days and put it in field :

“DueDate”: {{parseDate({3.DueDt}t; “YYYYMMDD”)}}, “Amount”: {{3.Am}},

I see i had one error in the string after testing. Duedate should have {{3.DueDt}} in, and that`s where it often is empty.

Regards
Mathias