passive link quickbooks sales orders

[ketoman]

I am building a shop tracking database and would like to link QB open sales orders with an Alpha table. When viewing QB tables salesorder salesorderline seem like what I need, but locks up my pc when trying to create passive link tables. I believe due to massive amount of orders. Is there a way to get this data in a more stream lined fashion (I am already picking only necessary fields). I need really only the info as viewed on the QB sales order form (which I would like to duplicate somewhat in Alpha V)

[Al Buchholz]

Re: passive link quickbooks sales orders

double posting edited out..

[Al Buchholz]

Re: passive link quickbooks sales orders

I am building a shop tracking database and would like to link QB open sales orders with an Alpha table. When viewing QB tables salesorder salesorderline seem like what I need, but locks up my pc when trying to create passive link tables. I believe due to massive amount of orders. Is there a way to get this data in a more stream lined fashion (I am already picking only necessary fields). I need really only the info as viewed on the QB sales order form (which I would like to duplicate somewhat in Alpha V)

Is you PC really locked up or is the process working and taking longer than you expect? Check task manager and see what is actually running.. QB does not give up it's data easily - but it can be made to run fairly quickly with tuning.

Using QODBC also builds an internal optimizer database that makes the second and subsequent refreshes faster.. So the first load will take longer than others that follow.

What number is massive? 100 1,000 10,000 100,000 1,000,000

ie how many order lines are in your QB company file?

If you need line detail for the order, then you only need the salesorderline table. It's already including the header and line info.

When you build your passive link SQL select, include only the fields that you need rather than the select * which gets all fields. (You may be doing this already..)

If you really don't need all of the history, add a where clause to SQL select and filter only the rows that you need..

I'd also suggest that you build a QB company file - sandbox - that mirrors your QB company file ( but has little to no data in it) and you can build your SQL with that company file open... It's much faster to work with. Then when you need to actually get data, open the live company file.

[ketoman]

Re: passive link quickbooks sales orders

"I'd also suggest that you build a QB company file - sandbox - that mirrors your QB company file and you can build your SQL"

Thanks Al
Upwards of 10,000 for this year, although I would like to grab only open orders from QB so maybe 1900 at the moment. Is there a method of filtering the salesorderline to get open only?

what do you mean by sandbox above, and how would I go about this?

Thanks again

[Al Buchholz]

Re: passive link quickbooks sales orders

"I'd also suggest that you build a QB company file - sandbox - that mirrors your QB company file and you can build your SQL"
what do you mean by sandbox above, and how would I go about this?

sandbox - a play area that doesn't effect the live system. Also known as - development/testing database environments.

Upwards of 10,000 for this year, although I would like to grab only open orders from QB so maybe 1900 at the moment. Is there a method of filtering the salesorderline to get open only?

Thanks again

Look at the ismanuallyclosed and isfullyinvoiced fields to start.

One of the nice things about QB is that it is really flexible. Of course that's also one of the drawbacks..

I usually talk to a client and figure out their processes and then determine how and when an order is closed. It varies from client to client.

[ketoman]

Re: passive link quickbooks sales orders

I am giving it shot now, still taking a while, I will see if I can do some more trimming. Is there an argument for just Salesorders that are new or changed since last refresh?

[Al Buchholz]

Re: passive link quickbooks sales orders

I am giving it shot now, still taking a while, I will see if I can do some more trimming. Is there an argument for just Salesorders that are new or changed since last refresh?

You could store the last update time and compare with the datetime that a record was modified, but there is nothing 'built-in' for that..

The QODBC optimizer database does that for you. But you then extract all of the data out of the optimized database into the QRB tables.

Remember that the first refresh is by far the longest. So I'd get past the first one and see how long it takes the second time before doing more work...

Extracting 10,000 records the first time is going to be a really long coffee break.

I've also found that a machine with 2gb or more of memory and fast hard drives can make a big difference...