Creating a JSON from VFP

[DominicProgrammer]

Hi Experts.

I have an issue here trying to create a json like txt file so i can use in in API link but i cant get the products coming in records [there are only 2 records in that temp table] why does the records duplicate in the ItemDetails object ? Kindly assist me. Thanks in advance:

Close All

lcCRLF = Chr(13)+Chr(10)
local krec,i
Use temp In 0
Select temp
Go Top

orec=1
i=1
SCAN
krec=Recno()
     Scatter Memvar
     kstockcode=stockcode
     Strtofile(['{"CallBackUrl":"[URL unfurl="true"]http://192.168.0.5:8128/kekaapi/invoices",[/URL]]+lcCRLF, [c:\json.txt], .F.)
     Strtofile(["InvoiceNumber":,] +'"'+Alltrim(m.docno)+'"'+','+ lcCRLF, [c:\json.txt], .T.)
     Strtofile(["TotalInvoiceAmount":,] +'"'+Alltrim(Str(m.amount))+'"'+','+ lcCRLF, [c:\json.txt], .T.)
     Strtofile(["TotalDiscount":,] +'"'+Alltrim(Str(m.discamt))+'"'+','+ lcCRLF, [c:\json.txt], .T.)
     Strtofile(["PINOfBuyer":,] +'P001244574Q'+','+ lcCRLF, [c:\json.txt], .T.)
     Strtofile(["HeaderFields":] +'{'+','+ lcCRLF, [c:\json.txt], .T.)

     Strtofile([		"Header1": "",] +''+ lcCRLF, [c:\json.txt], .T.)
     Strtofile([		"Header2": "",] +''+ lcCRLF, [c:\json.txt], .T.)
     Strtofile([		"Header3": "",] +''+ lcCRLF, [c:\json.txt], .T.)
     Strtofile([		"Header4": "",] +''+ lcCRLF, [c:\json.txt], .T.)
     Strtofile([		"Header5": "",] +''+ lcCRLF, [c:\json.txt], .T.)
     Strtofile([   },] +''+ lcCRLF, [c:\json.txt], .T.)
     **----------------Item details
     Strtofile(["ItemDetails":] +'[{ "HSCode"'+":"+','+ lcCRLF, [c:\json.txt], .T.)
     for i=1 to RECCOUNT()
          Strtofile([                              "ItemCode":,] +'"'+Alltrim(m.stockcode)+'"'+','+ lcCRLF, [c:\json.txt], .t.)
          Strtofile([                              "Description":,] +'"'+Alltrim(m.desc)+'"'+','+ lcCRLF, [c:\json.txt], .T.)
          Strtofile([                              "UnitInclPrice":,] +'"'+Alltrim(Str(m.unitprice))+'"'+','+ lcCRLF, [c:\json.txt], .T.)
          Strtofile([                              "UnitDiscount":,] +'"'+Alltrim(Str(m.discamt))+'"'+','+ lcCRLF, [c:\json.txt], .T.)
          Strtofile([                  },] +''+ lcCRLF, [c:\json.txt], .T.)
		 krec=krec+1     
     NEXT
     Strtofile([} ]+''+ lcCRLF, [c:\json.txt], .T.)
     Select temp
	 
Endscan
Modify File c:\json.txt

 

[mJindrova]

Hi,

Because your code duplicate they.

[pre]
Close All

lcCRLF = Chr(13)+Chr(10)
local krec,i
Use temp In 0
Select temp
Go Top

orec=1
i=1
SCAN && scan table from first record to last record ( RECCOUNT() )
krec=Recno()
Scatter Memvar
kstockcode=stockcode
Strtofile(['{"CallBackUrl":"

http://192.168.0.5:8128/kekaapi/invoices",

]+lcCRLF, [c:\json.txt], .F.)
Strtofile(["InvoiceNumber":,] +'"'+Alltrim(m.docno)+'"'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile(["TotalInvoiceAmount":,] +'"'+Alltrim(Str(m.amount))+'"'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile(["TotalDiscount":,] +'"'+Alltrim(Str(m.discamt))+'"'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile(["PINOfBuyer":,] +'P001244574Q'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile(["HeaderFields":] +'{'+','+ lcCRLF, [c:\json.txt], .T.)

Strtofile([ "Header1": "",] +''+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ "Header2": "",] +''+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ "Header3": "",] +''+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ "Header4": "",] +''+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ "Header5": "",] +''+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ },] +''+ lcCRLF, [c:\json.txt], .T.)
**----------------Item details
Strtofile(["ItemDetails":] +'[{ "HSCode"'+":"+','+ lcCRLF, [c:\json.txt], .T.)
for i=1 to RECCOUNT() && create item x records in table
Strtofile([ "ItemCode":,] +'"'+Alltrim(m.stockcode)+'"'+','+ lcCRLF, [c:\json.txt], .t.)
Strtofile([ "Description":,] +'"'+Alltrim(m.desc)+'"'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ "UnitInclPrice":,] +'"'+Alltrim(Str(m.unitprice))+'"'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ "UnitDiscount":,] +'"'+Alltrim(Str(m.discamt))+'"'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ },] +''+ lcCRLF, [c:\json.txt], .T.)
krec=krec+1
NEXT
Strtofile([} ]+''+ lcCRLF, [c:\json.txt], .T.)
Select temp

