Easy way to make a "Data Dictionary"

[xochi]

I need to set up a regular export of a lot (100s of fields) of data to another organization.

Sensibly, they want a Data Dictionary spreadsheet which includes FieldName, Type, and Description.

Is there any way to automatically get this info directly from filemaker? I have Advanced so I can run a DDR but that puts out way too much data.

If I do an 1 record export from each table, then I'll have field names, but it would be great if there was some way to also get the fieldType, and especially the field Comments into the export.

[Fitch]

I whipped up a little proof of concept file for you. Uses FileMaker Design functions along with Bruce Robertson's virtual list technique. Enjoy.

dataDictionary.zip

[xochi]

Wow, that's perfect! Thank you :

I hadn't looked at the design functions in a while...

[xochi]

Just noticed one issue: the script line "go to next field" will skip over any calc fields on the layout, so these don't get included. Is there any way to avoid this? I need to let calc field be included...

[xochi]

I rewrote the script to only use Get(FieldNames) and then just iterate through those using $f = GetValue($fieldNames; $j) / then $j = $j+1 / exit loop if $f = ""

This works, but the problem is that the field name order seems to be fixed as the creation order for that table.

The original solution posted is nice since the field name order will match the order of fields if the layout is in Table view mode. However, that solution skips Calc fields.

[Fitch]

Attached is an updated data dictionary. This version goes into Table view and Find mode before cycling through the fields.

You should set the order of the columns in table view. The tab order is based on the column order of the table view, rather than the order specified on your layout. If you prefer to preserve the layout tab order, remove the script step that changes the view and use list or form view.

Go to next field in Find mode does go to calc fields. Also, using Find mode means you don't even need a single record in the file, you could run this on a clone.

Enjoy.

dataDictionary2.zip

[comment]

Here's another way. Note that neither method includes fields that are not on the layout.

FieldInfo.zip

[xochi]

Attached is an updated data dictionary. This version goes into Table view and Find mode before cycling through the fields.

[...]

Go to next field in Find mode does go to calc fields. Also, using Find mode means you don't even need a single record in the file, you could run this on a clone.

Enjoy.

Clever hack! You also avoid the need for the Commit Records step (which might change the modification date) in the original method. Thanks :)

[Fitch]

Nice one, Michael. List() is such a great function...

FieldNames seems to always return the fields in creation order, which is why I went a different way in my second attempt.

[comment]

FieldNames seems to always return the fields in creation order

Actually, it lists the fields in layout's stack order - which is not much more difficult to change than tab order.

[Vaughan]

How do you know all this stuff? It amazes me. :yay:

[xochi]

Actually, it lists the fields in layout's stack order - which is not much more difficult to change than tab order.

By "layout stack order" you mean the back-to-front "Z" order that can be adjusted with "Arrange/Bring Forward or Send Backward" menu?

If so that's pretty easy to use.

My solution was just to sort the data dictionary table before exporting (reasoning that "hey, it's a Dictionary, so alphabetical order is smart") but having the dictionary match the field export order is perhaps still the better way.

Thanks to all for this help!

[comment]

By "layout stack order" you mean the back-to-front "Z" order that can be adjusted with "Arrange/Bring Forward or Send Backward" menu?

Yes.

[Fenton]

Here is another variation of the techinique. This one will also do Calculation fields. It does not however get the calculation itself. I believe only a DDR can get that (or AppleScript, via the field's "formula").

This modified file doesn't go to the fields. It just gathers the names, to use in the Loop. It can do this 2 ways. A dialog will ask which you want to use.

FieldNames ( file name; layout name ) is "fields of layout"

FieldNames (file name; table name ) is "fields of table" (table occurrence actually)

It also uses Tom's suggestion to create the virtual list records if needed, by looking at the count.

File modified per comment's post, so table and layout are named differently

dataDictionary_fej.fp7.zip

Edited July 18, 2010 by Guest

[comment]

FieldNames (file name; table name ) is "fields of table" (table occurrence actually)

Only if there isn't a similarly named layout. In your example, both layout and TO are named "data" - so it will always get only fields placed on the layouts, no matter what you choose.

BTW, I am amazed that even in version 11 this still isn't documented.

[Fenton]

Good catch. So "layout" is the default (if the names are the same). FileMaker Help says that if the layout is not specified, the fields of the 1st table in the file is returned. But they don't say anything there about being able to specify the table (table occurrence).