Find all instances of 'Location' field??

ccbryan63

My client wants to consolidate their locations, rolling 15 or 16 current locations into four.

My questions to you:

1. Is there anywhere that a change in Location sets off a cascading rename like changing Item."No." does?

2. Is there any way in a native Nav database to identify all the tables where the Location field exists? My tentative plan is just to go to all these tables and change the location field values.

2a. Is this plan just silly?

2b. Is there an easier way that I haven't thought of?

Thanks all and very much.

Chandler

kriki

Well, you can do the rename of the values in the location table. If the value already exists, delete it WITHOUT triggering the OnDelete of the table. After that you can rename another location code to that code.
To make this work, ALL location fields NEED to have a tablerelation to the location-table.

Create a form on table "Field" and filter on Field name : @*location* to have a list of all locations. I don't think there are location code fields that DON'T have 'location' in them. At least NOT in the standard.

Of course, you need to test this first on a testDB before doing it in production.

ccbryan63

Renaming the location in the Location table will automatically rename all the entries etc. for that location? Sweet.

So... if I want to turn all the existing location 40 and 50 entries into location 30 I'd deactivate the ondelete() trigger in Location, delete 30, then overwrite '40' with '30' in the location table, delete 30 again, overwrite '50' with '30', and reenable ondelete(). Right?

Table 'Field'? Please clarify...

Test database DEFINITELY.

Thanks for the quick response!

Chandler

Alex_Chow

ccbryan63 wrote:

Renaming the location in the Location table will automatically rename all the entries etc. for that location? Sweet.

So... if I want to turn all the existing location 40 and 50 entries into location 30 I'd deactivate the ondelete() trigger in Location, delete 30, then overwrite '40' with '30' in the location table, delete 30 again, overwrite '50' with '30', and reenable ondelete(). Right?

Table 'Field'? Please clarify...

Test database DEFINITELY.

Thanks for the quick response!

Chandler

I believe you have to negative adjust the items out first. Or else something funny will happen with the Remaining Quantity fields in the Item Ledger Entry. You will also encounter item costing problems if you're using Average Cost.

David_Singleton

I am with Alex on this one, renaming/merging Location code is not as easy as it sounds. Its not just costing that will be an issue, even FIFO will be all out of sync. You are going to have to be very careful to correctly handle the Itme ledger entries. Take a scenario where one location was sold to negative and the other has a positive open quantity. You need to then run aroutine to apply those open entries.

Also watch out for variants and SKUs, there are some odd uses of the variant code that can cause you issues (if you are using variants OR SKUs).

Another thing is Add-Ons, I have coem across a numbr of add ons that don't create correct table relations for the Location Code. (I think because many developers think table relations are only needed if the user will have to drill down on the table) often histry type tables are not linked correctly.

Anyway just test this like crazy. It may work first time no problems, but be carefull.

Alex_Chow

David Singleton wrote:

Anyway just test this like crazy. It may work first time no problems, but be carefull.

It will work in that you will not encounter any error messages. The normal users will not notice any impact and they will get no error messages as they go about their daily lives.

The problem will arise period end when the CFO/Controller, owners, and/or auditors are looking at the numbers. Then you'll be in serious trouble.

The testing shouldn't be done by a programmer. It should be done by a controller after a period double entries to see if the numbers match up.

ccbryan63

Got it. This is a non-trivial task. Would it be a different ball game if all items were costed at standard?

Chandler

Alex_Chow

ccbryan63 wrote:

Got it. This is a non-trivial task. Would it be a different ball game if all items were costed at standard?

Chandler

No, as you still would have the remaining quantity problem to deal with.

ccbryan63

Back up towards the beginning of this thread, Kirikri said

"Create a form on table "Field" and filter on Field name : @*location* to have a list of all locations."

I would like to do this but can't find a "Field" table. Guidance please?

Thanks...

Alex_Chow

ccbryan63 wrote:

Back up towards the beginning of this thread, Kirikri said

"Create a form on table "Field" and filter on Field name : @*location* to have a list of all locations."

I would like to do this but can't find a "Field" table. Guidance please?

Thanks...

When you're creating a form from the Form Wizard, type in Field on the table you would like to use.

David_Singleton

ccbryan63 wrote:

Back up towards the beginning of this thread, Kirikri said

