Sorting Issues after conversion to SQL2005 NAV4.03

Persson

The crimescene:
Our customer have a NAV 4.00 on a native database with integrated Celena Payment Management, Customers Own developed webshop etc. The job was to migrate the NAV 4.00 / native into a NAV 4.03 on a MS SQL 2005 databaseserver.

The 95% solution:
It was a easy job: with migrate.fob we cleaned up the native database and migrated it into the SQL-server without serious trouble.

New NAV 4.03 clients was installed in the organisation and it seemed to be working. Celena Payment Management needed a minor upgrade. No problem. The customers webshop worked too.

Then trouble began ..

The remaining 5%:
In Chart of Accounts the G/L accounts was sorted like 1, 11, 111, 1111 etc. Hmm .. this problem was solved by changing the SQL
Data Type property for Table 15 "G/L Account":Field 1 "No." and Table 17 "G/L entry":Field 3 "G/L Account No" to Integer.
Now the sorting of the G/L Accounts was fine.

But .. the "Net Change" and Balance Colums is showing incorrect data! When you f.ex. have a Account Type End-Total with a totalling of "300 .. 6995" you want all the values from G/L Accounts from 300 to (and including) 6995 to be counted in the "Net Change" and "Balance" fields. Instead NAV gives me a count that begins with 3*, 4*, 5* and 6*. Including 51000 and 60995! One big mess with Balance sheet and Income Statement results mixed all up.

My question is: how do I correct the Chart of Accounts in a way that "Net Change" and "Balance" is counted like 1, 2, 3 and 4 insted of 1, 11, 111, 1111 etc: ?

The Customers have hardcoded ](*,) his G/L Account Numbers into his webshop, so renaming the G/L Account Numbers to five figures Numbers, like 00300 and 51000, is an option I would like not to implement.

Help anyone?

cnicola

Hi JPE,

I do not think there is any way you can solve this. I think it is safer to just rename the G/L Accounts. And BTW I am a little worried about you changing the data type on the SQL side.

As for the webshop since you were on C/Side to start with I would assume you used some sort of NAS based interface or the likes (or simply text files exported to a folder :P ). So for that I would add a new field to the G/L Account No. called "Old Crappy G/L Acc. No" and move the current value there before renaming them the right way. Then I would simply change the interface to send to the webshop the "Old Crappy G/L Acc. No" instead of "No." and then you are set.

P.S. I will not even ask how come a webstore is linked to the Chart of Accounts.

Persson

cnicola wrote:

I do not think there is any way you can solve this. I think it is safer to just rename the G/L Accounts. And BTW I am a little worried about you changing the data type on the SQL side.

Thx for your reply! It's much appriciated The SQL datatype was changed from inside NAV - it is not wise to modify NAV from the SQL-side, I was told when I started at the NSC.

My Customer is convinced that I can work something out that don't involve renaming the G/L Accounts. And I will offcourse do my homework before I have to tell them the bad news. Are you absolutly sure that it is impossinle to organize the "Net Change" and "Balance" Colums for Numeric Search (SQL Datatype Integer) in the Chart of Accounts?

cnicola wrote:

As for the webshop since you were on C/Side to start with I would assume you used some sort of NAS based interface or the likes (or simply text files exported to a folder :P ). So for that I would add a new field to the G/L Account No. called "Old Crappy G/L Acc. No" and move the current value there before renaming them the right way. Then I would simply change the interface to send to the webshop the "Old Crappy G/L Acc. No" instead of "No." and then you are set.

P.S. I will not even ask how come a webstore is linked to the Chart of Accounts.

Thanks for the suggestion - I will note it down in case I can't find another solution. I have not seen the code behind the webshop, I have only been told about the limitations the NAV heritage from this webshop. Being a certified MS C# Developer myself I can't see the big problem in editing the Data Access Layer of the Webshop Application. But I am not sure that the Webshop is developed with different layers .. :sick: And this is off course a problem .. for the NAV Consultant ](*,)

Persson

Just for the record: a fast workaround would be to use criteria like "300|400|500|600|1000" instead of "300..1000" but I still search for a solution to use Numeric Search (SQL Datatype Integer) in the "Net Change" and "Balance" Colums in the Chart of Accounts.

cnicola

Well even from Navision I don't think I would have considered that change.
I am not sure whether there are workarounds for the Net Change and Balance but that is actually: you will have an endless list of places in Navision where you need to work around the issue. You will have to analyze all the places that access G/L Account No. is used in that way and change them and then reports and filtering and so on.
So that is why is easier and safer to just go back to standard Navision.
I know it is not the answer you are looking for but it will save you a lot of headaches in the future.

David_Singleton

I think the Swedish version has (or had some time back) sorting like this, done by converting all the Account No. fields to Text. I wouldn't suggest that, but maybe looking at that system, you can see how they solve it.

Waldo

Persson wrote:

In Chart of Accounts the G/L accounts was sorted like 1, 11, 111, 1111 etc. Hmm .. this problem was solved by changing the SQL
Data Type property for Table 15 "G/L Account":Field 1 "No." and Table 17 "G/L entry":Field 3 "G/L Account No" to Integer.

Have you tried to use Variant instead of Integer?

cnicola

How will variant make a difference?

Waldo

