The Big Picture Of NAV v2

Administrator

The Big Picture Of NAV
This tool will export all table relations for the selected NAV DB. Just export all tables as .txt file, import these objects into the DB, run the form 66000, select the TXT file, set the options and process. If you let the process to fill the table, you can browse the relations by running form 66001 (you can renumber the objects in the txt format to be able to run them).

This tool was used to create complete diagram of table relations with name "The Big Picture Of NAV".

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

Discuss this download here.

rainergaiss

I got an error message while importing the text. It happens within the function SplitLine, in the "sector" "Separate Target table name from target field name" and with the statement "PKField := COPYSTR(Table,Pos) + PKField;"

The problem is that the field Pos contains zero (which is invalid).

Please contact me if I can help you to find this bug.

daborg

I too had this same error.

After a bit of debugging it looks like the tool does not handle when a field relation doesn't define the field, only the table.

For example create a field in the Location table called "Manufacturing Location Code" and relate it to the Location table. Make sure in the table relation you do NOT specify that it is the Code field. this is course allowed because the Location table has a single field primary key.

This will cause it to error.

Changing the table relation to "Location.Code" causes the error to go away.

Can we get a fix for this?

SigGunn

I also got these errors when I tried these objects.

After some debugging I made the following changes to form 66000 and got it to complete the text import without errors.

1. Change both occurrances of

IF STRPOS(Line,';')>0 THEN
  EndLoop := TRUE;

to

IF (STRPOS(Line,';')>0) OR (STRPOS(Line,' }')>0) THEN
  EndLoop := TRUE;

2. Change

EndLoop := STRPOS(Line,';') <>0;

to

EndLoop := (STRPOS(Line,';') <>0) OR (STRPOS(Line,' }')<>0);

3. In REPEAT after //*****Separate Target table name from target field name make the following changes

Table := DELCHR(Table,'<>','"; ');

should be

Table := DELCHR(Table,'<>','";} ');

and

PKField := COPYSTR(Table,Pos) + PKField;
Table := COPYSTR(Table,1,Pos-1);

should be

IF Pos > 0 THEN BEGIN
  PKField := COPYSTR(Table,Pos) + PKField;
  Table := COPYSTR(Table,1,Pos-1);
END ELSE BEGIN
  Table := DELCHR(Table,'>',' }');
END;

There is still a potential bug with this, if a table which is specified in TableRelation doesn't exist in the database the REPEAT goes into infinite loop because Name will never match ObjectBuffer.Name

I hope this will help someone.

kine

Ok, thanks for the feedback. I was on holidays...

It is interesting, that I didn't have this error. Which NAV version objects you were importing? Can you send me the text file of the object, on which it is crashing? I want to reproduce it and change the logic according that...

kine

Just wants to add that the code is based on analysis of txt file exported by NAV 5.00SP1. It seems that you have found construction of the TableRelation I didn't had in the cronus objects.

If someone can post the whole block of the TableRelation definition from the text file on which this happen, I will be happy. I need the part from "TableRelation=" line to next property or field definition (+prev and next line).

Thanks

rainergaiss

Hi there again,

@SigGunn: Thank you for the patches, no it worked perfectly.

@kine: I would like to send you an example. Where do you want it to be sent to? I think it is a little too big to post it here in the forum.

Regards

Rainer

kine

Thanks to rainergaiss I know now what is the problem. Boys, your objects does not fulfill main guidelines - there is caption missing on the field and it is why you have this problem. In this case, and if you did not set any other property on the field, the definition of the field is only on one line, and it means that the table relation ends with character '}'. ;-)

In this case, the } is like ; and the patch described by SigGunn is the correct solution.

example of problematic text:

{ 50001;  ;Field Name;Code20;TableRelation=RelTable.Field }

rainergaiss

Thank you so far to everybody!

Rainer

kine

I assume that tomorrow I will send new version to Luc for publishing. It will include also all relations made through FlowFields!!! 8) :whistle:

Administrator

The Big Picture Of NAV v2
These objects can be used to extract TableRelations data from txt file with definition of all tables in the database.

How to use:

1) Import the objects into your DB
2) Run the form 66000
3) Set the parameters
4) Click the button
5) If you selected to fill the table, you can run the form 66001 and browse the relations there. You will see all refered and all referring fields for the selected field.


Version 2.0 changes:

1) "One line field definition" bug removed (patch from SigGunn)
2) New field "Relation Type" added
3) CalcFormula= definition is now parsed too - now the relations includes all relations made through FlowFields and TableRelation

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

Discuss this download here.

kine

I hope that everybody enjoy the relation hell of your own databases... :-)

Any ideas for making the tool better are welcome...

daborg

Found an issue with the flowfield parsing. If you have a COUNT flowfield with no "where" condition, the tool hangs endlessly when reading in the text file.

Problem Field definition:

{ 2   ;   ;CounterField        ;Integer       ;FieldClass=FlowField;CalcFormula=Count(Location) }

I don't know if this is limited to COUNT or if all flowfields set up like this are affected.

Also, it would be nice if a field is defined using a non-existent table that the tool errors. Right now it just hangs as well on reading in the test file. I know normally a field should not be related to a non-existent table but it does happen if you are moving objects around multiple databases and you forget some. :roll:

kine

1) Yes, I make small changes to prevent the endless loop, I will check if the published version includes that or I will upload never version...
2) Problem with the example is, that there is no Caption for the field and it means the whole definition is on one line. I didn't count with that because I expected "correct" definition of fields (including ENU language at least).