"Create a form on table "Field" and filter on Field name : @*location* to have a list of all locations."

I would like to do this but can't find a "Field" table. Guidance please?

Thanks...

Take a look at form 7702.

mgm

Alex, David, Anyone who thought about this or has done this before,

We are also thinking about merging 2 locations (say A and into 1 new location (C).
I don't get what the problems with Remaining Qty. and Costing exactly will be.

We were thinking about doing the following:
1. Rename Location A into C (I've done a test. The renaming part seems ok. Like Kriki's suggested, I checked all fields where with the name *@location*).
2. Use the Item Reclass. Journal to move stock from B
3. Change Location Code B to C in the open order lines

Beside this, we want to stop using SKU's. They were only used for locations, not for Variants. After merging the locations, SKU's don't make any sense anymore. Therefore we want to delete all SKU's.

Maarten

bbrown

Instead of treating this like a technology problem, why don't you approach it as an operations issue. Create 4 new locations and move the inventory.

mgm

We don't 4 new locations, but only 1.
We already move inventory to one existing location.
It's just that we like to rename that location, because the old name (code) does not make sense anymore.

DenSter

This is exactly why you should not ascribe 'meaning' to the codes of the locations. The 'Code' field is a technical attribute of the entity, it's just there to uniquely identify the record in the table, it should not hold functional meaning. If the code would just have been 'LOC01' or something meaningless, you could have just changed the name of the location and kept all inventory there.

For human beings, the name of the location should identify it. The 'Code' field is for the computer to identify it.

mgm

Daniel,

I don't completely agree; codes are shown in the order lines and everywhere else, not the name of the location. So, for users it is much easier to give a meaningful name to a code. But, I don't wanna argue about 'naming' codes here.
We are dealing with an existing situation. The customer gave meaning to the location codes in the past.
I'd like to get clear what exactly the problem is when renaming a location.

Maarten

Sog

Main problem with renaming both A and B to C:
Item Z is in stock on both A (aka C) (valued 10 €) and B (valued 8 €)
last movement of Z in B: 31/07/2011
last movement of Z in A (aka C): 29/08/2011 (sale) -> this has been applied with a purchase in A on 25/08/2011
Rename B to C and suddenly an open entry before the latest open entry appears -> costing is fubar.

I'd suggest to rename one location (the largest preferred) and have a transfer order take care of the other location.
When stock on small location = 0 -> remove location.

DenSter

mgm wrote:

I don't completely agree; codes are shown in the order lines and everywhere else, not the name of the location. So, for users it is much easier to give a meaningful name to a code.

I agree, you want to focus on your problem at hand, but I want to just add something, because often customers have meaningful names because that's how their NAV partners set it up.

People are smart enough to know that location ABC123 is the location in Delft, and they will be able to handle that when they need to move inventory to a larger facility in Rotterdam, that the location code can still be the same. Now the location move is just a logistical matter. Move all the inventory to the new building and you're done.

By having 'meaningful' codes, all of a sudden location DELFT no longer exists and on top of the move now you also have an IT problem because you have to change all existing codes to RDAM.

People have absolutely no problem remembering that ABC123 is 'the warehouse that used to be in Delft but we had to move to a new warehouse in Rotterdam because our business is doing so well and by the way did you see that building? it's amazing!'. For some reason they can't deal with location DELFT being in a different city.

I think the best approach would be to create the new locations and use NAV functionality to actually transfer the inventory.

mgm

Daniel,

Thanks for the reply and the suggested solutions.
I think that we won't fully agree on giving code fields meaningful codes.
When talking about something like bins I agree.
But Country Codes, Payment Terms, etc. also have meaningful names as.
I guess a Location is an arguable item.

Maarten

DenSter

You're right, it's very subjective, and changing some of these codes doesn't happen all that much. In most implementations the location codes will never change, so it won't be a problem in most. There's a fine line and this is always a judgement call. I've just seen so many times that 'meaningful' codes create IT problems that should never have been IT problems in the first place.

Tell me this though... next time you have a new customer that asks your opinion about what to use for location codes, I bet you're going to tell them the story of this customer, and let them form a more educated opinion After all adding the Location Name to forms/pages/reports is much easier than moving the entire inventory.

mgm

I will remember this item ;-).
Thanx Daniel.