{
"id":"Test01",
"invoices":[
{
"advAmt":0,
"amountTaxation":false,
"currency":"INR",
"customer":{ },
"dateOfDelivery":"2019-02-06",
"dueDate":"2019-02-06",
"exemptedAmount":false,
"grandTotal":100,
"invoiceIssueDate":"2019-02-06",
"invoiceNumber":"TEST_INV_MGF03_01",
"invoiceReference":"",
"invoiceType":"I",
"lineItems":[
{
"amount":100,
"amountAfterDiscount":100,
"code":"A1",
"descriptionArabic":"descarabic",
"descriptionEnglish":"desceng",
"discountAmount":0,
"discountPercent":0,
"price":100,
"priceAfterVat":100,
"quantity":1,
"serialNumber":1,
"totalVat":0,
"uomArabic":"uomarabic",
"uomEnglish":"uomeng",
"vatPercent":0
}
],
"milestonePayments":false,
"narrationArabic":"narraarabic",
"narrationEnglish":"narraeng",
"outstandingAmount":100,
"percentOfCompletion":20,
"profitMargin":{ },
"remarksArabic":"remarkarabic",
"remarksEnglish":"remarkeng",
"salespersonArabic":"salesarabic",
"salespersonEnglish":"saleseng",
"selfAccountForTax":false,
"shipmentRefNumber":"SHIP123",
"supplier":{ },
"totalBeforeVAT":100,
"totalVAT":0
}
],
"supplierId":"663"
}
is it possible to write JSON file like this format if yes then how to define table schema or dataset relation to achive this using Write-json method.
Thanks in Adv.
Regards
-Shashank
Hi Shashank
I do not believe you can get there directly, but you can get close and then you can easily get the object you want:
It looks like you have a dataset with some unidentified top level object, I will call it "order", then you have an "invoices" table with a DATA-RELATION ..... NESTED FOREIGN-KEY-HIDDEN between the order and invoices, and then there is a table "lineItems" with a DATA-RELATION ..... NESTED FOREIGN-KEY-HIDDEN between invoices and lineItems. You need to define the dataset as SERIALIZE-HIDDEN.
DEFINE TEMP-TABLE ttOrder SERIALIZE-NAME "order" .......
DEFINE TEMP-TABLE ttInvoice SERIALIZE-NAME "invoices" .....
DEFINE TEMP-TABLE ttLineItem SERIALIZE-NAME "lineItems" .....
DEFINE DATASET dsOrderInvoices SERIALIZE-HIDDEN
FOR ttOrder, ttInvoice, ttLineItem
DATA-RELATION invoiceOfOrder FOR ttOrder, ttInvoice
RELATION-FIELDS(ordNo, ordNo) NESTED FOREIGN-KEY-HIDDEN
DATA-RELATION lineOfInvoice FOR ttInvoice, ttLineItem
RELATION-FIELDS(invNo, invNo) NESTED FOREIGN-KEY-HIDDEN.
This will however serialize all the parent fields and then the child object array. If you want to control where it goes, you can define a PARENT-ID-RELATION, which allows you to specify which parent fields you want before and after the the child, but then you build the relation by putting the RECID of the parent in a field on the child.
DEFINE TEMP-TABLE ttOrder SERIALIZE-NAME "order" .......
DEFINE TEMP-TABLE ttInvoice SERIALIZE-NAME "invoices"
...
FIELD orderId as RECID
...
DEFINE TEMP-TABLE ttLineItem SERIALIZE-NAME "lineItems"
...
FIELD invoiceId as RECID
...
DEFINE DATASET dsOrderInvoices SERIALIZE-HIDDEN
FOR ttOrder, ttInvoice, ttLineItem
PARENT-ID-RELATION invoiceOfOrder FOR ttOrder, ttInvoice
PARENT-ID-FIELD (orderId)
PARENT-FIELDS-BEFORE ("id")
PARENT-FIELDS-AFTER("supplierId")
PARENT-ID-RELATION lineOfInvoice FOR ttInvoice, ttLineItem
PARENT-ID-FIELD (invoiceId)
PARENT-FIELDS-BEFORE("advAmt", "amountTaxation", .... , "invoiceType")
PARENT-FIELDS-AFTER("milestonePayments", ...... , "totalVat").
Next, you serialize the dataset to a JsonObject. This object will have a single "order" node with an array of orders. The assumption is that there is always exactly one order. In that case, you can get the above in the long but clear way as follows.
DEFINE VARIABLE orderdataset AS JsonObject NO-UNDO.
DEFINE VARIABLE orders AS JsonArray NO-UNDO.
DEFINE VARIABLE order AS JsonObject NO-UNDO.
DATASET dsOrder:WRITE-JSON("JsonObject", orderdataset).
orders = order:GetJsonArray("order").
order = orders:GetJsonObject(1).
You can do it even shorter like this:
DEFINE VARIABLE order AS JsonObject NO-UNDO.
DATASET dsOrder:WRITE-JSON("JsonObject", order).
order = order:GetJsonArray("order"):GetJsonObject(1).
Now the "order" variable contains the Json as you described.
You can then user the "order" object's WRITE() method to get it into a LONG-CHAR, MEMPTR or CHARACTER. You can use the "order" object's WRITE-FILE() to get it to disk.
Hi Shashank
I do not believe you can get there directly, but you can get close and then you can easily get the object you want:
It looks like you have a dataset with some unidentified top level object, I will call it "order", then you have an "invoices" table with a DATA-RELATION ..... NESTED FOREIGN-KEY-HIDDEN between the order and invoices, and then there is a table "lineItems" with a DATA-RELATION ..... NESTED FOREIGN-KEY-HIDDEN between invoices and lineItems. You need to define the dataset as SERIALIZE-HIDDEN.
DEFINE TEMP-TABLE ttOrder SERIALIZE-NAME "order" .......
DEFINE TEMP-TABLE ttInvoice SERIALIZE-NAME "invoices" .....
DEFINE TEMP-TABLE ttLineItem SERIALIZE-NAME "lineItems" .....
DEFINE DATASET dsOrderInvoices SERIALIZE-HIDDEN
FOR ttOrder, ttInvoice, ttLineItem
DATA-RELATION invoiceOfOrder FOR ttOrder, ttInvoice
RELATION-FIELDS(ordNo, ordNo) NESTED FOREIGN-KEY-HIDDEN
DATA-RELATION lineOfInvoice FOR ttInvoice, ttLineItem
RELATION-FIELDS(invNo, invNo) NESTED FOREIGN-KEY-HIDDEN.
This will however serialize all the parent fields and then the child object array. If you want to control where it goes, you can define a PARENT-ID-RELATION, which allows you to specify which parent fields you want before and after the the child, but then you build the relation by putting the RECID of the parent in a field on the child.
DEFINE TEMP-TABLE ttOrder SERIALIZE-NAME "order" .......
DEFINE TEMP-TABLE ttInvoice SERIALIZE-NAME "invoices"
...
FIELD orderId as RECID
...
DEFINE TEMP-TABLE ttLineItem SERIALIZE-NAME "lineItems"
...
FIELD invoiceId as RECID
...
DEFINE DATASET dsOrderInvoices SERIALIZE-HIDDEN
FOR ttOrder, ttInvoice, ttLineItem
PARENT-ID-RELATION invoiceOfOrder FOR ttOrder, ttInvoice
PARENT-ID-FIELD (orderId)
PARENT-FIELDS-BEFORE ("id")
PARENT-FIELDS-AFTER("supplierId")
PARENT-ID-RELATION lineOfInvoice FOR ttInvoice, ttLineItem
PARENT-ID-FIELD (invoiceId)
PARENT-FIELDS-BEFORE("advAmt", "amountTaxation", .... , "invoiceType")
PARENT-FIELDS-AFTER("milestonePayments", ...... , "totalVat").
Next, you serialize the dataset to a JsonObject. This object will have a single "order" node with an array of orders. The assumption is that there is always exactly one order. In that case, you can get the above in the long but clear way as follows.
DEFINE VARIABLE orderdataset AS JsonObject NO-UNDO.
DEFINE VARIABLE orders AS JsonArray NO-UNDO.
DEFINE VARIABLE order AS JsonObject NO-UNDO.
DATASET dsOrder:WRITE-JSON("JsonObject", orderdataset).
orders = order:GetJsonArray("order").
order = orders:GetJsonObject(1).
You can do it even shorter like this:
DEFINE VARIABLE order AS JsonObject NO-UNDO.
DATASET dsOrder:WRITE-JSON("JsonObject", order).
order = order:GetJsonArray("order"):GetJsonObject(1).
Now the "order" variable contains the Json as you described.
You can then user the "order" object's WRITE() method to get it into a LONG-CHAR, MEMPTR or CHARACTER. You can use the "order" object's WRITE-FILE() to get it to disk.
Thanks a lot @Simon L Prinsloo definitely it will helpful for me..