Awkward Relational Database in FileMaker 7

[eYz]

I'm trying out FP7 and find its new relational database thing to be really awkward. I've been a database developer on Windows platforms for years. Maybe MS technologies have too much influence on me. But I really don't appreciate FP7's second-hand / amateur way of handling relational databases. For example, multiple instances of related tables make it so awkward and confusing to define calculated fields using relations. The relations are not directional. There isn't the concept of primary/foreign keys, which is pretty much the foundation of any real relational database engine. There's no cascading update. The "layout" based UI is clumsy and inefficient when you have a fairly sophisticated database.

I guess there's no serious "quick-development" database application for Mac yet. Going over those free or paid third-party FileMaker solutions, I haven't found anything that compares to a fairly common fully customized MS Access solution that I can complete in a couple of days.

Any advice?

[H.P.]

I came from common db background in sqlserver and access too... it was hard @ the beginning to adjust to filemaker but once you got used to how it works, it's so much faster to develop small to mid-size database application compare to access.

The main problem I found with filemaker is that if you have a "huge" db, there will be ways too many occurances of the same entity. Also, if you want to have restricted query, you will have to modify the tables and put in global fields and/or calculation field, which directly violates the main rule of relational db

I still miss the sql query and the programming capability of common db though. Woudln't move to filemaker unless I have no other choices

[H.P.]

Oh, regarding some of the limitations you metion:

1. Calculation fields work pretty well with multiple occurances, which problem did u have?

2. Relations r not directional? I don't really understand what you mean here

3. Primary/Foregin key exist in filemaker. They just aren't explicity called primary/secondary as in access. When you create relationships, filemaker automatically create primary/foreign keys.

4. There is cascading update in filemaker. Double click on the relationship, you will see the 2 fields that will allow you to change cascading update properties.

Lastly, filemaker is one of the quickest development db I have seen, although it does have many limiatations (unless you use advanced server version or through third-party)

[Rangoon]

Hi EYZ,

Are you strictly Mac or will you need to migrate to Pc,s?

Harry

[Tripod]

