I am using java (+Axis) API to query Netsuite. I want to do query against multiple entities (like Customer, Journal Entry, etc.,) based on a list of fields. In other words, it’s like doing a query based on meta-data.
For example, I want to run a query against Journal Entry, and I would like to query specifically for the following fields:
internalId
postingPeriod
tranDate
currency
Likewise, I would do query against other entities with a list of fields. After reading Netsuite webservices documentation and numerous suiteflex webservices user group threads I figured that I should use advanced search to query for a specific list of fields. But I am not able to get the code to work – as intended, i.e. query Journal Entry and get a specific list of fields.
I would greatly appreciate if you could provide a bit of sample code. Thanks a ton. Cheers.
Please scroll down to see the code and the issue I am facing….
Fyi – here’s my code:
this.login(true);
_console.info("\nFetching Journal entries...");
TransactionSearchAdvanced tsa = new TransactionSearchAdvanced();
TransactionSearchRow tsr = new TransactionSearchRow();
TransactionSearchRowBasic tsrb = new TransactionSearchRowBasic();
SearchColumnSelectField[] postingPeriod = new SearchColumnSelectField[1];
postingPeriod[0] = new SearchColumnSelectField();
tsrb.setPostingPeriod(postingPeriod);
SearchColumnSelectField[] internalId = new SearchColumnSelectField[1];
internalId[0] = new SearchColumnSelectField();
tsrb.setInternalId(internalId);
SearchColumnDateField[] tranDate = new SearchColumnDateField[1];
tranDate[0] = new SearchColumnDateField();
tsrb.setTranDate(tranDate);
SearchColumnSelectField[] currency = new SearchColumnSelectField[1];
currency[0] = new SearchColumnSelectField();
tsrb.setCurrency(currency);
tsr.setBasic(tsrb);
tsa.setColumns(tsr);
TransactionSearch ts = new TransactionSearch();
TransactionSearchBasic tsb = new TransactionSearchBasic();
SearchEnumMultiSelectField semsf = new SearchEnumMultiSelectField();
semsf.setOperator(SearchEnumMultiSelectFieldOperator.anyOf);
semsf.setSearchValue(new String[] {TransactionType.__journal});
tsb.setType(semsf);
ts.setBasic(tsb);
tsa.setCriteria(ts);
SearchResult result = _port.search(tsa);
RecordList recordList = result.getRecordList();
if ( recordList == null )
{
_console.info("\n RecordList object returned by SearchResult::getRecordList() is null.");
return;
}
if ( result.getTotalRecords() == 0 )
{
_console.info("\n No records found.");
return;
}
NOTE: The code falls into the first if condition - RecordList object returned by SearchResult::getRecordList() is null. This is totally misleading because when I grab the inbound and outbound soap packets via Apache Axis Log I get the following:
SOAP Request Packet:
<?xml version="1.0" encoding="UTF-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<soapenv:Header>
<ns1:preferences soapenv:actor="http://schemas.xmlsoap.org/soap/actor/next" soapenv:mustUnderstand="0" xmlns:ns1="urn:messages.platform.webservices.netsuite.com">
<ns2:ignoreReadOnlyFields xmlns:ns2="urn:messages_2012_2.platform.webservices.netsuite.com">true</ns2:ignoreReadOnlyFields>
</ns1:preferences>
<ns3:searchPreferences soapenv:actor="http://schemas.xmlsoap.org/soap/actor/next" soapenv:mustUnderstand="0" xmlns:ns3="urn:messages.platform.webservices.netsuite.com">
<ns4:bodyFieldsOnly xmlns:ns4="urn:messages_2012_2.platform.webservices.netsuite.com">false</ns4:bodyFieldsOnly>
<ns5:returnSearchColumns xmlns:ns5="urn:messages_2012_2.platform.webservices.netsuite.com">true</ns5:returnSearchColumns>
<ns6:pageSize xmlns:ns6="urn:messages_2012_2.platform.webservices.netsuite.com">1000</ns6:pageSize>
</ns3:searchPreferences>
</soapenv:Header>
<soapenv:Body>
<search xmlns="urn:messages_2012_2.platform.webservices.netsuite.com">
<searchRecord xsi:type="ns7:TransactionSearchAdvanced" xmlns:ns7="urn:sales_2012_2.transactions.webservices.netsuite.com">
<ns7:criteria xsi:type="ns7:TransactionSearch">
<ns7:basic xsi:type="ns8:TransactionSearchBasic" xmlns:ns8="urn:common_2012_2.platform.webservices.netsuite.com">
<ns8:type operator="anyOf" xsi:type="ns9:SearchEnumMultiSelectField" xmlns:ns9="urn:core_2012_2.platform.webservices.netsuite.com">
<ns9:searchValue xsi:type="xsd:string">_journal</ns9:searchValue>
</ns8:type>
</ns7:basic>
</ns7:criteria>
<ns7:columns xsi:type="ns7:TransactionSearchRow">
<ns7:basic xsi:type="ns10:TransactionSearchRowBasic" xmlns:ns10="urn:common_2012_2.platform.webservices.netsuite.com">
<ns10:currency xsi:type="ns11:SearchColumnSelectField" xmlns:ns11="urn:core_2012_2.platform.webservices.netsuite.com"/>
<ns10:internalId xsi:type="ns12:SearchColumnSelectField" xmlns:ns12="urn:core_2012_2.platform.webservices.netsuite.com"/>
<ns10:postingPeriod xsi:type="ns13:SearchColumnSelectField" xmlns:ns13="urn:core_2012_2.platform.webservices.netsuite.com"/>
<ns10:tranDate xsi:type="ns14:SearchColumnDateField" xmlns:ns14="urn:core_2012_2.platform.webservices.netsuite.com"/>
</ns7:basic>
</ns7:columns>
</searchRecord>
</search>
</soapenv:Body>
</soapenv:Envelope>
SOAP Response Packet:
<platformCore:searchRowList>
<platformCore:searchRow xsi:type="tranSales:TransactionSearchRow" xmlns:tranSales="urn:sales_2012_2.transactions.webservices.netsuite.com">
<tranSales:basic xmlns:platformCommon="urn:common_2012_2.platform.webservices.netsuite.com">
<platformCommon:currency>
<platformCore:searchValue internalId="1"/>
</platformCommon:currency>
<platformCommon:internalId>
<platformCore:searchValue internalId="16"/>
</platformCommon:internalId>
<platformCommon:tranDate>
<platformCore:searchValue>2011-04-01T00:00:00.000-07:00</platformCore:searchValue>
</platformCommon:tranDate>
</tranSales:basic>
</platformCore:searchRow>
------------
------------
------------
------------
------------
<platformCore:searchRow xsi:type="tranSales:TransactionSearchRow" xmlns:tranSales="urn:sales_2012_2.transactions.webservices.netsuite.com">
<tranSales:basic xmlns:platformCommon="urn:common_2012_2.platform.webservices.netsuite.com">
<platformCommon:currency>
<platformCore:searchValue internalId="4"/>
</platformCommon:currency>
<platformCommon:internalId>
<platformCore:searchValue internalId="219"/>
</platformCommon:internalId>
<platformCommon:postingPeriod>
<platformCore:searchValue internalId="4"/>
</platformCommon:postingPeriod>
<platformCommon:tranDate>
<platformCore:searchValue>2011-04-09T00:00:00.000-07:00</platformCore:searchValue>
</platformCommon:tranDate>
</tranSales:basic>
</platformCore:searchRow>
</platformCore:searchRowList>
Issues:
- In the code recordlist happens to be null. Wonder why? The same code actually generates valid SOAP request and I do get a valid SOAP response.
- When I run the SOAP packets thru SOAPUI I get the response as pasted above.
o 16 records are returned instead of 4. In the Netsuite UI I can see that there are only 4 Journal records.
o Posting period is not returned for all the records. Posting period value (the date value) is not returned - only internalId for the posting period is returned.
o Currency value is not returned at all.
Please help. Thanks a ton. Cheers.