In my Headquarters Database, I have several items where the HQID=0. In the Store Operations Database, I also have some items that have the HQID=0. How do I fix this so that the items at the store match up with whats in HQ?
In HQ database, HQID isn't important, but for the stores, HQID is essential or else u will not be able to make any updates for these items from HQ or even you'll not be able to view sales or inventory history for these items.
To fix this problem, export the item table from hq to the intended store as ItemX, then run the following query in the store db
***************************** update item set item.HQID= ItemX.ID from ItemX where item.Itemlookupcode=ItemX.Itemlookupcode ************************************* this way u'll fix the item HQID problem based on your itemlookupcode which should be unique, now u have to check your hq database don't have lost sales that might point to those items ******************** select * from transactionentry where itemid=0select * from Inventorytransferlog where itemid=0
*************************** if u get some records for the previous 2 queries, please let me knowNashat
Could you check that there is no record in the itemdynamic table does not refer to the item table,
Select * From ItemDynamic where itemid not in (select id from item)
select * from item where id not in (select itemid from itemdynamic)
these two statement check the sychronize of item table , if you get any result record you must fix it , I recommend NASHAT syntax that were send before.
"Anthony Laidler" wrote in message news: snipped-for-privacy@microsoft.com...
Or the simplest way if it's only a few items... add the items in HQ then send them down to the stores :)
cosmicpinball, but this way he'll be losing any sales or inventory history that is already related to those items
You mentioned exporting the item table from hq to the intended store as ItemX...How do I do that?
I did the statements as you suggested...When I did: select * from transactionentry where itemid=0
It returned 891 items across my three stores...
The other statement returned 0 Hits.
"Nashat" wrote:
else u will not be able to make any updates for these items from HQ or even you'll not be able to view sales or inventory history for these items.
ItemX, then run the following query in the store db
should be unique, now u have to check your hq database don't have lost sales that might point to those items
Sorry for the big big delay, can u plz run this staement in the store to make sure we r on the right path
*************** select item.itemlookupcode,item.description,Item.HQID from item left join OPENDATASOURCE( 'SQLOLEDB', 'Data Source=your HQ IP;User ID=sa;Password=xxxx' ).HQ DB Name.dbo.Item RemoteItem on Item.ItemLookupCode=RemoteItem.ItemLookupCode where Item.HQID=0**************************************
"Anthony Laidler" wrote in message news: snipped-for-privacy@microsoft.com...
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.