I know what you mean, I grew up with the dot prompt and writing dbase files (geez, I'm old).

Filemaker is different. It's also very fast and extremely easy to use. There is more to learn all the time. Database rules to achieve certain normalization levels, etc... are just as important in FMP as for Access. Some projects need to be taken to the next level, others are fine a 1N. The difference is that Access makes you pretend you know something about good design, wether you do or not, and FMP just lets you take the ball and run with it, like any good Mac program.

Have fun,

--Tripod

[eYz]

In reply to H.P.'s questions:

1. Calculation fields work pretty well with multiple occurances, which problem did u have?

- How do I create a calculated field that requires data from 2 related tables in different "contexts" ? What I ended up doing was to re-organize all the relationships so that the 2 related tables I needed were in the same context. And I had to modify a number of other tables because of the change of relationship paths.

2. Relations r not directional? I don't really understand what you mean here

- Well, traditional/common RDBs have one-to-many and many-to-many relationships. FP7 seems to get this and automatically shows different arrows at the ends of a relationships. However, when you double-click a relationship, the options for both tables are the same. Why would I sort records in a table on the "one" side?!

3. Primary/Foregin key exist in filemaker. They just aren't explicity called primary/secondary as in access. When you create relationships, filemaker automatically create primary/foreign keys.

- Why so? Not to confuse the user or to confuse the developer?! I could use some clear and explict indication of those keys so that I won't accidentally screw things up.

4. There is cascading update in filemaker. Double click on the relationship, you will see the 2 fields that will allow you to change cascading update properties.

- No, there isn't. It only supports cascading deletion. What I want is: when the primary key is changed, all foreign keys in the related table is changed as well. This is very important in situations like updating a product code.

[eYz]

I'm primarily on Mac now. I'm working on an all-in-one small retail business solution tailored to my own needs. It does sales, purchase, inventory control and accounting. My partners are all on PCs, so I'll also need to use the file in Windows versions of FP.

[H.P.]

Hi EZ,

1. Calculation fields work pretty well with multiple occurances, which problem did u have?

- How do I create a calculated field that requires data from 2 related tables in different "contexts" ? What I ended up doing was to re-organize all the relationships so that the 2 related tables I needed were in the same context. And I had to modify a number of other tables because of the change of relationship paths.

This is confusing : I'm not clear on what you mean by different context? Does it mean not related in the table of ocurrances?

2. Relations r not directional? I don't really understand what you mean here

- Well, traditional/common RDBs have one-to-many and many-to-many relationships. FP7 seems to get this and automatically shows different arrows at the ends of a relationships. However, when you double-click a relationship, the options for both tables are the same. Why would I sort records in a table on the "one" side?!

FM7 cannot automatically create M:M relationship by itself. You would have to add a middle table by yourself and create relationship b/w the 3 tables for M-to-M. Once you done that, it will be exactly the same as in Access.

3. Primary/Foregin key exist in filemaker. They just aren't explicity called primary/secondary as in access. When you create relationships, filemaker automatically create primary/foreign keys.

- Why so? Not to confuse the user or to confuse the developer?! I could use some clear and explict indication of those keys so that I won't accidentally screw things up.

Yes, I agree. They could put _FK or _PK @ the end of the attribute to make it clearer

4. There is cascading update in filemaker. Double click on the relationship, you will see the 2 fields that will allow you to change cascading update properties.

- No, there isn't. It only supports cascading deletion. What I want is: when the primary key is changed, all foreign keys in the related table is changed as well. This is very important in situations like updating a product code.

I wonder why would you want to change the primary key? Primary is usually there to stay and only be changed in extreme cases. I would not consider a product code a primary key because it's a changeable attributes. ALthough I would set product code to unique and not null, I personally will not use it as primary and use another serial attribute as primary key instead. This way, if I change the product code, the product code in related tables would be changed as well.

[Tripod]

Absolutely! The primary key should NEVER change. Else, it's not a well designed database.

Like I said, Access makes you FEEL like you know what you are doing. Sounds like they have some crutches built in--you can do the same with FMP if you want to throw basic fundamentals of database design out the window(s), FMP is pretty good about giving you the tools to root through and modify all the necessary (cascading!!!) changes that would occur when (gasp!) _changing_ a PK. But make no mistake, that's a crutch, a work around, not good design.

[eYz]

This is confusing : I'm not clear on what you mean by different context? Does it mean not related in the table of ocurrances?

- Actually the word "context" is how FM calls the multiple instances of a table in the relationship chart. When I try to create a calculated field, the pop-up list on the top of dialog window forces me to choose one context only, which means I can't use data from two other tables related to the current one in different contexts. Or am I wrong here?

I wonder why would you want to change the primary key? Primary is usually there to stay and only be changed in extreme cases. I would not consider a product code a primary key because it's a changeable attributes. ALthough I would set product code to unique and not null, I personally will not use it as primary and use another serial attribute as primary key instead. This way, if I change the product code, the product code in related tables would be changed as well.

- As a matter of fact, it is because of the awkwardness of FM that I have to use product code as a primary key!!! Here's the scenario. A user needs to either type in the product code or pick it from a pop-up list when registering sales. The list should at least show product codes and product names. In Access, this is easily done by using a query as the source of the listbox and bound the proper column to the field. However, a value list in FM can have only two columns and the field always shows the content of its bounded column. My users are only interested in typing and seeing the product code instead of the auto-generated serial no. As as you said, product codes do change fairly often.

[eYz]

Tripod said:

Absolutely! The primary key should NEVER change. Else, it's not a well designed database.

Like I said, Access makes you FEEL like you know what you are doing. Sounds like they have some crutches built in--you can do the same with FMP if you want to throw basic fundamentals of database design out the window(s), FMP is pretty good about giving you the tools to root through and modify all the necessary (cascading!!!) changes that would occur when (gasp!) _changing_ a PK. But make no mistake, that's a crutch, a work around, not good design.

Please read the 2nd point of my latest reply to H.P. I'd really like to know a "work round" in FM.

[smorr]

Tripod said:

FM7 cannot automatically create M:M relationship by itself. You would have to add a middle table by yourself and create relationship b/w the 3 tables for M-to-M. Once you done that, it will be exactly the same as in Access.

Actually it is very straightforward to create M to M relationships in filemaker as each line of a text field or each repetition of repeating field is indexed as a key when used in a relationship. However, my experience is that the better way of properly implementent M to M relationships is through the link table -- it allows for better management of what links exist between two data tables.

First EZ Said

- How do I create a calculated field that requires data from 2 related tables in different "contexts" ? What I ended up doing was to re-organize all the relationships so that the 2 related tables I needed were in the same context. And I had to modify a number of other tables because of the change of relationship paths.

Then H.P Said

This is confusing : I'm not clear on what you mean by different context? Does it mean not related in the table of ocurrances?

What I think he means here is that a datatable has more than one table occurance and that you want to build a calculation based on more than one "Base" table occurance. The only way I can see doing this is to create a calculation field based on the first context and then use that calculated field in the second calculated field that is based on the second context.

[H.P.]

<quote>- Actually the word "context" is how FM calls the multiple instances of a table in the relationship chart. When I try to create a calculated field, the pop-up list on the top of dialog window forces me to choose one context only, which means I can't use data from two other tables related to the current one in different contexts. Or am I wrong here? </quote>

---> In that case, simply add new occurances to the database. You don't have to change any relationship, just add new occurance and link it to the current context. Everything else stay the same, and you still can use the calculated field.

<quote>- As a matter of fact, it is because of the awkwardness of FM that I have to use product code as a primary key!!! Here's the scenario. A user needs to either type in the product code or pick it from a pop-up list when registering sales. The list should at least show product codes and product names. In Access, this is easily done by using a query as the source of the listbox and bound the proper column to the field. However, a value list in FM can have only two columns and the field always shows the content of its bounded column. My users are only interested in typing and seeing the product code instead of the auto-generated serial no. As as you said, product codes do change fairly often. </quote>

---> I agree that it is easy to do this in Access with a query. You can do the same thing in Filemaker but you would have to use calculated field. THis is how I would do it, pls correct me if anyone have a better method:

1. Create a calculated field that is = product codes & product name (do not indext the field)

2. Create a value list that displays the serial primary key as the first field, and the calculated field (in #1) as the second field.

3. On the form you want to display the pop-up menu/list, display the primary id field. Go to field format and make it display the value list in #2. Afterthat, make that field transparent.

4. Put the calculated field on top of the transparent field and make that field not enterable in browse mode.

After you finish those steps, the user can select the value from the list of product code & name. It will automatically assign the primary key to the related table.

If you use this approach, the database design will be in good form. You don't need to use the product code as the primary key and it hides the auto-generated serial # from the user.

[Mike D.]

I would highly recommend that you take a look at the two free Introducing FM 7 videos on this Filemaker Magazine website. I think it would answer a lot of your questions.

Mike

[cellmaker]

I don't know if this is the best solution, but I'll tell you how I've solved this problem in the past.

If I'm creating a new record in an M-M join table, I want my user to be able to pick a human-readable element, not pick from a set of keys. Obviously, I need the key, though, to get the proper key reference from the other file.

In this example, I have clients in one file and group counseling sessions in another file. In order to have a record of which clients were in which sessions, I need a join file, which I'll call client_sessions.

If I'm in the session file, I want to show client_sessions in a portal with their names, though the defining keys for that join file will include the session key and the client key. (The file itself has its own primary key, which is auto-generated when the related record is created.)

So now I'm in Session 6, for example, and I want to pick clients from a drop-down list to add them to the client-session file. If I create a new record, my session key will get copied into the join file. But I need my client key to also be placed into that file. Therefore the client key "normally" would be the entry element in the join file. (With the client key in, the session key will be auto-entered because I'm making the new record through the relationship from the session side.)

Instead of trying to put the key in directly, I'm going to use a temporary lookup based on the name of the client.

So underneath the display field in my portal (which is the name of the client) I put a second, global field (in the join file), which uses a pop-up list based on client name in the client file. My display field on top I set to non-enter and make it a button connected to a script. When I click on it, I have it check to see if the join file's related value exists (i.e., there's already something there in that portal row). If it doesn't exist, I must be in the last row of the portal, and the script goes to the global field underneath, which triggers the pop-up list. Then I pick the name I want, which puts a value in the global field and creates the related record in the join file.

Now, I have a second relationship between the join file and the client file, which is global clientname <--> clientname. The client key in the join file is set to auto-enter as a lookup based on this global relationship, so once the name matches a name in the client file, the client key is entered into the client-session join file. The name then appears in the display (top) field in the portal. You can mask the global field underneath in many ways, so that it doesn't show underneath. (container field between the name display and the global name field; set global field color to the same as the background, etc.)

One drawback to this is that all of your client names must be unique. You can either make sure, through some twisted mess, that all client names are indeed unique, or you can force the issue by using a concatenated field for this relationship based on client name and something unique such as recordID. (This isn't very pretty to look at in a dropdown list, but it works fine.)

Another issue in something like this is limiting the pool of possibilities for your drop-down list, but that's a different discussion.

Hope this helps, or is at least food for thought. Sorry if it sounded convoluted. Short and sweet: don't put in the "other file's" key directly. Have the key looked up by a secondary relationship based on human-readable data.