Field Check v3

Administrator · 2009-10-12

Field Check
This report scans the database looking for values that will prevent an FBK of a native database from being restored into a Navision MS-SQL database.

By default it scans only for problem dates, however, it also has options to scan for overlong text fields and out of range decimal values. Once a problem is found the default action is to log it into the Change Log, if the "test run" flag is turned off the value will be truncated and change log entries written to show the before and after values.

All companies in the database will be scanned and fixed, the changelog entries will be placed in the same company as the table being scanned. Tables that are not 'per Company' have the changelog entries placed in the current company.

The Navision tool kits have an import called 'migrate.fob' that this would appear to be a duplicate of. However, this has significant advantages:

1) It's a simple report, drop it in and run.
2) The "test run" allows you to run this on a copy of the live database and then use the change log entries to update Live.
3) It's a lot quicker! (eg: 15Gbyte database in 45 minutes on a desktop PC)

On the subject of speed, this has to scan a lot of data so it's best run on a local database not through a database service. Measurements suggest that going direct is at least four times faster than using a service.

This can run using a non-developer license, however, it needs permissions added for the protected tables if it is to 'fix' them.

The report works for all Navision versions from 3.60 onward.
But for 3.60 the FOB must be recompiled by that version after replacing the RecRef.OPEN(... "Company Name") line with an EXIT. This means it'll only scan the current company.

This report does not implement the code field value classification that's in the migrate.fob tool.

--
Robert de Bath c/o mibuso.com

This is a update to Report to convert illegal date-values v2 by Mikael Hansen. Thanks Mikael.

http://www.mibuso.com/dlinfo.asp?FileID=1149

Discuss this download here.

rdebath · 2009-10-18

I've just uploaded an updated version that's a little faster and doesn't lose the contents of the BLOBs in any record it touches.

The "scan in a copy and update live" functionality is now working nicely.

There's even an option to apply a date formula (eg: +4Y) to every date and datetime in the database.

Oh and one last little nasty to deal with the characters ÿšœž in Code fields.

Administrator · 2009-10-19

Field Check v2
This report scans the database looking for values that will prevent an FBK of a native database from being restored into a Navision MS-SQL database or cause issues with software that replicates between these databases.

By default it scans only for problem dates, however, it also has options to scan for unsupported characters in code fields, overlong text and code fields and out of range decimal values. Once a problem is found the default action is to do a dumb fix and save change log entries to show the before and after values.

If the 'logging only' flag is turned on the value will be saved in the "Database Field Updates" table. Where is can be copied, modified and later applied by pressing the "Run updates" button.

All companies in the database will be scanned and fixed, the changelog entries will be placed in the same company as the table being scanned. Tables that are not 'per Company' have the changelog entries placed in the current company.

The Navision tool kits have an import called 'migrate.fob' that this would appear to be a duplicate of. However, this has significant advantages:

1) It's a simple report & table, drop it in and run.
2) It can do character set fixes on code fields.
3) The table allows you to run this on a copy of the live database and then use the log entries to update Live. (Copy & Paste & Run)
4) It's a lot quicker! (eg: 15Gbyte database in 45 minutes on a desktop PC or 15 minutes if you just scan for dates) Cronus takes seconds.

This report does not implement the code field value classification that's in the migrate.fob tool.

--
Robert de Bath c/o mibuso.com

This started life as an update to Report to convert illegal date-values v2 by Mikael Hansen. Thanks Mikael.

http://www.mibuso.com/dlinfo.asp?FileID=1149

Discuss this download here.

Belias · 2010-01-19

