Country name of destination

Kinetic ERP
1

Hello everyone,

I want to create a BAQ that includes the country name of the order’s destination. The ultimate goal is a sales report based on the AR invoice.
However, I ran into one difficulty. I need the destination country name. I found out that OrderRel_OTSCounrtyNum had the field values I wanted. But that field is just a number, and I need a Description.
I want to create a SQL that will fetch this CountryNum into Description.
Please help me. Thank you very much.

image
image1477×1249 71.1 KB

2

Hi there

The information you need should be in the Country table. If you join that to your query then the Description field gives you the country name.
Sometimes the field help gives a bit of additional information to help locate the data you are looking for.

image

Does that help?

2 Likes
3

Hi,

As Gill said you can link the country table for the country name, I don’t think you need to use a calculated field as per your screenshot.

  1. Join the Country table to the OrderRel table on Company = Company and OTSCountryNum = CountryNum

TableJoin
TableJoin1107×910 227 KB

  1. Select the ‘Description’ field from the Country table

Fields
Fields1249×730 86.8 KB

  1. Check results, this should give you country name:

Results
Results831×497 35.9 KB

One other thing might be worth checking is that I think the OrderRel.OTSCountryNum field will only be populated if you are using One Time Ship-To addresses on the order. If you are using saved Ship To addresses I think you will need to look at another table for the address details.

2 Likes
4

Sorry, I didn’t tell you about my Phrase Build situation earlier.
I want to use InvcHead to represent one line per Invoice. Total amount and some additional information related to Total, etc…
When I connect OrderDtl, the lines increase as many as the number of Dtl.
So I’m looking for another way.

image
image1455×876 72 KB

5

Actually, the field I want is the blue one in the screenshot, in Order Releases. But, I couldn’t check the DB Field in the field help.

image
image1139×682 98.9 KB

6

There is also the field OrderRel.OTSCountryNum but it only seems to be populated if you have entered a different OTS on the release compared with the OTS input into the order header.

1 Like
7

Can you share a bit more information about your order/shipping/invoice process, particularly:

  • Do you ship your whole order to the same address, or can there be different addresses at line or release level?
  • Do you have multiple shipments per invoice? If so, do these all go to the same shipping address? If not, how will you decide which address to use in your report.

If you have a simple process where you have one shipment for the whole order to the same address, and then invoice the complete shipment, then I think there are only three places to look at the for the address, all at header level.

  1. The ShipTo table if you have saved shipping addresses
  2. The Customer table if the the order is shipped to the customers invoice address
  3. The OrderHed table if you are using One Time Ship To’s

Sorry I’m a bit rusty on this, a few years ago we added custom address fields to our ShipHed table which automatically capture the address information for all our scenario’s, making it easy to report on, but harder for me to remember how we did it before.

2 Likes
8

We ship whole order to the same address.
We have multiple shipments per invoice, but these all go to the same shipping address.
In fact, my now retired colleague implemented exactly the country name I wanted.
The BAQ below is his. I don’t have contact with him, so I can’t ask him.
I hope this helps.

image
image1965×1111 153 KB

image
image1576×1317 180 KB

1 Like
9

Thanks for sharing :+1:

1 Like
10

I read this thread because the title stirred up some anger in me about addresses lol.

Recent conversation I had:

  • Plant controller: Can you give me a list of invoices for the last X months.
  • Me. No problem, here you go.
  • Controller: Can you add the ship-to address
  • Me: :man_facepalming: :angry: :sob:

I’d love to be corrected, but addresses are just a nightmare with orders/shipments/invoices.

Here’s the BAQ I made:

image
image809×665 28.8 KB

Then 6 calculated fields like this:

image

And the 7th - country - being the oddball:

image

This is what everyone has been saying basically, but to put it all together:

  1. If the order has a “one-time ship-to,” then the address info is on the OrderHed table
  2. If the order has a ship-to selected, you need the ShipTo table for the data
  3. If you have no ship-to selected and it is not a “one-time ship-to” … you actually do have a ship-to - its ID is "" [empty string].
    a. (You can ignore this advice; it’s covered by #2.)
  4. City is free text in 1 and 2
  5. So is State/Province, ZIP, etc.
  6. But Country is text in the ShipTo table and an integer in the OrderHed table.
    a. Thus you need to join in the Country table for the sake of one-time ship-to.
4 Likes
11

Had the same problems here with OTS/non-OTS stuff in reporting…and you covered it well.

1 Like
12

Thank you (I guess) for the confirmation.

This reminds me of another implied point - one that really seems like a bug (I know, it’s not):

The ship-to address can be changed at any time, so if you reprint an old invoice, it can lie to you about where the order was actually shipped to.

For that reason and just the headache of it all, why is the address info not just ALL stored in the order or pack slip etc.?

I can hear the rebuttal - because the code probably looks at the ship-to ID to see if it can combine multiple lines/orders on one one pack slip or if it needs to be split up. Also ship-to IDs can carry Tax Liability info.

But that’s not really enough of a reason, IMHO.

1 Like
13

Depends on your settings. If the OTS is set to be saved, you can get it from the ShipTo table.

1 Like
14

Oy, yes, touché.

But programmatically it’s no different, right? You must create an ID for that saved ship-to, so it behaves like a stored ship-to - because it is now.

Hmm - well, does it then uncheck the UseOTS flag though?

15

Which is actually the address on the main Customer page.

1 Like
16

I am not sure about the triggers that happen with OTS. I have not really dug in to see what happens when you use OTS with saving as a ST and OTS without saving.

2 Likes