Create a JSON Array from a ExecuteSQL Query

# Generate JSON Data for Datawarehouse

Set Field [ im::JSONLabelItem ; JSONSetElement ( "" ; ["Plant" ; 25 ; JSONNumber]; ["ItemRef" ; im::i.a.ItemReference ; JSONString]; ["CustomerNum" ; im::i.c.b.CustNum ; JSONNumber]; ["CustomerName" ; im::i.c.b.CustName ; JSONString]; ["Description" ; im::i.a.description ; JSONString]; ["Serial" ; im::i.a.ItemSerialNum ; JSONNumber]; ["File" ; im::i.a.ItemFileNum ; JSONNumber]; ["Method" ; im::i.a.Type_q.ProductionMethod ; JSONString]; ["Die" ; im::q.l.TOOL ; JSONString]; ["Size" ; im::q.l.Tool_Width & " x " & im::q.l.Tool_Height ; JSONString]; ["Facestock" ; im::q.l.FS.REFERENCE ; JSONString]; ["Material" ; im::q.l.FS_Descrip ; JSONString]; ["Laminate" ; im::q.l.LA_Descrip ; JSONString]; ["Varnish" ; im::q.l.VARNISH ; JSONString] ) ]

Set Variable [ $ItemJSON ; Value: JSONSetElement ( "" ; ["Plant" ; 25 ; JSONNumber]; ["ItemRef" ; im::i.a.ItemReference ; JSONString]; ["CustomerNum" ; im::i.c.b.CustNum ; JSONNumber]; ["CustomerName" ; im::i.c.b.CustName ; JSONString]; ["Description" ; im::i.a.description ; JSONString… ]

Set Variable [ $WebPricingInfo ; Value: ExecuteSQL ( "SELECT ItemID,Qty,Price,UOM,PriceOverride,WebPrice FROM WebPricing WHERE ItemID = ?" ; "" ; "" ; im::i.a.ItemSerialNum ) ]

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

Set Variable [ $cnt ; Value: 1 ]

# Create Pricing List JSON

If [ $count>0 ]

Loop [ Flush: Always ]

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

Set Variable [ $rowFormat ; Value: JSONSetElement ( "" ; ["ItemID" ; GetValue ( $row ; 1 ) ; JSONString]; ["Qty" ; GetValue ( $row ; 2 ) ; JSONNumber]; ["Price" ; GetValue ( $row ; 3 ) ; JSONNumber]; ["UOM" ; GetValue ( $row ; 4 ) ; JSONString]; ["PriceOverride" ; GetValue ( $row ; 5 ) … ]

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

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

Exit Loop If [ $cnt > $count ]

End Loop

End If

// Set Variable [ $ItemWebPricingJSON ]

# 

# 

# Update Data Warehouse (FlexlinkAllDW)

Set Variable [ $serial ; Value: im::i.a.ItemSerialNum ]

Set Variable [ $plant ; Value: JSONGetElement ( $ItemJSON ; "Plant" ) ]

Set Variable [ $json ; Value: im::JSONLabelItem ]

Freeze Window

New Window [ Style: Card ; Name: "Update Item" ; Using layout: \ ; Height: 50 ; Width: 50 ]

Go to Layout [ “JSONDWItems” (JSONDWItems) ; Animation: None ]

Set Error Capture [ On ]

Perform Find [ Restore ]

If [ Get(FoundCount)=0 ]

New Record/Request

Set Field [ JSONDWItems::JSONLabelItem ; $json ]

Else

Set Field [ JSONDWItems::JSONLabelItem ; $json ]

End If

Close Window [ Current Window ]

# 

// Set Variable [ $post_data ; Value: "{\"token\":\"xxxxx\",\"data\":["& $ItemJSON & "]}" ]

// Insert from URL [ Select ; With dialog: On ; Target: $ResultField ; "https://api.integrator.io/v1/exports/xxxxx/data" ; cURL options: " --request POST" & " --header \"Content-Type: application/json\"" & " -d @$post_data" ]

// Show Custom Dialog [ "Result" ; $ResultField ]