| Update Item Sales/COGS Account assignment |
| Author |
Message |
| jojo |
| |
| Group | : Members |
| Posts | : 6 |
| Joined | : 2008-09-24 |
|
| Profile |
|
| Posted : 2008-09-24 23:29:35 |
We have many Items which belongs to different Item Types (parts assemblies) that was previously assigned
wrong Sales / COGS / Asset accounts.
Updating each one manually is time consuming, plus we need to open QB in single user mode.
While updating manually its prompting us to cascade the changes to previous period transaction which is OK with us.
Is there a way to do it using QODBC?
Thanks. |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-25 08:18:32 |
Yes, you can update the COGSAccountRefFullName values of the ItemInventory and ItemInventoryAssemblyLine tables directly using QODBC. But IncomeAccountRefFullName can't be updated. |
|
|
|
| jojo |
| |
| Group | : Members |
| Posts | : 6 |
| Joined | : 2008-09-24 |
|
| Profile |
|
| Posted : 2008-09-25 17:43:46 |
Will it have retro-active effect on the old transactions in QB?
Like in the attached screenshot, we wanted to say 'YES' ,
so that even old transactions will be affected.
We have one year worth of data on this QB file.
  |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-26 08:12:06 |
| No, the transactions would need to be "UPDATE"ed also. |
|
|
|
| jojo |
| |
| Group | : Members |
| Posts | : 6 |
| Joined | : 2008-09-24 |
|
| Profile |
|
| Posted : 2008-09-27 18:39:25 |
Thanks Tom..
Is it just the 'Transaction' table that needs to be updated?
|
|
|
|
| jojo |
| |
| Group | : Members |
| Posts | : 6 |
| Joined | : 2008-09-24 |
|
| Profile |
|
| Posted : 2008-09-27 21:25:48 |
Hi Tom,
As i was trying to update the Items' account assignments.. i noticed that u mentioned in your previous reply
"you can update the COGSAccountRefFullName, IncomeAccountRefFullName values of the ItemInventory and ItemInventoryAssemblyLine tables directly using QODBC."
Should'nt I be updating COGSAccountRefListID and IncomeAccountRefFullName instead?
|
|
|
|
| jojo |
| |
| Group | : Members |
| Posts | : 6 |
| Joined | : 2008-09-24 |
|
| Profile |
|
| Posted : 2008-09-28 18:28:52 |
I'm getting "Field Not Allowed In Update" error when updating 'IncomeAccountRefListID' or 'IncomeAccountRefFullName'
of ItemInventory table.
sp_column shows that those column is not updateable :(
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-28 18:44:56 |
| You must be using an old version of QuickBooks. My sp_column lookup using QuickBooks 2007 shows no update restrictions. |
|
|
|
| jojo |
| |
| Group | : Members |
| Posts | : 6 |
| Joined | : 2008-09-24 |
|
| Profile |
|
| Posted : 2008-09-29 22:59:09 |
Tom,
I have updated the QB7 Ent - Manufacturing Ed with the latest patch R11P, both on the server and client.
Still I'm getting same results with sp_columns iteminventory.
IncomeAccountRefListID
IncomeAccountRefFullName
still shows not updateable.
|
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2008-09-30 09:07:32 |
Sorry, I was going off memory, IncomeAccountRefListID and IncomeAccountRefFullName are not updatable, but COGSAccountRefListID and COGSAccountRefFullName are updatable.
The InvoiceLineOverrideItemAccountRefFullName is updatable in the InvoiceLine table. |
|
|
|
