Impossible to get a list of Table names in v10???

[Christensen Design]

How can I get a list of Table names in FM v10?

The current TableNames and TableIDs functions do not work. Instead of returning TableNames and IDs these two functions return RelationshipNames and RelationshipIDs. Tables and relationships are two very different things (tables are a storage structure, relationships are just searches).

All I want to do is generate the same list of 'Tables' that appears in the Tables tab of the 'File:Manage:Database' dialog.

Edited June 17, 2009 by Guest

[bcooney]

I usually use the DDR.

[LaRetta]

You can select 'print' when in File Manage tables. But if you want to always have a set of 'base table names' just name them in your graph that way.

like this

It's a fudge maybe. But it also can be used to tell you your core table occurrences, ie, can be used as a cluster which shows your primary relationships as suggested by using Ray Cologon's Flotilla or Cluster/Satellite Model.

There are many ways to name restricted only by your imagination and the ability to use a calculation to find the 'pattern' to produce your own list.

[Christensen Design]

No, no, no... I don't want a list of Tables just to refer to for development purposes. I need to generate a list of Tables (i.e. a value list) so I can step through each table in the system to build some 'arrays' of special field data.

Am I not correct that these two functions are totally misnamed?? TableNames does NOT return a list of table names. It returns a list of relationship names.

[LaRetta]

Am I not correct that these two functions are totally misnamed?? TableNames does NOT return a list of table names. It returns a list of relationship names.

There is ABSOLUTELY NO DIFFERENCE between a table and an occurrence of it in your graph - if you call it a chicken, it'll cluck just as well. So whatever you name the occurrence can be your base. :wink2:

[LaRetta]

so I can step through each table in the system to build some 'arrays' of special field data.

To explain a bit further, you can't do anything to a table. You can only do something to an occurrence of it in the graph.

UPDATE: Or another way ... since FM must "do things" to only occurrences in the graph, if you had two occurrences of your Customers table, one called Customers All and another one named, Customers Retail, how would FM know which one to act on? It can't. You must tell it the name of the occurrence to act upon.

Edited June 17, 2009 by Guest
Added update

[Christensen Design]

Well.. I complete disagree with LaRetta's response in the link above. There is a huge difference between Tables and Relationships and anyone who says otherwise does not understand relational theory.

What is tragic in the long run is FM Inc.'s blurring of the lines between Tables and Relationships which has resulted in their bastard child: the 'Table Occurrance'. This unholy marriage of Table and relationship causes a huge amount of unnecessary complexity and confusion -- particularly on the entity relationship diagram.

A table is very straightforward: it's the physical storage entity of an RDBMS. You can have many relationships between two tables. And ALL relationships between two tables in an RDBMS go between a 'one table' and a 'many' table. Period!!

Filemaker's concept of Table occurrances is what other RDBMS's refer to as simply 'relationships'. A relationship is nothing more than a way to pre-define a search between two tables so that the RDBMS can perform certain tasks automatically for the user -- tasks like searching for related records or performing deletion control (i.e. deleting all related many records when a parent related one record is deleted by the user, etc). But that's it... a relationship is nothing more than a search. In fact, in most systems it is not even necessary to define relationships because this exact same effect can be achieved with a simple search (i.e. query).

FM introduced 'table occurrances' because they have no true SEARCH command and because (apparently) they couldn't think of a better way to allow multiple relationships between tables in their Entity Relationship Diagram. It's rather amazing that FM is an RDBMS in the year 2010 with no search command!! You can save a search in FM and execute it later, but you can not execute a script command that says SEARCH [People] where [People]CompanyID = [Companies]CompanyID and get a selection of records in a portal. You first have to 'define' a 'table occurrance' (relationship) between [People] and [Companies] and then have FM activate that 'relationship' (i.e. search) for you by placing a portal on a layout that uses that 'relationship'. This is a very cumbersome and back-assward way of managing a relational data.

The bottom line is that FM's table occurrances are a bad idea they takes the very simple and elegant concept of relationships (in relational theory) and forever mutates it into a very cumbersome table/relationship concept that makes large systems difficult to build and maintain. Table Occurances have so muddied the water that FM's own development staff is confused about them. If they truly understood the significance between a TABLE and a RELATIONSHIP they would not have misnamed the 'TableNames' and 'TableIDs' functions.

