[al3xicon]

Hi all –

I am trying to query the transactions and transaction_lines tables for item fulfillments; what I want is to report on, for each item, how many have been fulfilled over the past 365 days. However, the ODBC connection seems to return multiple lines per item – e.g. if qty of 2 items were fulfilled on a transaction, there could be a line for that item showing +2, then a line showing -2, then a line showing +2 again. However, unfortunately this is not regular and seems to be random – e.g. if you simply SUM all the line quantities grouped by item, some totals result in a positive amount, some in a negative amount, and some in a zero amount. Sadly when comparing to actual fulfillment records in NetSuite, these quantities do not always match, so my reporting is wildly inaccurate.

Does anyone know how to reconcile this sort of a query?

Thanks in advance,

Alex
This is a cached copy. Click here to see the original post.

[JCirocco]

Go after the Quantity Rcvd/Fulfilled. I believe that is only populated on the “prper” fulfillment line. It is also the 1 line out of the 3 that the account field is null

[al3xicon]

@ J2 – thanks, that did the trick! Couldn’t get myself outside of the box that time, cheers.

[Author]

Posts