get fieldnames for all tables in a file

[Razumovsky]

Hi All,

Has anyone found a way to gather all field names from specific tables in a file without having to first make a dummy layout with all the fields from the table on it?

Thanks,

-Raz

[LaRetta]

You can export one record as Mer (which pulls the field names). Omit Record > Show Omitted > Export. Import into table as records (don't ignore first row when asked; treat it as a record) and then delete all but first record. It would be more dynamic than FieldNames() because the selected fields could be stored within export scripts. You might also be able to pull it out of DDR but I don't think that would be dynamic (specifying different fields to pull). What do you need that FieldNames() prohibits? What do you mean by gather? :wink2:

[Søren Dyhr]

I would GetValue( 'e in this function:

http://www.filemaker.com/help/Functions%20Ref30.html

--sd

[bruceR]

Has anyone found a way to gather all field names from specific tables in a file without having to first make a dummy layout with all the fields from the table on it?

Example:

Perform applescript:

set applescript's text item delimiters to return

get name of every field of table "Thistable" of database "Your Database"

copy result as text to cell "Fields" of current record of current layout of database "Documentor"

[Razumovsky]

Thanks for the suggestions:

I would GetValue( 'e in this function:

I'm afraid I dont follow...can't see how that would help without creating dummy layouts. Could you elaborate?

LaRatte and Bruce-

Great ideas, but I see I wasn't clear in what I was thinking- sorry, I meant to say I wanted to do this through define fields (or a CF, but I dont see that as being helpful). I am looking for a way to do this dynamically from within a calc, like a FieldNames function that you could set a table name instead of layout name for. FieldNames will not let you specify a table, only a layout name. if no layout name is specified, it will return all field names for the default (first created) table in the file. Unfortunately, it does not seem that you can access the field names for any of the other tables in the file unless you had previously created layouts with all and only all fields from each table.

Any thoughts?

[Søren Dyhr]

a closer look will reveal this:

If layoutName isn't specified, then the field names of the first table created (the "default table") will be returned.

...but you have to plan this carefully, if say you add an extra field later will it not be within reach.

--sd

[bruceR]

LaRatte and Bruce-

Great ideas, but I see I wasn't clear in what I was thinking- sorry, I meant to say I wanted to do this through define fields (or a CF, but I dont see that as being helpful). I am looking for a way to do this dynamically from within a calc, like a FieldNames function that you could set a table name instead of layout name for. FieldNames will not let you specify a table, only a layout name.

Not true.

Have you actually tried this? You can in fact use a table name and you will get back all fields defined for the table.

[Razumovsky]

Hmmmm, that was the first thing I tried.

FieldNames(Get(Filename); "Item")

Does not return anything for my file that has 10 fields in the table "Item" and no layout named "Item".

You are saying that a similiar calc works for you? Oh dear - I seem to be missing something then.

Okay; fiddled around and it seems that Get(FileName) is the culprit:

for file Data.fp7 with 2 tables,Invoice, Item:

FieldNames(Get(Filename); "Item") returns blank.

get(filename) returns "Data.fp7"

FieldNames("Data.fp7"; "Item") returns a list of all fields in the table Item.

I think I remember seeing this issue in other posts.

Actuallly, now I have it working in one file, but not another. Very buggy.

I might just risk the static lists over this nonsense!

-Raz

[Fenton]

Odd. It works for me, with "table" in this context being the table occurrence name, not the base table name (they are often the same, but not necessarily).

Also, from the help for Get (FileName):

Returns the name of the currently active database file, without the filename extension.

It should be an unstored calculation, if you expect it to be dynamic. But it seems to work as Bruce says.

[Razumovsky]

OK, wierd.

I do have a TO for the table, with the same name.

Now my original calc works fine. I know it was acting differently before.

I think there is a similiar issue being discussed In this thread.

But my filename is "SingleTable2.fp7" - no double period problem.

Kind of scary, as I use get(filename) in a lot of calcs referencing value lists and all... Something to keep an eye on.

-Raz

[Søren Dyhr]

Does not return anything for my file that has 10 fields in the table "Item" and no layout named "Item".

Since we're talking a singleTable structure should you, try not misinterpret the info from "help" ...what it says, is in order to get directly to the table use FileNames(GetFileName);"") it's more or less like applescript before fm7 where you got access to fields not present on the layout at hand by making a "Layout 0" clause.

I have found that deletion of default fields are responded correctly as well as renaming does. I had a hope that saving as a clone would change default, but no ...export as Excel and then dragging the file ontop of the app's icon, makes a new field label lacking file.

However is this pretty impractical, and I tested copy/paste of entire fieldsets ...again not the desired result, alterations made after the default is ignored. So the only cool cure I could think of is to get it done by adding the extra fields as if they were default, by text editing the things you get from this tool/plugin

http://www.quart-edv.de/plugins/pasteboard_en.html

--sd

[Søren Dyhr]

Update, I've been fooling myself - it's more flexible than I thougt - I had to checkout that cool cool plugin. There are absolutely nothing that distinguises default data from the newly added. In a custom dialog did I after learning this try to do this:

Substitute(FieldNames ( Get ( FileName ) ; "" );"¶";"****")

...so the stuff that confused my reasoning was the fixed size the dialogbox had to deal with a fixed number of 4 lines.

--sd

[Razumovsky]

Since we're talking a singleTable structure should you, try not misinterpret the info from "help" ...what it says, is in order to get directly to the table use FileNames(GetFileName);"")

Sorry, the file name is misleading. It is actually a singletable join structure with 5 tables that I adapted from a pure singletable file. So, I used FileNames(Get(FileName);"")before and now I need to specify which table I want for each of the calcs.

Considering that in the linked thread above there were two other independent reports of get(FileName) acting inconsistently in the same manner as here, it would seem that something is a bit off.

[Søren Dyhr]

Hmmmm, that was the first thing I tried.

FieldNames(Get(Filename); "Item")

Does not return anything for my file that has 10 fields in the table "Item" and no layout named "Item".

You are saying that a similiar calc works for you? Oh dear - I seem to be missing something then.

Before raising the bug flag do I wish to ensure, that the field not is stored/Indexed in any way - might sound silly to ask a seasoned developer this, but unreliable stuff usually stems from such deliberate ignorances. I can't simply copy your flaws!

--sd

[Razumovsky]

Unstored text calc. I did spend 9 hours last week looking for my house keys which were on my desk in front of me the whole time, so the question is not too silly at all.