Understanding Tables vs. Database Files

[CatLady03]

I recently converted from FMP6 to FMP8. FMP6 didn't have tables...at least not the way I understand it. If I wanted a related file, I just created a separate one that I related to the first, with each containing a common field, and then I created the necessary relationship.

Now I'm trying to understand tables within a single file. I've ordered a book on FMP8, but it hasn't arrived yet. I understand how to define tables...I just don't understand how to relate them to the main file and populate the fields with data using a drop-down list. I know...this probably sounds too easy for words, but I'm missing something somewhere in my thought process.

Here's my problem. I have a book database for my boss's home library. Everything was going along just fine until he decided he wanted to assign categories to each book. There is a Primary Category and a related code (Business Management = BM) and then a Secondary Category with its related code (Accounting = BM-1). He wants to be able to click on one field and have a drop-down list of options (I believe this needs to be the Secondary Category), which is fine because I understand how to create value lists. However, once a Secondary Category is selected, I want the Primary Category and the secondary code to populate automatically based on the Secondary Category.

For some reason, I just can't wrap my mind around this one. Any help would be greatly appreciated.

Debbie

[PeterHW]

Hi Debbie,

'tables' were a fairly foreign concept before FM7. Actually, they are a fundamental concept in relational database theory. Basically, a table is what in pre-V7 versions was equal to a file : a collection of fields, which together made up a logical entity of the item you wished to represent (in this case "Books").

There are many migration papers available on Filemaker's website, I'm sorry I can't provide a direct link, I'm sure others would be able to.

One of the main transitions when going from V6 to V7/8 is that you are now able to use (many) more than one table per file, while up to V6, one file could only hold one table.

In this case, you would require three tables :

1.Books

BookID (auto-enter serial)

BookDescription (text)

Category (text, Lookup based on Books<=>Subcategories)

SubCategory (text)

2. SubCategories

SubCategoryID (auto-enter serial)

SubCategoryName (Text)

Category

3. Categories

CategoryID (auto-enter serial)

Category (text)

Then, you relate Books to Subcategories, based on SubCategory. The value list can be defined by using "Values from Field", selecting Subcategories as the table and then the field SubCategory. Leave "show all values" selected.

Then, Subcategories is related to Categories, based on Category.

Note that you can only define the Lookup in Books once the Relationship has been defined. The other "Category code" field can be looked up in exactly the same way.

Regards,

Peter

[CatLady03]

Thank you, Peter. Your explanation was most helpful. I kind of figured that tables are to FMP8 as files are to FMP6. I just wasn't sure if the interaction was the same, but it is. It's just more convenient to have everything in one file...much better.

Now, I have one last question. Since the records in the database already exists and BookID is a new auto-generated number field, how do I populate the field with the serial number? I looked in help and found this script:

Go to Record/Request/Page [Last]

Set Next Serial Value [book Library::Book ID; Book Library::Book ID + 1]

Obviously, I have substituted my file/field names in their example. The problem is that nothing happened when I ran the script. I tried changing Last to First and put a 1 in the very first record and then ran the script, but nothing happened. The one thing it doesn't describe is how the script is used once defined. I'm missing something somewhere.

Also, according to your recommendation, I need to populate three different auto-generate fields, BookID, SubCategoryID and CategoryID. How do I populate all of these fields when the records already exist?

Can you help, please?

Thanks!

Debbie

[PeterHW]

Hi Debbie, glad I could help. You'll find many more benefits from the new features of FM8, I'm sure.

If I understand correctly you already have some data, but you don't have a BookID field. You will need this to establish relationships to Categories and Subcategories. In general, you should always use a unique ID (auto-enter serial) field in all your tables, you might need them someday.

The best way to set a serial value for existing data is using the "Replace field contents" command from the Records menu. Put the BookID field on a layout, make sure all records are in the found set by choosing "Show all records" and put your cursor in the BookID Field. Then select "Replace field contents". Then choose "Replace with serial numbers" Set the initial value to 1, as well as the increment. Make sure the "Update serial number in entry options" is checked.

