Hi Antonio!
your code works like a charm! but I’m getting problems on some fields, for example:
“OperatorCode__c”: “102”,
“DocumentNumber__c”: 54432,
“ReferenceDocument__c”: 21
DocumentNumber__c is returned as 0 (zero), same as OperatorCode
this is my code:
Dim document As New JSONItem(data)
'---- testata
Dim OFRIFDES,OFRIFDOC,OFSTATUS,OFDATSCA,OFDATCHI,OFCODMOD,OFCODAGE,OFCODVAL,OFDATDOC,OFFLFOSC,OFCODPOR as string
Dim OFCODUTE,OFNUMDOC as integer
Dim OFSCOCL2,OFSCOCL1,OFSCOPAG,OFSCONTI,OFSPETRA as double
Dim ShippingHandling,Agent__c,CurrencyCode__c,SendDate__c,DocumentDate__c,ForceDiscount__c,Port__c as string
OFRIFDES=document.Lookup("name","")
OFSCOCL2=document.Lookup("Discount__c","")
OFCODUTE=document.Lookup("OperatorCode_c","")
OFNUMDOC=document.Lookup("DocumentNumber_c","")
OFRIFDOC=document.Lookup("ReferenceDocument_c","")
OFSTATUS=document.Lookup("Status","")
OFDATSCA=document.Lookup("ExpirationDate","")
OFDATCHI=document.Lookup("CloseDate_c","")
OFCODMOD=document.Lookup("ReferenceModel_c","")
OFSCOCL1=document.Lookup("Discount","") '?
OFSCOPAG=document.Lookup("HigherPaymentDiscount__c","")
OFSCONTI=document.Lookup("DiscountsGlobalSurcharges__c","")
OFSPETRA=document.Lookup("ShippingHandling","")
OFCODAGE=document.Lookup("Agent_c","")
OFCODVAL=document.Lookup("CurrencyCode__c","")
OFRIFDOC=document.Lookup("SendDate__c","")
OFDATDOC=document.Lookup("DocumentDate__c","")
OFFLFOSC=document.Lookup("ForceDiscount__c","")
OFCODPOR=document.Lookup("Port__c","")
msgbox(OFNUMDOC.ToText)
msgbox(OFCODUTE.ToText)
'---- account
Dim ANDESCRI,ABCODCAT,ANCATCOM,ANCATCON,ANNUMCEL,ANCODAG1,ANCODFIS,ANPARIVA,ANNAZION,ANCODVAL,UTDC,AN_EMAIL,ABCODAGE,ABBUDGET,NOCODICE,ANCODICE,ABCODSET,ANCODREG,ANCODLIN as string
Dim Account As JSONItem=document.Lookup("Account", Nil)
If Account<>Nil Then
ANDESCRI=account.Lookup("Name","")
ABCODCAT=account.Lookup("Category__c","")
ANCATCOM=account.Lookup("CommercialCategory__c","")
ANCATCON=account.Lookup("AccountingCategory__c","")
ANNUMCEL=account.Lookup("MobilePhone__c","")
ANCODAG1=account.Lookup("AgentCode__c","")
ANCODFIS=account.Lookup("FiscalCode__c","")
ANPARIVA=account.Lookup("VatNumber__c","")
ANNAZION=account.Lookup("NationalCode__c","")
ANCODVAL=account.Lookup("CurrencyCode__c","")
UTDC=account.Lookup("EntryDate__c","")
AN_EMAIL=account.Lookup("EmailAddress__c","")
ABCODAGE=account.Lookup("CustomerResponsibleCommercial__c","")
ABBUDGET=account.Lookup("CustomerBudget__c","")
NOCODICE=account.Lookup("Serial__c","")
ANCODICE=account.Lookup("AccountCode__c","")
ABCODSET=account.Lookup("SectorCode__c","")
ANCODREG=account.Lookup("RegionCode__c","")
ANCODLIN=account.Lookup("LanguageCode__c","")
Else
System.DebugLog "No Account in document"
End If
'---- righe
Dim QuoteLineItems As JSONItem=document.Lookup("QuoteLineItems", Nil)
If QuoteLineItems<>Nil Then
Dim records As JSONItem=QuoteLineItems.Lookup("records", Nil)
If records<>Nil Then
Dim totalSize As Integer=QuoteLineItems.Lookup("totalSize", 0)
If totalSize<>records.Count Then
System.DebugLog "Not matching size"
Else
Dim n As Integer=records.Count-1
For i As Integer=0 To n
Dim attributeType, attributeUrl, ODSERIAL, id, product2Id, pricebookEntryId, productType, productUrl, productID, ODDESART, ODCODART, ProductCode As String
Dim ODQTAMOV, ODPREZZO, ODSCONT1,ODVALRIG As Double
Dim theRecord As JSONItem=records.Child(i)
'
Dim info As JSONItem=theRecord.Lookup("attributes", nil)
If info<>Nil Then
attributeType=info.Lookup("type", "")
attributeUrl=info.Lookup("url", "")
End If
'
ODSERIAL=theRecord.Lookup("QuoteId", "")
id=theRecord.Lookup("Id", "")
ODQTAMOV=theRecord.Lookup("Quantity", 0.0)
ODPREZZO=theRecord.Lookup("UnitPrice", 0.0)
ODSCONT1=theRecord.Lookup("Discount", 0.0)
product2Id=theRecord.Lookup("Product2Id", "")
ODVALRIG=theRecord.Lookup("TotalPrice__c", 0.0)
pricebookEntryId=theRecord.Lookup("PricebookEntryId", "")
'
info=theRecord.Lookup("Product2", Nil)
If info<>Nil Then
Dim info1 As JSONItem=info.Lookup("attributes", Nil)
If info1<>Nil Then
productType=info1.Lookup("type", "")
productUrl=info1.Lookup("url", "")
End If
productID=info.Lookup("Id", "")
ODDESART=info.Lookup("Name", "")
ODCODART=info.Lookup("ArticleCode__c", "")
ProductCode=info.Lookup("ProductCode", "")
End If
MsgBox(ProductCode)
'insert in db
stringa_sql="insert into ESL11ABGENOFF (OFNUMDOC,OFDATDOC,OFRIFDOC,OFSTATUS,OFDATSCA,OFDATCHI,OFCODMOD,OFRIFDES,OFCODVAL,OFCODLIS,OFCODPAG, "
stringa_sql=stringa_sql+"OFSCOCL1,OFSCOCL2,OFSCOPAG,OFSCONTI,OFFLFOSC,OFSPETRA,OFCODUTE,OFCODAGE,OFCODPOR,ODSERIAL,ODCODART,ODDESART,ODQTAMOV,"
stringa_sql=stringa_sql+"ODPREZZO,ODSCONT1,ODVALRIG,ANCODICE,ANDESCRI,ANINDIRI,AN___CAP,ANLOCALI,ANPROVIN,ANNAZION,ANTELEFO,ANNUMCEL,ANCODFIS,"
stringa_sql=stringa_sql+"ANPARIVA,ANCATCON,ANCODPAG,ANCODAG1,ANCODVAL,ANCODLIN,ANCATCOM,AN_EMAIL,ANCODREG,UTDC,NOCODICE,ABCODCAT,ABCODAGE,"
stringa_sql=stringa_sql+"ABBUDGET,ABCODSET) values ("
stringa_sql=stringa_sql+"'"+OFNUMDOC.ToText+"',"
stringa_sql=stringa_sql+"'"+OFDATDOC+"',"
stringa_sql=stringa_sql+"'"+OFRIFDOC+"',"
stringa_sql=stringa_sql+"'"+OFSTATUS+"',"
stringa_sql=stringa_sql+"'"+OFDATSCA+"',"
stringa_sql=stringa_sql+"'"+OFDATCHI+"',"
stringa_sql=stringa_sql+"'"+OFCODMOD+"',"
stringa_sql=stringa_sql+"'"+OFRIFDES+"',"
stringa_sql=stringa_sql+"'"+OFCODVAL+"',"
stringa_sql=stringa_sql+"'"+"'OFCODLIS'"+"',"
stringa_sql=stringa_sql+"'"+"'OFCODPAG'"+"',"
stringa_sql=stringa_sql+"'"+OFSCOCL1.ToText+"',"
stringa_sql=stringa_sql+"'"+OFSCOCL2.ToText+"',"
stringa_sql=stringa_sql+"'"+OFSCOPAG.ToText+"',"
stringa_sql=stringa_sql+"'"+OFSCONTI.ToText+"',"
stringa_sql=stringa_sql+"'"+OFFLFOSC+"',"
stringa_sql=stringa_sql+"'"+OFSPETRA.ToText+"',"
stringa_sql=stringa_sql+"'"+OFCODUTE.ToText+"',"
stringa_sql=stringa_sql+"'"+OFCODAGE+"',"
stringa_sql=stringa_sql+"'"+OFCODPOR+"',"
stringa_sql=stringa_sql+"'"+ODSERIAL+"',"
stringa_sql=stringa_sql+"'"+ODCODART+"',"
stringa_sql=stringa_sql+"'"+ODDESART+"',"
stringa_sql=stringa_sql+"'"+ODQTAMOV.ToText+"',"
stringa_sql=stringa_sql+"'"+ODPREZZO.ToText+"',"
stringa_sql=stringa_sql+"'"+ODSCONT1.ToText+"',"
stringa_sql=stringa_sql+"'"+ODVALRIG.ToText+"',"
stringa_sql=stringa_sql+"'"+ANCODICE+"',"
stringa_sql=stringa_sql+"'"+ANDESCRI+"',"
stringa_sql=stringa_sql+"'"+"'ANINDIRI'"+"',"
stringa_sql=stringa_sql+"'"+"'AN___CAP'"+"',"
stringa_sql=stringa_sql+"'"+"'ANLOCALI'"+"',"
stringa_sql=stringa_sql+"'"+"'ANPROVIN'"+"',"
stringa_sql=stringa_sql+"'"+ANNAZION+"',"
stringa_sql=stringa_sql+"'"+"'ANTELEFO'"+"',"
stringa_sql=stringa_sql+"'"+ANNUMCEL+"',"
stringa_sql=stringa_sql+"'"+ANCODFIS+"',"
stringa_sql=stringa_sql+"'"+ANPARIVA+"',"
stringa_sql=stringa_sql+"'"+ANCATCON+"',"
stringa_sql=stringa_sql+"'"+"'ANCODPAG'"+"',"
stringa_sql=stringa_sql+"'"+ANCODAG1+"',"
stringa_sql=stringa_sql+"'"+ANCODVAL+"',"
stringa_sql=stringa_sql+"'"+ANCODLIN+"',"
stringa_sql=stringa_sql+"'"+ANCATCOM+"',"
stringa_sql=stringa_sql+"'"+AN_EMAIL+"',"
stringa_sql=stringa_sql+"'"+ANCODREG+"',"
stringa_sql=stringa_sql+"'"+UTDC+"',"
stringa_sql=stringa_sql+"'"+NOCODICE+"',"
stringa_sql=stringa_sql+"'"+ABCODCAT+"',"
stringa_sql=stringa_sql+"'"+ABCODAGE+"',"
stringa_sql=stringa_sql+"'"+ABBUDGET+"',"
stringa_sql=stringa_sql+"'"+ABCODSET+"')"
'ps = db.Prepare(stringa_sql)
msgbox(stringa_sql)
'ps.SQLExecute
''msgbox(quoteid+"-"+id+"-"+quantity.ToText+"-"+unitprice.ToText+"-"+discount.ToText+"-"+product2id+"-"+totalPrice__c.ToText+"-"+pricebookEntryId.ToText+"-"+info+"-"+productID+"-"+productName+"-"+productArticleCode__c+"-"+ProductCode)
Next
End If
Else
System.DebugLog "No records in QuoteLineItems"
End If
Else
System.DebugLog "No QuoteLineItems in document"
End If
why?