Endscan
Modify File c:\json.txt
[/pre]

 

[DominicProgrammer]

Hi Jindrova.

Thanks for your reply, i appreciate, that duplicate is what i want to solve, help me, i have attached the Db too.

This is the JSON result i envision.

<'{"CallBackUrl":"

http://192.168.0.5:8128/kekaapi/invoices",

"InvoiceNumber":,"910641",
"TotalInvoiceAmount":,"0",
"TotalDiscount":,"0",
"PINOfBuyer":,P001244574Q,
"HeaderFields":{,
"Header1": "",
"Header2": "",
"Header3": "",
"Header4": "",
"Header5": "",
},
"ItemDetails":[{ "HSCode":,
"ItemCode":,"1010060107",
"Description":,"RAHA DRINKING CHOC SACHET 24X100G",
"UnitInclPrice":,"963",
"UnitDiscount":,"0",
},
"ItemCode":,"1010050306",
"Description":,"RAHA DRINKING CHOC.12*100 GM JARS(DOZ)",
"UnitInclPrice":,"963",
"UnitDiscount":,"0",
},
}
>


Regards.

 

[mJindrova]

Hmm, You don't see bug in your code...
You code contains many bugs (duplicite in ItemDetails, nonvalid JSON)


[pre]
Close All

lcCRLF = Chr(13)+Chr(10)
Use temp In 0
Select temp
Go Top

Scatter Memvar
Strtofile([{"CallBackUrl":"

http://192.168.0.5:8128/kekaapi/invoices",

]+lcCRLF, [c:\json.txt], .F.)
Strtofile(["InvoiceNumber":] +'"'+Alltrim(m.docno)+'"'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile(["TotalInvoiceAmount":] +'"'+Alltrim(Str(m.amount))+'"'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile(["TotalDiscount":] +'"'+Alltrim(Str(m.discamt))+'"'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile(["PINOfBuyer":"] +'P001244574Q'+'",'+ lcCRLF, [c:\json.txt], .T.)
Strtofile(["HeaderFields":] +'{'+''+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ "Header1": "",] + lcCRLF, [c:\json.txt], .T.)
Strtofile([ "Header2": "",] + lcCRLF, [c:\json.txt], .T.)
Strtofile([ "Header3": "",] + lcCRLF, [c:\json.txt], .T.)
Strtofile([ "Header4": "",] + lcCRLF, [c:\json.txt], .T.)
Strtofile([ "Header5": ""] + lcCRLF, [c:\json.txt], .T.)
Strtofile([ },] + lcCRLF, [c:\json.txt], .T.)

**----------------Item details
Strtofile(["ItemDetails":] +'['+ lcCRLF, [c:\json.txt], .T.)

SCAN && scan table from first record to last record ( RECCOUNT() )

Scatter Memvar
Strtofile('{ "HSCode":"",'+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ "ItemCode":] +'"'+Alltrim(m.stockcode)+'"'+','+ lcCRLF, [c:\json.txt], .t.)
Strtofile([ "Description":] +'"'+Alltrim(m.desc)+'"'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ "UnitInclPrice":] +'"'+Alltrim(Str(m.unitprice))+'"'+','+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ "UnitDiscount":] +'"'+Alltrim(Str(m.discamt))+'"'+ lcCRLF, [c:\json.txt], .T.)
Strtofile([ }]+IIF(RECNO()<RECCOUNT(), ',', '')+lcCRLF, [c:\json.txt], .T.)

Endscan
Strtofile(']}'+ lcCRLF, [c:\json.txt], .T.)

Modify File c:\json.txt
[/pre]

MartinaJ

 

[DominicProgrammer]

Hi Martinaj.

Yours worked. Thanks thanks thanks ,About the Json invalidation i will try work on it.

Regards.
Dominic

 

[Rajesh Karunakaran]

Dear Dominic,

Just thought to add that, after creating your JSON text file, you can validate it using sites like

https://www.jsonlint.com/.

If there are any errors, the site will give you clues.

Rajesh