1. Home
  2. Point-Of-Sale Software
  3. How to get the Shipto information from RMS HQ

How to get the Shipto information from RMS HQ

I try to create a custom detail sales report for HQ Manager which can show me the shipto infomation for customer. normaly I just need to connect the "Shipto" table with "Transaction" table by the field "ShiptoId" in Transaction table, and then I can get the field I need from the Shipto table in HQ database, but this time I found out there is a problem in RMS HQ database. The field "ShipToId" in "Transaction" table which should point to the "HQID" field in "ShipTo" table, the truth is if this shipto infomation is the first time created, it will point to the id field of the Shipto table in Store database but not the Id field in HQ database, if it is not a new shipto infomation, it will be OK, so I can't make this report work. is there someone can help me about this , thanks .

Reply to
Kelvin
Loading thread data ...

Kelvin - if you are using SHIFT F1 to track sand charge hipping fees at point of sale then you can pull the ship to info from the shipping table - edit the detailed sales report by adding a join to the shipping table

LEFT JOIN Shipping WITH(NOLOCK) ON [Transaction].TransactionNumber Shipping.TransactionNumber

Then add something like this to view the shipping address on each item - I think you said you wanted to edit the detailed sales report, right?

Begin Column FieldName = "ShipTo" DrillDownFieldName = "" DrillDownReportName = "" Title = "Ship To Address" VBDataType = vbString Formula = "Shipping.Name+' '+Shipping.Address+' '+Shipping.Address2+' '+Shipping.City+', '+Shipping.State+' '+Shipping.Zip" ColHidden = False ColNotDisplayable = False FilterDisabled = False ColWidth = 2500 GroupMethod = groupmethodNone ColFormat = "" End Column

Hope this helps.....

"Kelv> I try to create a custom detail sales report for HQ Manager which can show me

Reply to
convoluted

Thank you for your quick reply.

a correct report need a correct database, but I think there is a problem in RMS HQ database.

In RMS Pos, after you select a customer, press Ctrl + F7, you can create a "shipto" for the customer and assign to the transaction. these shipto infamation will be upload to HQ database after the next 401 worksheet runs. I try to create a report in HQ to show these "ShipTo" infomation. After 2 day's test, I found that I need to run two 401 worksheet for the store to upload the shipto infomation to HQ, the first 401 worksheet will upload the whole transaction to HQ database, but if at this time you look at the "ShiptoId" field in HQ database, you will find that the value is wrong, it is the id value of the store database, after another 401 worksheet run (the" from date" for this worksheet must before the transaction's create time), you can see the correct value in "Shiptoid" field in Transaction table in HQ database.

so the problem is -- RMS need to run two 401 worksheet to upload the correct ShiptoId in transaction table to HQ database. that means you can't get the correct "ShiptoId" value in HQ database before the second worksheet happens. is this a bug for RMS?

you can try to create a ShipTo in store database and then run a 401 worksheet (only one) and check the field "ShiptoId" field in Transaction table in HQ database.

"c> Kelvin - if you are using SHIFT F1 to track sand charge hipping fees at point

Reply to
Kelvin

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.