i have an .fdb database in which there are some dates are (wrongly) set to, for example, 01/11/0008....i thought: "let's use this tool and change add 2000 years to them!!!"
is this correct, or also the other dates will be incremented? (currently, the tool is running on my test db, but i can't wait for the result

EDIT: #-o I just found Report to convert illegal date-values v2!!
EDIT2: which didn't work!!

Per_Bay · 2010-01-28

Hi!

I have tried this little nice tool for first time today.

One issu is the illegal charecter, and I can't figur out your function :-k

First I Can't see that SelectBefore, SelectAfter are initilazed if you not enter anything in
"Remote Uppercase String"
Next is follow code, I asume that Before should be lowcase.
But if you first Uppercase the string, next Line will delete all #-o

SelectBefore := UPPERCASE(SelectBefore);
SelectBefore := DELCHR(SelectBefore, '=', UpCase);

To got a regulare Upcase Control i Forced the filter with:

ELSE IF TRUE{SelectBefore <> ''} THEN

and added

            // I've seen Text and code fields values that are too long for the field.
            FieldTable.Type::Code : BEGIN
              OrigCodeVar := Field_Ref[Fno].VALUE;
              CodeVar := CONVERTSTR(COPYSTR(OrigCodeVar, 1, Field_Len[Fno]), SelectBefore, SelectAfter);
              IF (CodeVar <> OrigCodeVar) THEN
                BEGIN
                  RecLogAndDel("Company Name", Fno, CodeVar);
                  IF NOT Field_Iskey[Fno] THEN
                    Field_Ref[Fno].VALUE := CodeVar;
                END;
//>>
              CodeVar := UPPERCASE(OrigCodeVar);
              IF (CodeVar <> OrigCodeVar) THEN
                BEGIN
                  RecLogAndDel("Company Name", Fno, CodeVar);
                  IF NOT Field_Iskey[Fno] THEN
                    Field_Ref[Fno].VALUE := CodeVar;
                END;
//<<

Please tell me how this function are ment to work.

rdebath · 2010-01-28

Per Bay wrote:

Hi!

I have tried this little nice tool for first time today.

One issu is the illegal charecter, and I can't figur out your function :-k

First I Can't see that SelectBefore, SelectAfter are initilazed if you not enter anything in
"Remote Uppercase String"

Okay, if you don't change the remote character string no translation is done. The init is in the OnOpenForm of the request form so that if you run this from a NAS no translation is done either.

Per Bay wrote:

Please tell me how this function are ment to work.

The idea is that different Navision databases make Navision have different ideas as to what the uppercase function should do. So if you take a string of all 255 characters and uppercase it on a Native database you get a different result to when you uppercase on an SQL database. (This also happens for different character sets on an SQL database). So you run this program on the destination database (eg: SQL) and get the "Remote Uppercase String" from that DB, paste it into the same field of the program running on the source DB (Native) tab off and the "Code field translation" is set to "ÿšœž -> ŸŠŒŽ" (for my CP850 -> CP1252 & Native -> SQL combination).

The result is that when run, all the code fields in the source DB (Native) are changed as if they have been run through an uppercase function on the destination database (SQL).

As you can see the list of translations (in the "Code field translation" field) that it'll do is quite short, so it's usually rare that you'll need to do this.

Per_Bay · 2010-01-28

Thanks, for fast response!

Yea, my code change above didn't work, because system didn't know what upercase of õ was :?

On SQL (Destination), I Have follow HEX/String konverting

819A829083B6848E85B7868F878088D289D38AD48BD88CD78DDE919293E2949995E396EA97EB98FE9B9DA0B5A1D6A2E0A3E9A4A5C6C7D0D1DABFDCC1E4E5E7E8ECEDF2C3
abcdefghijklmnopqrstuvwxyzüéâäàåçêëèïîìæôöòûùÿøáíóúñãðšœõþýž -> ABCDEFGHIJKLMNOPQRSTUVWXYZÜÉÄÀÅÇÊËÈÏÎÌÆÔÖÒÛÙŸØÁÍÓÚÑÃÐŠŒÕÞÝŽ

This looks real nice.

I paste this on my Native db, on other maschine and got

abcdefghijklmnopqrstuvwxyzüéâäàåçêëèïîìæôöòûùÿ¢áíóúñ–¨ÀÍÏÕÛ -> ABCDEFGHIJKLMNOPQRSTUVWXYZÜÉ†Ä‡ÅÇ®¯¹¸ÃÆËÖÌÓÔþ¥…´ÊÒÑ—©‘ÎÐ×“

One know problem I have is the char õ.
In my Destination mapping this should have been translated from õ -> Õ.
But when i Paste it on my source database, we don't have any lowchar for õ.
It's Õ, and will be replaced with Î. ](*,)

I solved it by modify code and pasting follow strings in source database before run.

SelectBefore := 'abcdefghijklmnopqrstuvwxyzüéâäàåçêëèïîìæôöòûùÿøáíóúñãðšœõþýž';
SelectAfter  := 'ABCDEFGHIJKLMNOPQRSTUVWXYZÜÉÄÀÅÇÊËÈÏÎÌÆÔÖÒÛÙŸØÁÍÓÚÑÃÐŠŒÕÞÝŽ';

Maybe a quick and dirty hack, to solve my problem and not a common solution.

rdebath · 2010-01-28

Per Bay wrote:
I solved it by modify code and pasting follow strings in source database before run.
SelectBefore := 'abcdefghijklmnopqrstuvwxyzüéâäàåçêëèïîìæôöòûùÿøáíóúñãðšœõþýž';
SelectAfter  := 'ABCDEFGHIJKLMNOPQRSTUVWXYZÜÉÄÀÅÇÊËÈÏÎÌÆÔÖÒÛÙŸØÁÍÓÚÑÃÐŠŒÕÞÝŽ';
Maybe a quick and dirty hack, to solve my problem and not a common solution.

That's quite reasonable if you know the characters you want to translate, though you don't have to put the plain ASCII letters (abcdef...z) in there just the ones that you need to change.

In fact, I think I'll make those fields editable on the request form.

Per_Bay · 2010-01-28

rdebath wrote:

though you don't have to put the plain ASCII letters (abcdef...z) in there just the ones that you need to change.

To ensure that no external tool have put a lowcase char in CODE field, I include a..z.
But normaly the client should handling this.

rdebath · 2010-03-20

Belias wrote:

i have an .fdb database in which there are some dates are (wrongly) set to, for example, 01/11/0008....i thought: "let's use this tool and change add 2000 years to them!!!"
is this correct, or also the other dates will be incremented? (currently, the tool is running on my test db, but i can't wait for the result
EDIT: #-o I just found Report to convert illegal date-values v2!!
EDIT2: which didn't work!!

I just noticed this message.
If you just want to fix the dates that are a problem for SQL all you have to do is run this report with default options. Not add 2000 years to every single date and datetime in he system!

Belias · 2010-03-22

Ok, i'll check it out, thank you!

Administrator · 2010-03-22

Field Check v3
This report scans the database looking for values that will prevent an FBK of a native database from being restored into a Navision MS-SQL database or cause issues with software that replicates between these databases.

By default it scans only for dates before 1/1/1754 (and moves them to 1756, a leap year), however, it also has options to scan for unsupported characters in code fields, overlong text and code fields, out of range decimal values and newlines, tabs and trailing spaces in text fields.

It also has the ability to shift all dates in the database with a date formula.

Once a required change is found the default action is to do a dumb change and save change log entries to show the before and after values.

If the 'logging only' flag is turned on the value will be saved in the "Database Field Updates" table. Where is can be copied, modified and later applied by pressing the "Run updates" button.

All companies in the database will be scanned and fixed, the changelog entries will be placed in the same company as the table being scanned. Tables that are not 'per Company' have the changelog entries placed in the current company.

The Navision tool kits have an import called 'migrate.fob' has some of this functionality. However, this has significant advantages even for the overlap:

1) This is a simple report & table, drop it in and run.
2) The table allows you to run this on a copy of the live database and then use the log entries to update Live. (Copy & Paste & Run)
3) It's a lot quicker! (eg: 15Gbyte database in 45 minutes on a desktop PC or 15 minutes if you just scan for dates)

