newbie passive link issue

[ketoman]

I am building a tracking data base for sales orders received in, entered into QuickBooks to go out to our mfg. floor, and track product thru all stages of production. After messing around with the QODBC I have found it to be a pain. So I have created a report in QB to export all needed data in excel file (customer, product, due date, etc.) I have a passive link table (Transfer) in Alpha. I now need to link this (transfer table) with multiple(customer, product, and sales order)tables so that inventory, customer, quote tables can be used in the standard set fashion. I am trying to bring data in to populate customer, product, and sales order tables. And only update data that is new or changed within the excel file thru the transfer table. I have tried mapping and sets to do this, but I must be missing the boat because this does not function for me correctly. The sets i have built seem to be only one way parent table (transfer) does not populate child table for some reason. Am I just missing some thing right in front of me?

[Al Buchholz]

Re: newbie passive link issue

I am building a tracking data base for sales orders received in, entered into QuickBooks to go out to our mfg. floor, and track product thru all stages of production. After messing around with the QODBC I have found it to be a pain. So I have created a report in QB to export all needed data in excel file (customer, product, due date, etc.) I have a passive link table (Transfer) in Alpha. I now need to link this (transfer table) with multiple(customer, product, and sales order)tables so that inventory, customer, quote tables can be used in the standard set fashion. I am trying to bring data in to populate customer, product, and sales order tables. And only update data that is new or changed within the excel file thru the transfer table. I have tried mapping and sets to do this, but I must be missing the boat because this does not function for me correctly. The sets i have built seem to be only one way parent table (transfer) does not populate child table for some reason. Am I just missing some thing right in front of me?

I found that using QODBC is the answer to the type of issues that you are raising.

Working with QB data is not like working with most other databases. But then again I don't drive a car like I fly a plane, and I certainly don't fly a plane like I drive a car.

So what are the issues that you have with QODBC that are causing you trouble?

What flavor of QB(year/country/line of business) and what version of QODBC do you have?

edit: Maybe you should download a trial of QReportBuilder. It's an Alpha5 application written to report from QB. It'll help you understand what QODBC does to help with QB data.

[ketoman]

Re: newbie passive link issue

First we are a small company one copy of 2009 QB prem. MFG edition, in accounting office, I am writing application in another office, server is Linux based. QB will not run or even let the company file reside there (just another of proprietary BS of QB). QB must be linked directly to the accounting pc with QODBC and found that the RDS client server does not work on a regular basis due to the fact the client cannot find the RDS server 95% of the time. I have found the fix to this is to change our network structure. That is not going to happen. The server works for all other software except RDS system. It is very fast and very stable. We need only one way data out of QB and into Alpha. The data needed is basically everything found on the QB sales order page (which is a set made from many tables and sub-tables)this seems to be an amazingly tedious task to link. Which is why I opted for the excel route. If there is a better way I am all ears or eyes in this case. I am mfg engineer who believes in keeping things simple.

[Al Buchholz]

Re: newbie passive link issue

To connect to QB with QODBC I have found the most reliable way is to have a copy of the QB client software on the windows machine that is running Alpha and QODBC.

Then I can connect either with the QB company file that is currently open by QB.

Or provide the actual company file name to the QODBC driver and connect without the QB program being open. This is slower at connection time, but more flexible for unattended access.

Here is some additional information that you may find helpful.

Have you looked at QReportBuilder?

[ketoman]

Re: newbie passive link issue

I have tried the demo, and this is how I found it does not work correctly with our system. Also the fact I would have to purchase another QB prem Package $400 + the Qodbc $150. Then change network around? On top of Alpha Five that the sales rep told be connected directly to QB. This just makes no sense what so ever. I need solid data 12 fields filled into Alpha to alleviate double entry into two programs which is what we do now. QB and Filemaker. And the ability to update this information. I am not trying to write back to QB. Is there anyone who has successfully done this.

[Al Buchholz]

Re: newbie passive link issue

I have tried the demo, and this is how I found it does not work correctly with our system. Also the fact I would have to purchase another QB prem Package $400 + the Qodbc $150. Then change network around? On top of Alpha Five that the sales rep told be connected directly to QB. This just makes no sense what so ever. I need solid data 12 fields filled into Alpha to alleviate double entry into two programs which is what we do now. QB and Filemaker. And the ability to update this information. I am not trying to write back to QB. Is there anyone who has successfully done this.