I was informed that "Integer" is only there to be backwards compatible. There are problems with this datatype however (I won't go into this deeper). Therefore, you shouldn't use Integer.

Furthermore .. Integer causes some (other) problems which Variant solves. Therefor I just suggested to try to use Variant to also (may be) solve this problem.

Persson

Waldo wrote:

Have you tried to use Variant instead of Integer?

No Sire, but I will try out tomorrow. Feedback will come.

However - it seems like many intelligent people suggests that a renaming is the best practise og the best way to go, considering future development and upgrading etc.

Persson

Waldo wrote:

I was informed that "Integer" is only there to be backwards compatible.

I have read this too somewhere. Dynamic NAV Whitepapers or perhaps Mibuso ;-)

Waldo

Persson wrote:

Waldo wrote:

I was informed that "Integer" is only there to be backwards compatible.

I have read this too somewhere. Dynamic NAV Whitepapers or perhaps Mibuso ;-)

It was certainly discussed on mibuso, but I can't find the thread anymore... .

Marije_Brummel

Waldo wrote:

It was certainly discussed on mibuso, but I can't find the thread anymore... .

We discussed this in email. I cannot recall posting it on the forum. :?

Anyway, you are correct. The integer option is not to be used anymore.

I will also include this information in my session "Migrating from C/Side to SQL" at Directions. Hope you can all make it there.

So; now it is on Mibuso.

Waldo

Mark Brummel wrote:

I will also include this information in my session "Migrating from C/Side to SQL" at Directions. Hope you can all make it there.

Good one =D> .
I didn't think of it when I was reviewing your presentation... .
I think your presentation and the described steps would be quite an interesting thing to publish somehow:
- tips&tricks
- white paper
- ...

Persson

Mark Brummel wrote:

I will also include this information in my session "Migrating from C/Side to SQL" at Directions. Hope you can all make it there.

"Directions"? :-k

I am eager to learn more about the migration proces, best practise, pitts to avoid etc., but finding technical information about this at PartnerSource is like looking for coal in a very dark basement. Blindfolded. That's why I have grown happy about Mibuso.

Waldo

That's why I said the above.

FYI: more info about Directions: http://www.cronususa.com

Persson

Waldo wrote:

I think your presentation and the described steps would be quite an interesting thing to publish somehow:
- tips&tricks
- white paper
- ...

Please do and I will buy you a lapdance some day \:D/

DenSter

Mark Brummel wrote:

Waldo wrote:

It was certainly discussed on mibuso, but I can't find the thread anymore... .

We discussed this in email. I cannot recall posting it on the forum.

It was in a discussion on mibuso, and it was quite recently too, I just can't find it. Someone posted a number of reasons not to use integer, one of them had something to do with data type compatibility in related fields.

Persson

cnicola wrote:

Well even from Navision I don't think I would have considered that change.
I am not sure whether there are workarounds for the Net Change and Balance but that is actually: you will have an endless list of places in Navision where you need to work around the issue. You will have to analyze all the places that access G/L Account No. is used in that way and change them and then reports and filtering and so on.
So that is why is easier and safer to just go back to standard Navision.
I know it is not the answer you are looking for but it will save you a lot of headaches in the future.

I have found another posting at Mibuso with the same problem. Pls have a look at the last two postings : http://www.mibuso.com/forum/viewtopic.p ... nt+integer

Isn't there anyone out there that have experieced this problem before and solved without renaming the G/L Account Codes? Or perhaps have solved it but then ran into a application filled with trouble because of the use of the SQL Data Type Property?

cnicola

Hi Persson,

I do admit I am on old Navision guy so I am quite into the "Navision" way.
So I will still suggest renaming the G/L Accounts codes.
But besides the "Navision religion" O:) I would say there are no unknown side-effects to doing that. It is clear what you have to do on both ends (Navision and webshop) and once they are done you will not have to worry about anything else.
If you go the other path you do not know what else can happen and you might only find out too late.
Looking at a very high level I would still go with the safe (though unpopular) option.

Just my 2 cents

paurola

How about creating a new integer field in G/L Account table and then making that a key. Add code to Account No. field's OnValidate-trigger to keep the field up to date.

Then use this field for sorting where ever you want.

For starters you need to write some code for updating that field for existing data.

Persson

cnicola wrote:

Hi Persson,
I do admit I am on old Navision guy so I am quite into the "Navision" way.

That's why I come here .. the beer is warm and the air condition no existing but there are lots of Old Timers taking time to share information and kick us Youngsters a little 'round the Canvas

cnicola wrote:

So I will still suggest renaming the G/L Accounts codes.
But besides the "Navision religion" O:) I would say there are no unknown side-effects to doing that. It is clear what you have to do on both ends (Navision and webshop) and once they are done you will not have to worry about anything else.

Your point is taken and the Customer was informed yesterday. I went for the renaming proces. Though I think it is funny how NAV Consultants actually use the SQL Value Type option in the Chart of Accounts to "correct" this "error" - at least among the danish consultants I know. But not for long - I will spread the message about the Right Way to handle the SQL-migration according to the Chart of Accounts.

cnicola wrote:

Just my 2 cents

Thanks to you and the rest of you guys who enlighted me on this topc \:D/

Persson

paurola wrote:

How about creating a new integer field in G/L Account table and then making that a key. Add code to Account No. field's OnValidate-trigger to keep the field up to date.

Then use this field for sorting where ever you want.

For starters you need to write some code for updating that field for existing data.

That would have been a nice solution too. Thanks for input. However the Financial Accounts was renamed yesterday so I will not use your suggestion this time. But for sure keep it in BrainTrust for later!