And how long has this misnomer gone unnoticed??

[LaRetta]

Table occurrences are NOT relationships.

I suggest it is you who doesn't understand the new multi-layered and multi-dimensional rendering and data-flow both up through grand-parent and down through grand-child.

You can treat FM exactly like a standard ERD. Go ahead, no problem. :laugh2:

[Christensen Design]

Loretta..

In FM's version of a Entity Relationship Diagram, yes there is (unfortunately) no difference between a Table and a 'Table Occurrance'. To FM, a table occurrence is basically an alias for a table and one-half of a relationship. But under the hood there is only ONE table for all the 'occurrences' of that table. And the occurrences are only necessary in FM because of their lack of a search command and a true separate 'relationship' entity. The Table Occurrance is FM's kludgy way of allowing multiple relationships (pre-defined searches) between the SAME underlying tables in the system. And it has the unfortunate effect of making users think that a 'table occurance' is a relationship. It is not.

You can see this for yourself by opening the Database Management window and looking at the THREE tabs in the window: TABLES, FIELDS, and RELATIONSHIPS. The first tab is correctly labelled and shows the unique TABLES in the system. That is the list I need to generate dynamically in my system in order to implement a generic navigation system.

[LaRetta]

Well we could argue til the cows come home; I refuse. It is obvious to me that you lack any real understanding of FileMaker and I have better things to do than argue with someone that can't even spell my name right. :crazy2:

[comment]

The Table Occurrance is FM's kludgy way of allowing multiple relationships (pre-defined searches) between the SAME underlying tables in the system.

That may very well be so. However, once FMI has chosen this "kludgy way", that's how it works - and one of the consequences is that you can do virtually nothing with a source table name, even if you had one because (as LaRetta pointed out) you can only address a TO, not its source table.

[David Jondreau]

I don't know how you can get that list using FM's design functions.

But if you gave details about what you're trying to accomplish, we may be able to help by finding an alternative method.

All you've said about the problem you're having is that:

I need to generate a list of Tables (i.e. a value list) so I can step through each table in the system to build some 'arrays' of special field data.

The first tab is correctly labelled and shows the unique TABLES in the system. That is the list I need to generate dynamically in my system in order to implement a generic navigation system.

[LaRetta]

I gave him an alternative, DJ - he just wanted to vent instead.

[Genx]

Well hmmm...

I don't know why people always complain about the relationships graph. I mean, yes it can get a little slow when you're dealing with thousands of table occurrences, but realistically it's oriented towards something with a few hundred and is a brilliant concept.

Additionally, I don't see anything all that confusing about the table occurrence concept. A table occurrence is just a table alias / view of a table which serves two purposes:

1) "One side of a relationship"

2) A point of origin from which relationships are contextually evaluated.

The main advantage of this context based system is that relationships can be re-used without needing to be respecified for each specific context. I.e. you can view all invoices related to a customer from the view point of the customer table occurrence and utilize the same relationship to view related customer details from the invoices table occurrence.

If you do need a work around to address the tables directly for some reason, setup an ODBC connection to the DB and do it via SQL. You have been given other alternatives as well.

[fabriceN]

I'm happy to see that I am not the only guy on Earth to be missing this function badly. And every time I try to explain how it would be useful I face the same arguments.

So I'll try to explain again.

First, here are the 4 functions that I miss (the 2 between brackets are not as important as the two others)

TableName means 'table occurrence', for FileMaker calls TOs this way (except during certification exam

- GetBaseTableName ( TableName ) : returns

[- TableNames ( BaseTable ) : a list of TOs for a table ]

- GetTableName ( LayoutName ) : returns the table occurrence name for a layout (we already have Get ( LayoutTableName ), but it works only for CURRENT layout)

[- LayoutNames ( TableName ) : a list of Layouts for a TO]

Now let's imagine I want to create a DRY script able to create a new record in any table. (pretty simple, isn't it ?)

For a more understandable approach, let's say I want to create a record in a table displayed in a portal, without using the Allow creation relationship option (by script only).

Now, I simply can't : I have to know the name of the layout representing a TO of that table then do :

Go To Layout [ my layout name ]

New Record

