Putting a invoice in to JSON (sample)

# Generate JSON Data for Datawarehouse
# Create Invoice Part of JSON

Set Variable [ $InvoiceJSON ; Value: JSONSetElement ( "" ; ["Plant" ; 25 ; JSONNumber]; ["AMNum" ; am::a.a.AMnum ; JSONNumber]; ["InvoiceNumber" ; am::a.am.INVRef ; JSONNumber]; ["CustomerNumber" ; am::a.c.b.CustNum ; JSONNumber]; ["CustomerName" ; am::a.c.b.CustName ; JSONString]; ["Type… ]

Set Variable [ $invNum ; Value: am::a.a.AMnum ]

Set Variable [ $lineItems ; Value: ExecuteSQL ( "SELECT \"ad.Description\",\"ad.QTY\",\"ad.Amount\",\"z.c.LineSubTotal\",\"z.c.LineTax\",\"ad.Category\",\"ad.Class\",\"ad.ClassFamily\",\"ad.ClassFamilyDivision\" FROM \"am.ad\" WHERE \"ad.AMnum\" = ?" ; "" ; "" ; am::a.a.AMnum ) ]

Set Variable [ $count ; Value: Substitute ( ValueCount ( $lineItems ) ; "," ; ¶ ) ]

Set Variable [ $cnt ; Value: 1 ]

# Create Invoice Items Part of JSON

Loop [ Flush: Always ]

Set Variable [ $row ; Value: Substitute ( GetValue ( $lineItems ; $cnt ) ; "," ; ¶ ) ]

Set Variable [ $rowFormat ; Value: JSONSetElement ( "" ; ["ItemDecription" ; GetValue ( $row ; 1 ) ; JSONString]; ["QTY" ; GetValue ( $row ; 2 ) ; JSONNumber]; ["Price" ; GetValue ( $row ; 3 ) ; JSONNumber]; ["LineTotal" ; GetValue ( $row ; 4 ) ; JSONNumber]; ["SalesTax" ; GetValue ( $r… ]

Set Variable [ $JSONLineItems ; Value: $JSONLineItems & $rowFormat & If ( $cnt = $count ; ""; "," ) ]

Set Variable [ $cnt ; Value: $cnt + 1 ]

Exit Loop If [ $cnt > $count ]

End Loop

# 

Freeze Window

# Add JSON to Datawarehouse

New Window [ Style: Card ; Name: "Update Item" ; Using layout: “DWInvoice” (DWInvoice) ; Height: 50 ; Width: 50 ]

Set Error Capture [ On ]

Perform Find [ Restore ]

If [ Get(FoundCount)=0 ]

New Record/Request

Set Field [ DWInvoice::DatabaseID ; $invNum ]

Set Field [ DWInvoice::JSONInvoice ; "{ \"Invoice\": " & $InvoiceJSON & ", \"Items\": [" & $JSONLineItems & "]}" / Replace ( "{ \"Order\": " & $InvoiceJSON & ", \"LineItems\": [" & $JSONLineItems & "]}" ; Length ( "{ \"Order\": " & $InvoiceJSON & ", \"LineItems\": [" & $JSONLineItems & "]}" ) - 2 ; 1 ; "" ) / ]

Commit Records/Requests [ With dialog: Off ]

Else

Set Field [ DWInvoice::DatabaseID ; $invNum ]

Set Field [ DWInvoice::JSONInvoice ; "{ \"Invoice\": " & $InvoiceJSON & ", \"Items\": [" & $JSONLineItems & "]}" / Replace ( "{ \"Order\": " & $InvoiceJSON & ", \"LineItems\": [" & $JSONLineItems & "]}" ; Length ( "{ \"Order\": " & $InvoiceJSON & ", \"LineItems\": [" & $JSONLineItems & "]}" ) - 2 ; 1 ; "" ) / ]

Commit Records/Requests [ With dialog: Off ]

End If

Close Window [ Current Window ]