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.