This report does not implement the code field value classification that's in the migrate.fob tool.
__________________________________________________________________________________

This report implements a character function for converting code fields that contain characters that are uppercased differently on SQL and Native.
The "Remote Uppercase String" needs to be copied from the SQL database to the Native database client for this to work. You should make sure they are running the same ANSI and OEM character sets, the easiest way to do this is to have the clients run on the same machine.

The option "Adjust All Dates by (Years)" adds or subtracts a number of years to every date and datetime field in the database. This works well as a torture test for the program and it can also be used to shift demo data to a different year.
Note, however, that a period that's not an exact multiple of four years can have issues because of leap years. This is why the date formulas are made into a number of days this field is not a multiple of four. The exact formula in this case depends on the first posting date in the G/L. This runs in about 10 seconds on a local (native) Cronus database.

See also: http://www.mibuso.com/dlinfo.asp?FileID=1150

The "Remove TAB CR and LF from Text Fields" option removes these characters from the ends of text strings and replaces them with spaces if they're embedded in a string.

The "Trim Trailing Spaces" option removes trailing spaces from every text field.

The "Reselect Updated Dates" find all dates in the database before 01/01/1757, these are probably dates that are either 'bad' now or were given a dumb fix earlier. This can be used to add a smarter fix after the event.

The program normally commits any changes it makes within a few moments of making the change, not immediatly, but not more than 200ms later.
The "Use a Single Transaction" option does just that; this is essential for the "Adjust All Dates" options as is the "Disable Changelog Entries" option.