What the script you mention does is something else: when you set a field to auto-enter serial, you can also specify a "next value" and increment. Each time a new record is created, Filemaker adds the increment to the value of the previously created record. However, you can always manually change the Serial number. Let's say you delete a record and wish to reset the Serial Number, so that the next record that is created does not have a gap with the previous one. Then you go into field entry options and manually lower the "next value" with 1. Note that it's not a good idea in general to change Serial Numbers, especially not if they have been around for some time, changes are VERY high that you'll mess up relationships that rely on that Serial Number field.

This is actually what the "Set Next Serial Value" script step does : it sets the value in "next value" in the field entry options to what you specify. In your example, the script simply goes to the last record, takes the value of BookID (which is empty), adds 1 ands sets this as the "next value" in field entry options for the BookID field. So no data is actually written to the field.

This is also the meaning of the "Update serial number in entry options" : it makes sure that after the serial values have been entered, the next value in field entry options is set to the last value + 1. That prevents that there could be any duplicates (which would cause problems with related data).

Hope this helps, regards,

Peter

[CatLady03]

That worked GREAT, Peter...thanks!

Now, I'm still having trouble with the SubCategory and SubCategoryID fields. They aren't open to modification in Browse mode and I believe I have them setup correctly and the behavior is set as Edit Box.

Something that just hasn't yet jelled in my mind is how to populate data into one table so it can be used in another. Here's what I want to do from the main file: Select the SubCategory from a drop-down list and then have the related SubCategoryID auto-fill based on the SubCategory. I don't really want an auto-generated number, because the prefix to the ID is based on the Category used, i.e., Business Management<=>BM, Health and Nutrition<=>HN, etc. So I have a Value List that provides the Category, one that provides the SubCategory Name and one that provides the SubCategoryID, which is based on both the Category and the SubCategory, i.e., Accounting<=>BM-1, which is the first SubCategory for the Category [business Management]. Does this make sense?

Maybe I don't need all of these Value Lists, but I don't know how to "pre-populate" the list of Category and SubCategory Names in the specific tables. When they were separate files in FMP6, I had a basic layout in each sub-file where I added each record and then created the relationship to display them in the master file. I don't know how to do this with tables. I'm obviously missing something very simple and will kick myself when you tell me what it is.

I know I'm close, but I'm not there yet. At least now I have a BookID, and although I haven't used it for anything yet, I now know how to do this.

Thanks for your continued assistance...it is much appreciated!!

[PeterHW]

Hi Debbie,

I really think it would be very helpful for you if you looked into the help files, particularly the section about relationships and the way they work.

Tables and files were the same thing in pre-V7 Filemaker. So if you could make relationships to your files in V6, you should be able to do the same thing in 7/8. Relationships are based on so-called "key fields". For every table you wish to use in a relationship, you would need what is called a primary key, i.e. a field which uniquely identifies each record in that table. Typically, this is a serial number, although there are many other methods that are much more complicated, however you don't need to go into that right now.

Take a look at the attached sample file. Go to the Categories layout. Here you can create new Category records. To enter subcategories to a category, use what is called a portal, i.e. a window to a related table, in this case subcategories. You will have to set the relationship to allow the creation of records. Now, each time you enter a new subcategory through the portal, Filemaker automatically sets the CategoryID field in Subcategories to the CategoryID value from the record in Categories where you are entering them from. So the relationship is automatically created.

If, such as in your case, you already have data but no relationships, you will have to manually create the relationships by entering the correct CategoryID's in SubCategories to correspond with the CategoryID of the related Category record.