Keith

I'm doing this all of the time... From what I'm seeing from your responses, you are not hearing what I telling you to do.

And what does filemaker have to do with this? This is the first that you've mention it.

Slow down and do what I've mentioned previously.

[ketoman]

Re: newbie passive link issue

Al

I appreciate your help,
maybe I am missing something. I have tried till I am blue in the face to use RDS and QOBDC and cannot maintain connection. The alternative you said was keep everything on one PC that is also impossible for me. I have one copy of QB being used by accounting (second copy is not an option for us at this point)so RDS and using file on accounting is my only option. I only mentioned FileMaker because that is our current system I am leaving, to come to Alpha and I do not want to have the same problems with Alpha as I did with Filemaker.
(having to double enter everything once in QB and once in Filemaker)

I am trying to create a system where we enter data in QB and have it sent to Alpha for use there. And have it refreshable

Do you feel that it is easier to use QODBC, vs just saving a Custom Sales Order report in an excel file and passive link to an Alpha table.

[Al Buchholz]

Re: newbie passive link issue

Al

I appreciate your help,
maybe I am missing something. I have tried till I am blue in the face to use RDS and QOBDC and cannot maintain connection. The alternative you said was keep everything on one PC that is also impossible for me. I have one copy of QB being used by accounting (second copy is not an option for us at this point)so RDS and using file on accounting is my only option. I only mentioned FileMaker because that is our current system I am leaving, to come to Alpha and I do not want to have the same problems with Alpha as I did with Filemaker.
(having to double enter everything once in QB and once in Filemaker)

I am trying to create a system where we enter data in QB and have it sent to Alpha for use there. And have it refreshable

Do you feel that it is easier to use QODBC, vs just saving a Custom Sales Order report in an excel file and passive link to an Alpha table.

Yes, you want to use QODBC to read the tables from QB rather than get the data from the reports.

You are still making too many assumptions on how this will work when you don't have it working yet.

If only one copy of QB is available, then the refreshing can be done with that machine.

If cost is a factor, then your company will live with having a refresh done overnight. (Unless accounting is working 24/7). It may also be refreshable during the day. I don't know the volume of data per day and the amount of data in the QB company file.

I get the feeling that the value of this process is not understood.

I still don't know what version of QODBC you are using.

When someone continues to use the 'impossible' word and doesn't try what I suggest, I'm lost on how to help. You've really boxed yourself in a corner. You need to move out of that box or you won't get anywhere.

[ketoman]

Re: newbie passive link issue

The current version of QODBC Driver for QuickBooks is 10.00.00.265 I do not think this is a problem. I believe it is more the RDS system between client /server and the way it searches for server. It does not use IP address just DNS. At the moment I have connection to RDS server. And I see how I can access QB tables. But now do I do a passive link table ( sort of just a hand off table) or do all my tables more or less connect passively to their respective QB tables. Say I want to connect Sales Order in QB to basically a duplicate table in Alpha

[Al Buchholz]

Re: newbie passive link issue

The current version of QODBC Driver for QuickBooks is 10.00.00.265 I do not think this is a problem. I believe it is more the RDS system between client /server and the way it searches for server. It does not use IP address just DNS. At the moment I have connection to RDS server. And I see how I can access QB tables. But now do I do a passive link table ( sort of just a hand off table) or do all my tables more or less connect passively to their respective QB tables. Say I want to connect Sales Order in QB to basically a duplicate table in Alpha

You should use the passive linked tables to hold the data from QB. Passive linked are essentially imported tables.

I use the listid from QB in Alpha table to tell me which record in the QB table to connect to.

An easy way to manage that is to build a set where the QB PL table is the parent and the Alpha table is the child linked by the listed.

For dramatically improved performance only bring the columns (fields) from the QB tables that you need. And also only bring across only the rows (records) that you need. (where clause)

Watch out.. You'll actually make this work and enjoy it... ;-)

I don't use the RDS method so I can't help in improving it for you. I have too much fun with the direct method because I've found that it works. I like when it works.

[ketoman]

Re: newbie passive link issue

Thanks, I will try this out