Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
Click Here
|
Buy Support
Incidents |
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
Click Here
|
|
Import Pending Builds |
Author |
Message |
|
Posted : 2006-12-11 08:27:59 |
We're using QODBC V7 to link Excel and Access to data in QB Premier Mfg 2006. Our current goal is to periodically import all new records into the BuildAssembly table.
The schema shows "False" for Mod and Ins attributes for the "IsPending" field, but can I disregard this during a procedure that would a) first wipe out all existing builds and pending builds, then b) import an entirely new set of builds from Excel (preferred) or from Access, while letting QB automatically decide which to mark as Pending?
(Note: when inventory quantities are insufficient for the build, QB asks if we would like to make it pending, and we always respond "Yes.")
If so, could you recommend the easiest ways to go about this sort of mass deletion followed by mass import? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-12-11 08:56:46 |
Using QODBC v7.00.00.194 (or later) and QuickBooks 2006 (or 2007) you can now Build Assemblies using a very simple INSERT statement. For example:
INSERT INTO BuildAssembly (ItemInventoryAssemblyRefFullName, RefNumber, QuantityToBuild) VALUES ('Interior Door kit', 'QODBC1', 2)
which results in the following Build being created in QuickBooks:
I would suggest you create just the new builds, instead of deleting and starting again. |
|
|
|
|
Posted : 2006-12-11 09:13:07 |
Thanks, Tom. I should add that when the new set of BuildAssembly records is generated and ready to be imported, it will already contain not only the new builds but also any pre-existing builds with certain changes we make. That is, the newly generated import file will contain everything, up-to-date.
The reason we're doing this is to avoid having to manually update the builds table at all since it is an extremely slow process in the QB interface. Hence the requirement to delete all and replace with all new data. Is there a recommended method or line of code that could be used to wipe out all records in the BuildAssembly table? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-12-11 11:09:45 |
To delete my example build I would do:
delete from BuildAssembly where RefNumber='QODBC1' |
|
|
|
|
Posted : 2006-12-11 12:02:02 |
Thanks Tom. I'll try that with a batch procedure to delete all builds.
Regarding an alternative approach we're trying using QODBC link to Access, is there a recommended way to delete all builds through the QODBC driver from within the Access table view? |
|
|
|
Tom |
|
Group | : Administrator |
Posts | : 5510 |
Joined | : 2006-02-17 |
|
Profile |
|
Posted : 2006-12-11 13:37:04 |
You would run:
DoCmd.RunSQL "delete from BuildAssembly where RefNumber='QODBC1'" |
|
|
|
|