Query custom fields from Invoice LineItems

April 30, 20196 yr

Newbies

Hi there,

I am trying to query custom fields in invoice line items and am having trouble getting the LineItem DataExt object in the response.

I pass OwnerID as 0 in the InvoiceQuery Request but i don't get any DataExt objects in the response at all (when i check the XML Response text).

Is there something else i need to add to the Request?

The fields i'm trying to get are "% Complete" and "% This Billing" -- custom Line Item fields

See below script!

Set Variable [ $QBListID; Value:PROJECTS_MainMenu::QBListID ]
Set Variable [ $result; Value:PCQB_RqNew( "InvoiceQuery"; "" ) ]
Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "EntityFilter:ListID" ; $QBListID )]
Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "IncludeLineItems" ; True )]
//  Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "IncludeRetElement" ; "DataExtRet")]
Set Variable [ $result; Value:PCQB_RqAddFieldWithValue( "OwnerID" ; "0" )]
Set Field [ Utility::g.QBResult; PCQB_RqExecute ]
Perform Script [ “Check for FMConnector Errors (Halt)” ]

Set Variable [ $result; Value:PCQB_RsOpenFirstRecord ]

Set Variable [ $i; Value:0 ]
Loop
	Exit Loop If [ $Result= "!!ERROR!!" or $Result= "End" or $Result= "?" ]
	Set Variable [ $InvoiceDate; Value:PCQB_RsGetFirstFieldValue( "Detail:TxnDate" )]
	Set Variable [ $json; Value:JSONSetElement ( $json; "Invoices["& $i &"]Date" ; $InvoiceDate; JSONString ) ]
	Set Variable [ $result; Value:PCQB_RsOpenFirstRelatedRecord( "InvoiceLineRet" ) ]
	Set Variable [ $z; Value:0 ]
	Loop
		Exit Loop If [ $Result= "!!ERROR!!" or $Result= "End" or $Result= "?" ]
		Set Variable [ $amount; Value:PCQB_RsGetFirstFieldValue( "Amount" )]
		Set Variable [ $json; Value:JSONSetElement ( $json; "Invoices[" &$i& "]Line[" &$z& "]Amount"; $amount ; JSONNumber ) ]
		Set Variable [ $result; Value:PCQB_RsOpenFirstRelatedRecord( "DataExtRet" ) ]
			Loop
				Exit Loop If [ $Result= "!!ERROR!!" or $Result= "End" or $Result= "?" ]
				Set Variable [ $pField; Value:PCQB_RsGetFirstFieldValue( "DataExtName" )]
				If [ PatternCount ( $pField ; "% COMPLETE") ]
                	Set Variable [ $pComplete; Value:PCQB_RsGetFirstFieldValue( "DataExtValue" )]
					Set Variable [ $json; Value:JSONSetElement ( $json; "Invoices["& $i &"]Line[" & $z &"]PercentageComplete" ; $pComplete; JSONString ) ]
				Else If [ PatternCount ( $pField ; "% This Billing" ) ]
					Set Variable [ $billingThis; Value:PCQB_RsGetFirstFieldValue( "DataExtValue" )]
					Set Variable [ $json; Value:JSONSetElement ( $json; "Invoices["& $i &"]Line[" & $z &"]PercentageBilling" ; $billingThis; JSONString) ]
				End If
				Set Variable [ $result; Value:PCQB_RsOpenNextRelatedRecord ]
			End Loop
			Set Variable [ $result; Value:PCQB_RsCloseRelatedRecord ]
			Set Variable [ $z; Value:$z + 1 ]
			Set Variable [ $result; Value:PCQB_RsOpenNextRelatedRecord ]
		End Loop
		Set Variable [ $result; Value:PCQB_RsCloseRelatedRecord ]
		Set Variable [ $i; Value:$i + 1 ]
		Set Variable [ $result; Value:PCQB_RsOpenNextRecord ]
End Loop

May 1, 20196 yr

Is it possible that the Custom Fields were defined with a different OwnerID value, perhaps by another integrated application?

You're looking at the raw xml using the PCQB_SGetXML( "Response" ; "" ) function, right?

Worth noting: they normally appear in the xml response only when they have a value.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC 28105
704.814.6852

P.S. I asked about the raw xml because I'm wondering if the "%" symbol is coming back encoded, and therefore isn't matching your PatternCount test.

Edited May 1, 20196 yr by Geoffrey Gerhard

May 1, 20196 yr

thieInthiel,

Be sure to check that your company file in fact has the custom fields defined. Your scripting is properly making the request, all you would need to do is provide an "OwnerID" of "0" just after the last IncludeRetElement call.

Best,

-PCIPal

May 2, 20196 yr

Author
Newbies

20 hours ago, Geoffrey Gerhard said:

