Simon Guerrero Simon Guerrero - 5 months ago 156
PHP Question

Netsuite SuiteTalk - requesting list of invoices for a customer via PHP

I'm using the Netsuite PHP Toolkit to try to obtain a list of invoices for a customer. I can do the call (using a TransactionSearch) with no problem, but I'm struggling to understand how I'm supposed to get all details for an invoice - i.e. the invoice "header" details (e.g. grand total, currency, main menu line etc) as well as details for each line item (net value, taxable value, item etc).

I have tried a couple of approaches:


  1. TransactionSearchAdvanced, with return columns specified and returnSearchColumns preference set to "false". This gives back all the separate lines (woo!) but things like currency and term aren't expanded out - you just get internalId specified and not the actual text (or the symbol). Also, with TSA, do you really have to specify every column you want? i.e. is the default really just an empty set of fields? Isn't there a way of just saying "give me all the details for all lines of each invoice?

  2. TransactionSearch, with returnSearchColumns preference set to "true". This gives a list of single Invoice type records, with all the currency and term stuff correctly populated, but frustratingly, none of the individual line items. It's more of a summary.



So I am left with a couple of options, neither of which are very palatable, namely:


  1. Do both calls for all invoices and combine the data. These searches take a long time (performance is another bugbear for me, so I really don't want to do this.



or


  1. Figure out a way of requesting the data for terms, currency etc and also a way of obtaining invoice lines.



I have no idea how you're supposed to do this, and can't find anything on the internet about it. This is one of the worst interfaces I've used (and I've used some pretty bad ones).

Any help would be hugely appreciated.

Simon

Answer

Just like you I started out trying to do things with the Web Services API (aka SuiteTalk). Mostly it was an exercise in frustration because eventually what I found out was that I plain couldn't do what I wanted with them. That and the performance was pretty bad, which would have killed my project even if it had worked properly.

Like Faz, I've found it much easier and faster to use a combination of RESTlets and Saved Searches than deal with the web services framework.

Basically break your problem down into these parts:

  • Saved Search that returns the results that you want (keep track of the internal ID you'll need it later)
  • RESTlet it's just a Javascript file that defines the function you will use to return the results from the search
  • Client code to call the RESTlet and get the results.

Part I: So the saved search is pretty straightforward. I'm going to assume you can make that happen and also that you can actually get all the fields you want in one place. That hasn't always been the case in my experience.

Part II: The RESTlet involves a lot more steps even though it's really a very simple thing. What makes it complicated is getting it uploaded and deployed on your NetSuite site. If you don't already have the NetSuite IDE installed I highly recommend it if only to make deploying the scripts a little easier. The autocompletion and tooltips are extremely useful as well.

For instance here is code I use to get results from a search I cared about. This was adapted from some kind soul's posting somewhere on the internet but I forget where:

function getSearchResults(){
var max_rows = 1000;
var search_id = 1211;
var search = nlapiLoadSearch(null, search_id);
var results = search.runSearch();
var rows = [];

// add starting point for usage
var context = nlapiGetContext();
startingUsage = context.getRemainingUsage();
rows.push(["beginning usage", startingUsage]);

// now create the collection of result rows in 1000 row chunks
var index = 0;
do{
    var chunk = results.getResults(index, index+1000);
    if( ! chunk ) break;
    chunk.forEach( function(row){
        rows.push(row);
        index++;
    });
}while( chunk.length === max_rows);

// add a line that returns the remaining usage for this RESTlet
context = nlapiGetContext();
var remainingUsage = context.getRemainingUsage();
rows.push(["remaining usage",remainingUsage]);

// send back the rows
return rows;
}

This is where you get things primed by passing in your Saved Search Internal ID:

var search = nlapiLoadSearch(null, SEARCH_ID);
var resultSet = search.runSearch();

Then the code repeatedly calls getResults() to get chunks of 1000 results, this is a NetSuite limitation. Once you have this written you have to upload the script to NetSuite and configure and deploy it. The most important part is telling it what function to assign to each verb. In this case I assigned GET to execute the getSearchResults. There is a lot of work to do here, and I'm not going to type all of it out because it is worth your time to learn this part. At least enough to get the IDE to do it for you =D. You can read all about it in the "Introduction to RESTlets" guide.

Part III. Client code can be in whatever you want that does REST the way you like to. Personally I like Python for this because the requests library is fantastic. Here's some example Python code:

import requests
import json
url = 'https://rest.sandbox.netsuite.com/app/site/hosting/restlet.nl?script=123&deploy=1'
headers = {'Content-Type': 'application/json', 'Authorization':'NLAuth nlauth_account=1234567, nlauth_email=someone@somewhere.com, nlauth_signature=somepassword, nlauth_role=3'}
resp = requests.get(url, headers=headers)
data = resp.json()

The URL is going to be displayed to you as part of the deployment of the RESTlet. Then it's up to you to do what you want with the data that comes back.

So the things I would suggest you spend time with would be

  • Setting up the NetSuite IDE
  • Getting and reading the SuiteScript developer reference docs
  • Finding a good way to create REST client code in you language of choice.

I hope that helps.