1. Home
  2. Point-Of-Sale Software
  3. Linking Transaction to ShipToID

Linking Transaction to ShipToID

Hey all.

I've got a customer using RMS with multiple stores and I'm trying to report on data on the central server.

I can easily link Transaction with Customer and Store but I'm having problems linking Transactions with the ShipToID.

Should the ShipToID field in Transaction map to an ID in the ShipTo table?

because it's not so I'm wondering if there is some kind of translation table I need to use to relate them together.

Any help would be greatly appreciated here.

Thanks!

Reply to
Andrew MacNeill
Loading thread data ...

Hi Andrew - yes, [transaction].shiptoid = shipto.id

shiptoid of zero in transaction table means no ship to was assigned on that sale

shipto table links to customer table on shipto.customerid = customer.id

there's a shipp> Hey all.

Reply to
convoluted

So does that mean if there is a ShipToID value in the Transaction table that does NOT exist in the ShipTo table, then the synchronization between the two is incomplete or screwed up somehow?

It sounds like it - but I want to make sure I understand before I state that "out loud"

Thanks for the quick resp> Hi Andrew - yes, [transaction].shiptoid = shipto.id

Reply to
Andrew MacNeill

If there's a [transaction].shiptoid value that does not exist in shipto.id then the ship to record was deleted from the customer record. query the table to see which id's are no longer in the shipto table....

select shiptoid, transactionnumber from [transaction] where shiptoid not in (select id from shipto)

you could also set these nonexistent shiptoid's to zero with an update query (backup first).... update [transaction] set shiptoid = 0 where shiptoid not in (select id from shipto)

h> So does that mean if there is a ShipToID value in the Transaction table that

Reply to
convoluted

Hi

I also have some problem with the shipto table, I try to create a custom sales report in HQ which can show me the shipto infomation, I found if I connect [transaction].shiptoid = shipto.id, there are some shipto info will not show in the report.

so I looked into HQ database and found out that if the Shipto info was first time uploaded from store to HQ, the value of the field "ShiptoId" is wrong in "Transaction" table, it is the "id" value of the "Shipto" table in Store database but not in HQ database.

To Andrew: Have you work your report out? is it correct all the time with the shipto?

To convoluted: is this a bug in RMS?

Thank you very much.

"c> If there's a [transaction].shiptoid value that does not exist in shipto.id

Reply to
Kelvin

Hi

Picked up this old question because I discovered this problem too. In my case I was merging some Shipping Addresses (my client made some double addresses and wanted to compound them). I updated Transaction (&Order &TransactionHold) tables column ShipToID in SO and after 401 checked HQ database - found that it updated HQ also but with SO ShipToID. So I checked other HQ Transaction.ShipToID values and found that they all are same with SO values. Next query had no result:

select * HQBase.dbo.[Transaction] HQ join SOBase.dbo.[Transaction] SO on HQ.TransactionNumber=SO.TransactionNumber and HQ.ShipToIDSO.ShipToID and HQ.StoreID=1

As there is no reference to Transaction.ShipToID in ShipTo table in HQ you have to use a link to SO database for reports.

I made another test - changed some Shipping Address value in HQ Manager - it updated also SO values after next 401. What makes a connection between SO and HQ ShipTo tables seems to be HQID value (in HQ it equals to ID).

I think this is a bug - ShipTo update from HQ to SO seems to be working but from SO to HQ not (ShipToID value in Transaction table in HQ has no meaning within HQ Database).

Koit Lahesoo

"Kelv> Hi

Reply to
Koit Lahesoo

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.