Is it possible that the Custom Fields were defined with a different OwnerID value, perhaps by another integrated application?

You're looking at the raw xml using the PCQB_SGetXML( "Response" ; "" ) function, right?

Worth noting: they normally appear in the xml response only when they have a value.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC 28105
704.814.6852

P.S. I asked about the raw xml because I'm wondering if the "%" symbol is coming back encoded, and therefore isn't matching your PatternCount test.

Geoffrey,

I'm pretty sure the fields were defined in QB itself. Yes with the PCQB_SGETXML function.

I didn't think about the % symbol coming back encoded, which it probably would however I don't see the fields anywhere at all in the XML.

here is the line item in QB -->

image.png.ab6156f87deb5b8ab4241430ea09a398.png

and here is the XML for the invoice, with the above line being the last LineItem in the XML

<InvoiceRet>
				<TxnID>111A21-1525273534</TxnID>
				<TimeCreated>2018-05-02T10:05:34-06:00</TimeCreated>
				<TimeModified>2018-05-29T11:24:08-06:00</TimeModified>
				<EditSequence>1525273534</EditSequence>
				<TxnNumber>101457</TxnNumber>
				<CustomerRef>
					<ListID>80001DA5-1506523615</ListID>
					<FullName></FullName>
				</CustomerRef>
				<ARAccountRef>
					<ListID>3E0000-884317953</ListID>
					<FullName>Accounts Receivable</FullName>
				</ARAccountRef>
				<TemplateRef>
					<ListID>E0000-1117116752</ListID>
					<FullName>D) Other - Progress Invoice</FullName>
				</TemplateRef>
				<TxnDate>2018-04-30</TxnDate>
				<RefNumber>10883</RefNumber>
				<BillAddress>
					<Addr1>/Addr1>
					<Addr2></Addr2>
					<City></City>
					<State>TX</State>
					<PostalCode>75234</PostalCode>
				</BillAddress>
				<BillAddressBlock>
					<Addr1></Addr1>
					<Addr2></Addr2>
					<Addr3></Addr3>
				</BillAddressBlock>
				<IsPending>false</IsPending>
				<IsFinanceCharge>false</IsFinanceCharge>
				<DueDate>2018-04-30</DueDate>
				<ShipDate>2018-04-30</ShipDate>
				<Subtotal>15885.00</Subtotal>
				<ItemSalesTaxRef>
					<ListID>1D0000-918242596</ListID>
					<FullName>SalesTax</FullName>
				</ItemSalesTaxRef>
				<SalesTaxPercentage>8.25</SalesTaxPercentage>
				<SalesTaxTotal>0.00</SalesTaxTotal>
				<AppliedAmount>-15885.00</AppliedAmount>
				<BalanceRemaining>0.00</BalanceRemaining>
				<IsPaid>true</IsPaid>
				<IsToBePrinted>false</IsToBePrinted>
				<IsToBeEmailed>false</IsToBeEmailed>
				<CustomerSalesTaxCodeRef>
					<ListID>20000-1033839081</ListID>
					<FullName>Non</FullName>
				</CustomerSalesTaxCodeRef>
				<InvoiceLineRet>
					<TxnLineID>111A23-1525273534</TxnLineID>
					<Desc>Professional Architectural Interior Design Services provided for Change Order #4 as set forth in agreement dated March 5, 2018 as follows:</Desc>
				</InvoiceLineRet>
				<InvoiceLineRet>
					<TxnLineID>111A24-1525273534</TxnLineID>
				</InvoiceLineRet>
				<InvoiceLineRet>
					<TxnLineID>111A25-1525273534</TxnLineID>
					<ItemRef>
						<ListID>800000E0-1489084293</ListID>
						<FullName>BA</FullName>
					</ItemRef>
					<Desc>Bid Administration &amp; Construction Administration</Desc>
					<Quantity>0.6</Quantity>
					<Rate>26475.00</Rate>
					<ClassRef>
						<ListID>8000000A-1359658740</ListID>
						<FullName>OP</FullName>
					</ClassRef>
					<Amount>15885.00</Amount>
					<SalesTaxCodeRef>
						<ListID>20000-1033839081</ListID>
						<FullName>Non</FullName>
					</SalesTaxCodeRef>
				</InvoiceLineRet>
			</InvoiceRet>

Thanks for the help!

May 2, 20196 yr

It's strangely coincidental that the value of Quantity is 0.6. Have you checked the Form Editor in QB to be sure that the "% XXX" columns are really custom fields and not just labels? I've never encountered a situation where Custom Fields defined within the QB UI ( or via the plug-in ) have failed to return the DataExt node(s) when OwnerID = 0 and the DataExtValue is not empty.

HTH!

Geoffrey Gerhard
Creative Solutions Incorporated
14000 Creekside Drive
Matthews, NC 28105
704.814.6852