This has to scan a lot of data so it's best run on a local database not through a database service. Measurements suggest that going direct is at least four times faster than using a native service. SQL is even slower.

This can run using a customer license and the required permissions have been added, however, it hasn't been well tested in this form.

The report works for Navision versions from 3.70 onward.
Small changes are needed to make it work for 3.60 but as a native database can be opened by (later) 3.60 and 3.70 clients at the same time a 3.70 client should be used.

The options that scan for values that are illegal on an SQL database are disabled when we're running on an SQL database. Other options are still available.

-- Robert de Bath c/o mibuso.com

This started life as an update to http://www.mibuso.com/dlinfo.asp?FileID=1025
by Mikael Hansen. Thanks Mikael.

http://www.mibuso.com/dlinfo.asp?FileID=1149

Discuss this download here.

Fatboy40 · 2010-03-30

Administrator wrote:

Field Check v3

This can run using a customer license and the required permissions have been added, however, it hasn't been well tested in this form.

-- Robert de Bath c/o mibuso.com

I'm running Nav 3.6, under what I believe to be a 'customer license', however I'm unable to run this tool so that I can clean our data and restore it to an MS SQL database (You do not have permission to run the XXX report).

Is there anyone who would would be kind enough to provide me with some assistance ?.

Thanks.

rdebath · 2010-03-30

Ah, that's a problem, the report (and table) numbers have to exist on the license.

You might try it with a Cronus license ...
But please, only do it on a copy of your live data.

Fatboy40 · 2010-03-31

rdebath wrote:

You might try it with a Cronus license ...

Good idea, but unfortunately our native DB is far larger than that which the CRONUS license allows

rdebath · 2010-04-01

Then I imagine you need to acquire a better license, either buy access to an official one or I understand there's a 3.70 license RU-IX-123-CRAZY-X floating round the internet.

afarr · 2013-09-27

I posted the same message twice because of some problem with mibuso. See the post below.

afarr · 2013-09-27

This report is really useful. However, I've just run into a strange issue with the characters (From and To).

I imported the report into a .fdb and an SQL database. I opened the native client and the NAV SQL client (2009 R2, build 32012) on the same machine, which is running SQL 2008 R2 64-bit.

The Remote Uppercase String is shorter in native than in SQL. This happened both when I imported the report as .txt, and when I imported it as .fob.

819A829083B6848E85B7868F878088D289D38AD48BD88CD78DDE919293E2949995E396EA97EB9B9DA0B5A1D6A2E0A3E9A4A5C6C7D0D1E4E5E7E8ECED
819A829083B6848E85B7868F878088D289D38AD48BD88CD78DDE919293E2949995E396EA97EB98FE9B9DA0B5A1D6A2E0A3E9A4A5C6C7D0D1DABFDCC1E4E5E7E8ECEDF2C3

The SQL database is using the default Windows collation, case INsensitive, accent INsensitive:
Afrikaans, Basque, Catalan, Dutch, English, Faeroese, German, Indonesian, Italian, Portuguese

The SQL server (SQL 2008 R2 Standard 64-bit) is using collation Latin1_General_CI_AS, and it also runs on the same machine.

We would appreciate any ideas about what causes this and, more importantly, what we can do about it.

Howdy, Stranger!

Quick Links

Categories

Field Check v3

Comments