In stead of using ID fields, you can also use CategoryNames, which I suspect you have done in the past. So your relationship from Category to Subcategory was based on CategoryName. This works fine as well, but I would not recommend doing this. Why ? Because fields that are meaningful to us humans are prone to change. You might, at some point in the future, decide to rename the category Business Management to simply Management. The result would be that the related records in subcategories, which rely on CategoryName, are no longer related (the records in SubCategories still have Business Management, while the records in Category now have Management : no match).

So in general it is regarded good practice not to use meaningful names for primary key fields, but use serial numbers in stead. Then, if you choose to rename Business Management to Management, the related records stay related, since the ID field did not change.

As mentioned, in your case you should manually enter the ID fields to establish the relationships. However, although I just explained why you shouldn't base relations on fields like CategoryName, you could now temporarily create such relationships (based on CategoryName and SubCategoryName), and using these relationships, perform Lookups for the CategoryID and SubcategoryID fields in Books and the CategoryID field in Subcategories. This would save you from having to manually enter all ID fields. So : create a relationship from Subcategories to Categories, based on Category Name (I assume this field is already present in Subcategories). Then, set the CategoryID field in Subcategories as a Lookup, based on that relationship. Lookups are only performed if the field on which the lookup is based is changed. So, you will have to do a re-lookup. Go to the Subcategories layout, put your cursor in CategoryName and choose "Relookup Field contents" from the Records menu. This will perform a relookup for all records in the found set. All records should now have the correct CategoryID. Now, delete the relationship based on CategoryName and use the ID-field relation that I suggested. Uncheck the Lookup option for the CategoryID field. You can do the same thing for Books.

The attached sample shows two setups (note that you can use a table more than once on the relationship graph ?), one that uses a 'logical' approach, i.e. when creating a book, you first specify a category, then a subcategory. The second allows you to use your proposed setup, i.e. choose a subcategory and then get to see the Category and Category code.

The file is meant to get you started, but I really urge you to further investigate and study filemaker and database concepts, this will be infinitely more helpful.

Good luck and regards,

Peter

Books.zip

[CatLady03]

Thank you so much for your time, Peter. You have been most helpful...more so than I ever expected from anyone.

I believe I have it now...you're the best!!

Debbie :worship:

[mluka]

Hi, PeterHW.

I'm also trying to get my head around something in a relational database. I looked at your example file, "Books.zip" and found it helpful.

Here's my question: is there any way to NOT display the ID fields (say for Category or Subcategory)?

I understand that the ID of the related record is what needs to be stored in the foreign key field of the primary record. But I don't want to display that ID because it is very "unfriendly", being just a raw number, in most cases. That number is totally meaningless to users. Not so the "description", which is what they're really after.