With those two functions, combined with GetFieldName or named objects, as well custom function such as CustomList (a sort of ForEach), I could guess the name of the layouts representing all TOs of this table.

In the example I posted here (sorry LaRetta, second time in two days

http://www.bh-a.com/index.php?option=com_content&view=article&id=88〈=en

(download the FieldMap file or Saved Finds) or in Ray Cologon's recent Related Record Duplication sample, we use RelationInfo (now very useful with GetFieldName), but there are still parts that rely on a naming convention.

Those two functions would help us write 100% DRY scripts.

I think we have get used to rely on naming conventions far too much. A naming convention should just be about naming, it should not be NECESSARY to accomplish certain operations.

Hope it helps to understand the point.

I'd be glad to give more details if I haven't succeeded in explaining clearly.

Fabrice

[comment]

I still don't get it: suppose you have such function - what will you do with the result? Filemaker makes you talk to a table through a layout, which in turn belongs to a TO.

Whether one likes this arrangement or not, it has its own logic: it ensures that every reference is evaluated in some context - and one of the most powerful features of this method is that [color:blue]TO::Field can mean a LOT of different things, depending on the current context.

A script to create records directly in the base table sounds fine - until one considers how [color:blue]Set Field [ AnotherTable::Field ; ... ] should be interpreted in such a script.

[fabriceN]

But DRY scripts can of course not reference things such as AnotherTable::Field or even ThisTable::field.

It can however parse a parameter to set a value.

And with Set Field By Name and GetFieldName, it's more powerful than ever... making the lack of these functions even more sad.

[comment]

But DRY scripts can of course not reference things such as AnotherTable::Field or even ThisTable::field.

But Filemaker cannot know that you intended the script to be a "dry" one. It must have a mechanism to interpret a Set Field[] step, if the script includes such step.

I think your (and the OP's) objection is to the method itself, not to some or other point of implementation. As I said, there is a logic to this method, and it is based on making context of paramount importance. Alongside with the many benefits this also dictates some limitations, notably on "generic" solutions that would prefer to ignore context.

[fabriceN]

No, no !

There would be nothing to change in the method.

If your calculation is wrong, then the layout you go to is wrong too, and possibly in the wrong context, then set field would return an error, just as now. (relationship missing...)

I'm not saying you wouldn't need to go to the layout, I'm just saying that you could write generic ways of targetting it.

Edited June 18, 2009 by Guest

[LaRetta]

I'm sorry but I still see it. You can't do anything unless you do it to a table occurrence - you can script to create a record, set a field, export records, nada.

When you want a list of 'base tables' why not just name a specific table occurrence as your 'base' (if anyone even looked at my link?) - it's easy to specify the base by some designator and then use that list to get the 'base tables'). Your script would then loop through this list and create the records in those 'tables'.

All one needs to do is decide which occurrence will be their TABLE based upon how that TO is used in the graph (ie, part of the TOG which allows creation of records! Having a list of table names is meaningless because you can't do anything with a table anyway; what you want is a list of table occurrences which REPRESENT one complete set of your tables.

What's so hard about understanding this?

UPDATE: My goodness, you guys are typing fast!!!!

Edited June 18, 2009 by Guest

[comment]

OK, I can understand the demand for a feature that would allow you to go to ANY layout of a known TO - or perhaps even establish a context directly, without needing a layout as an intermediary*. But this has nothing to do with base tables.

---

(*) No, that wouldn't be right, because then script steps that work on the layout couldn't be interpreted.

But a function that returns layout names/IDs of a given TO could be useful.

Edited June 18, 2009 by Guest

[fabriceN]

it's just the next step :

Then I can go to any layout of a base table

I can also check, if there is a related TO to current TO for a given Base Table.

...and so on...

[comment]

I can go to any layout of a base table

It still doesn't make any sense to me. Going to a random layout of a base table means going to a random TO of that table - and that means all your references will be interpreted in a way that you cannot possibly predict.

[Genx]

Having a list of table names is meaningless because you can't do anything with a table anyway; what you want is a list of table occurrences which REPRESENT one complete set of your tables.

What's so hard about understanding this?

Okay, so I don't think they're complaining about the fact that your workaround doesn't work... just more that it's a work around.

Having a list of table names is meaningless because you can't do anything with a table anyway

To be fair, you wouldn't be able to interact with your table occurrences either unless they had an attached layout.

Admittedly it is a bit weird that there is no way of determining the base table name of a given table occurrence, given that you can do pretty much everything else. On the other hand, given that nothing has meaning without context in FM I can't necessarily think of a time when I'd use it unless I wanted to say scriptually create a new record in a particular table and set a few fields in that specific table... but then I'd probably just use a SQL plugin to trigger an insert.

Oh well :

[fabriceN]

Exactly. To do this kind of things you have to use the SQL interface or AppleScript... strange.

I have dozens of example where I use the naming convention to gess what Table I'm on.

In A/B schema, I use xxx__XXX__zzz

where xxx is the TOG name, and XXX is a Base table name.

Then if I want to point to the developer layout for this table (which I have to name XXX_raw), I have to parse the name.

It's doable (and often done !)... but it relies on a strong naming convention.

Another thing I'd like to do with it is some internal audit. Check if I have a relationship somewhere that would delete related records when I delete current record (here the TO is of no help, I need all TOs of the table).

[Genx]

See but the issue here is that even if they do introduce the function, it still has relatively little use - it doesn't let you talk at the Tables or even the table occurrences directly, you still need to talk through layouts which is relatively painful for what you're trying to achieve - so really it just becomes an extensively convoluted work around.

I mean I doubt it would be very difficult for them to integrate, and it is clearly a missing function, but it's also hard to see a potential use for it given the whole TO/Layout scheme that FM is built around.

I might prefer it if they would instead integrate the SQL calls as actual FM functions, but that would also introduce a whole new series of issues in itself and would not likely address the issues which you are trying to address...

Mind you I don't think anyone actually suggested a possible use for this other than me which was dynamically creating records / editing in a specific table.

[LaRetta]

LOL, Alex, well okay, it'd be nice to have a list of TABLES without a workaround but there are far more important things we need (such as ListFoundSet), sortable value lists, dynamically sortable portals, and NON-BREAKING import maps!

To be fair, you wouldn't be able to interact with your table occurrences either unless they had an attached layout.

It might make a bit of sense to allow export of records without a layout. But tell me ... if one is exporting calculations based upon a related table, or one is also exporting related [color:blue]fields, how would that happen without a layout to specify the context?

It just feels like much todo about nothing ... I say tomato; you say tomoto.

UPDATE: MY GOODNESS I'm slow today! Changed the word in blue from records to fields.

Edited June 18, 2009 by Guest

[Genx]

Lol, no I am aware that was an empty point, just figured I should make it anyway... haven't been saying much lately :

I actually agree with pretty much everyone in this thread. I think it's a clear omission but at the same time can't see much point to it given the clear requirement for context.

FileMaker really does need a better export / import system though. Updating existing solutions with newer files (where fields are just added) is a pain and and really shouldn't be anywhere near as hard as it is.

[LaRetta]

haven't been saying much lately

Yeah, it's good to see you hanging around again. :wink2:

[mr_vodka]

Ahhhh now only if there were a true separation model, then we could use the function. hehe...

Although when trying to keep scripts DRY as possible this makes sense, in the contextual world of FileMaker, I can not see it being a readily used function.

I know relying on a naming convention is a work around but the issue stems from the fact that lies within FileMaker's feature/limitation depending on how you see it to be contextual.

As for your point about having strict naming conventions, I think that one would STILL have to have strict naming conventions when naming layouts then even with a BaseTableName function.

Edited June 18, 2009 by Guest
strange double post

[fabriceN]

maybe have a look at the two files for which I posted a link (FieldMap and Saved Finds).

You can read the comments of the scripts (dependencies to naming conventions are hilighted) and understand how these functions would help.

Sorry, I don't agree with your last sentence: "I think that one would STILL have to have strict naming conventions when naming layouts then even with a BaseTableName function."

Cheers,

[Genx]

People keep using the term "Dry Script" - anyone care enough to explain?

Cheers.

[bruceR]

People keep using the term "Dry Script" - anyone care enough to explain?

Cheers.

Don't Repeat Yourself

http://en.wikipedia.org/wiki/Don't_repeat_yourself

[Genx]

Ah, its a real thing. Cheers Bruce!!