For instance, in my application, I want to use a related table for cities (named "Cities", as part of the address for a Contacts file. This would avoid having to store the full name of a city in each record of the Contacts file. Presumably, this would save space as it's much shorter to store the ID "23", for instance, instead of "White Plains".

Also, we're in an environment where there are still city mergers going on, with the attendant name changes. I'd love to be able, if need be, to just change the name of the city in the "Cities" table and know that all the records (in the "Contacts" table) that are related to that city are up to date. (I know, of course, that I could store the full name in the Contacts table, do a "Find", then a global replace, etc... I don't want to do that.)

BUT, I just can't figure out how to NOT display the "23" and just display "White Plains".

Thanks for your help!

[Genx]

... Delete the field off the layout? And add the related name field instead?

[mluka]

Hi, Genx; thanks for the response.

If I delete the field, how do I establish the relationship??? In other words, how do I specify that for THIS contact, the city is (ID="23", City_Name = "White Plains".

In other words, on the layout that allows me to create (and modify) the Contacts records, what am I supposed to do if I only want the city name and NOT the ID for that city, to appear?

I'm attaching the simplified relationship diagram for the tables involved.

Thanks for your help.

Relationship.pdf

[Vaughan]

Genx means for you to delete the field from the *layout* not from the database structure itself.

[Genx]

The structure seems a bit rudementary at the moment. Before i answer your question decide if your cities will be unique, if not, will they be filtered by state or some other narrowing definition?

[Genx]

Yeh, Vaughan got what i meant. The one issue that concerns me however is why you would have that sort of identifier for a city..

[mluka]

Hi, Vaughan.

OK. So I delete the field ("fk_City_Id") from the "Contacts" layout.

Now, I want to add a new contact; its key ("pk_Contact_Id") is assigned automatically by FM; let's assume it's "4635". I enter the contact's name: "Smith" (in the "Contact_Name" field on the "Contacts" layout).

OK. Now I enter the contact's street address: let's say "123 Main Street" (in the "Street_Address" field on the "Contacts" layout).

OK. Now, what is going to tell FM that Smith lives in "White Plains" (which happens to have a "pk_City_Id" of, say, "283")?

Thanks.

I'm joining the example file I've been referring to.

Contacts.fp7.zip

[mluka]

Hi, Genx.

Yes, each city will be unique.

[Genx]

In that case... why not just use the city as the primary key?

The only issue with this is the possibility that someone mis-spells it and changes it later. Though if you use a lookup its not necessarily as bad.

In fact you could select the city name and then use a lookup over a second table occurance.

I.e.

Contacts CityLookup

CityName = CityName

Contacts City

fk_City = pk_City

Your original selection could be made using city name and foreign key could be a lookup field over the second relationship.

Anyway, point is, either way you shouldnt have to select a city via it's key if it's key has no meaning.

[mluka]

Hi, Genx.

I think that for the time being, I'll just go along witht the "standard" approach: I'll just add a "City" field in the Contacts table and that'll be that. Lots of redundancy, no normalization, etc., but what the hell, I've got to move on!

Thanks for your comments.

[Genx]

Your choice i suppose. But in that case just use a value list with two values with the id key being stored.

[mluka]

I FOUND A WAY! I FOUND A WAY!

1- Define a value list that displays the contents of both fields (pk_City_ID and City_Name).

2- On the layout that allows creation/edit of Contacts, put the field fk_City_ID and set it up as a dropdown list, using the value list defined in step 1. The behavior of this field should be: "Allow field to be entered in Browse and Find modes"; I also picked that entire contents be selected.

3- On the same layout, add the (related) field "Cities::City_Name"; set it up just as an Edit box, but its behavior should be THAT IT CAN'T BE ENTERED IN Browse mode!

4- Now, slide this field exactly over the one added in step 2 and Voilà!

When you enter the field (well... the two-fields-one-on-top-of-the-other), you see a dropdown list that shows the key and city name; after you pick the one you want, all you see is the city name. Yet, all that goes in the file (Contacts, that is) if the ID of the city.

In my book, that's having my cake and eating it too!

To give credit where credit is due: I got this idea from iSolutions' "Client Tracker" which came in the "FileMaker Creative Pro Starter Kit".

Thanks to all!

[CatLady03]

SOMEONE HELP!!

I'm still having trouble fully understanding how to use tables. I mean, I get some of it. What I don't seem to fully understand is how to create additional layouts for just the fields ONE table when it's not the main database file.

Here's what I'm trying to do. I have a table called IS Reps that contains all of the fields I want to print. There are no fields from the main database file. I have 98 records in the IS Reps table. There are multiple reps per company and I'm just trying to print the reps by company. It certainly seems simple enough. The IS Reps table is joined to the main file by Company, however ALL of the fields in the report I'm trying create are from the IS Reps table, including Company.

This is what's happening. When I look at in Browse mode, I see that the 98 records I want to print are showing up like I want. However, when I go to Preview mode, all I see in the fields is "Unrelated Table." I'm so confused because although I have created relationships between ALL of my tables, they ALL appear as "unrelated tables" when I pull up the list to specify a field. Why does this happen? It's making me crazy because I'm sure I'm missing something really basic here.

Sometimes we beginners just can't see the forest for the trees!!

Debbie :confused:

[LaRetta]

Not to worry, Debbie. The trees which block your view of the forest is your own current design. Here is what helped me ... set it aside. Take a new file. Create small relationships with 20-30 records only. Begin mixing and placing fields. Begin creating new table occurrences. Because, what seems to happen, is that when working in a REAL solution, it becomes TOO important that we do it right. And, just like being afraid to ride a horse lest we get bucked off, the aprehension will impede your learning of it.

It seems like it will take longer but I assure you that the reverse is true. Without all the clutter of your real structure around you, the trees will disappear and you will see more clearly. Here's something to remember: You can NOT access a table. Period. You can only access a table occurrence (which is a representation in your graph of a table). All table occurrences are just as real and solid as the next.

If you wish to attach your file, we can show you the problem. But your graph will also show you. What is the table occurrence of the layout? The fields placed on that layout should be from the same table occurrence as the layout OR ... from a table occurrence directly related (look at your lines). If you have 3 table occurrence of your Companies table (for example), but only one is related to your Contacts (and it is called 'Contacts Companies') then THAT is the TO the field should be based upon and NOT a table occurrence of Companies which is attached elsewhere in your graph called 'Terms for Companies.' And you must make sure the relationship is valid. We've had many discussion on that one ... how the child table key field must be indexable.

Testing in a raw file is powerful. Testing in a real solution is scary. When you only have a few records and only a few relationships, you can see the power of 7/8 clearly. I know how you feel but there is only one way out and that is through ...

LaRetta :wink2:

[Genx]

I thought it was the jungle through the ferns.

Anyway, go in to layout mode.

Choose layout setup and make sure your layout is based on the same table occurance as you have sourced all of your IS rep fields from.. Note that there is a fairly large difference between Table Occurances and Tables.

~Genx

[Genx]

Oh well there you go. LaRetta explained the difference for you .

[CatLady03]

Actually, LaRetta, I just created a copy of my real database to work with, so I'm not afraid to work with anything in it. There aren't thousands of records, so it's not a huge file to begin with. One of the tables has thousands of records, but not the main one.

Anyway, what I'm trying to say is that I'm not afraid to "play" with this database. I'm doing anything and everything I can think of and I'm still not getting it.

I love this new version, but I'm going a little crazy with the conversion. I could have left well enough alone and just kept the related files, but I really would prefer that there only be one file to contend with, so I'm trying to create the tables I need in the master file. I do want to fully understand tables, so I'm digging in.

Thanks a bunch!

Debbie

[color:red]I think I just discovered my problem. I read something that Genx said and it clicked. Somehow the layout lost the relationship to the file the records are supposed to come from, so now I'm trying to fix that. I just didn't think about going there after I created the layout. It's the little things!!

[LaRetta]

What is the table occurrence of the layout? The fields placed on that layout should be from the same table occurrence as the layout OR...

When you updated your post saying you think you've discovered the problem, I deleted my response. But Debbie, your post was more than just a broken report. And THAT was the focus of my response. I know you aren't afraid and I didn't mean you were. It's just that when something is important, we tend to move a bit too cautiously and true learning takes place when caution is thrown out the window.

You'll get it; I've no doubt. :wink2:

[CatLady03]

[color:red]

What is the table occurrence of the layout? The fields placed on that layout should be from the same table occurrence as the layout OR...

This is EXACTLY what my problem was. All problems are resolved. You see, I knew it was something easy. It's just so new I found myself in the dark...but I've seen the light!!

[color:red]

I know you aren't afraid and I didn't mean you were. It's just that when something is important, we tend to move a bit too cautiously and true learning takes place when caution is thrown out the window.

I wasn't defending being fearful...I was trying to express that I wasn't hesitating or being too cautious. I created the copy of the database for that very reason. I just didn't know what the heck I was doing! I do love when it all comes together, though. It's exhilirating!!

Thanks for coming to my rescue again...you're a trooper!!

Debbie