Transportation Administration System
Snoezelen Pillows for Dementia
Begeleiders voor gehandicapten
Laat uw hond het jaarlijkse vuurwerk overwinnen
Betuweroute en Kunst
Hey Vos! Je eigen naam@vos.net emailadres?
Kunst in huis? Nicole Karrèr maakt echt bijzonder mooie dingen
Kunst in huis? Netty Franssen maakt ook bijzonder mooie dingen | Relations, Related records and |  |
How to use relations, read/update related records and use value lists from other files.
Tweet
Step 1 - Problem:
I wanted to use my own number sequencing instead of the one Filemaker offers because then I have no problems whatsoever with ex- and importing records. What to do?
Step 2 - Think it over
On our AS/400, I use a special sequence number file for all sorts of data that needs a unique id. What if I could use the same setup with FileMaker?
I use a unique key to read a number sequence from this file, which has fields that make up a sequence: lowest value, highest allowed value, next value and step size (or increment size). Filemaker uses the same fields for its numbering. So I'm not unique, it's just the most flexible setup. But how can I read a record from another file, like I do on the AS/400? There's no script step for it. So I dived in the books and the only way to read the record is not to read the record: let FileMaker do it automatically by creating a relationship from the data file to the sequence number file. FileMaker automatically activates a related record for you! That's the solution!
Step 3 - Create the Sequence Number file:
Start the FileMaker Pro application and create a new empty database. Call it 'Sequence Numbers.fp5' or so and create the following fields:
For the fields LOW, NEXT and STEP you should activate the 'Auto-enter Data' option and enter the value 1. For HIGH, enter 99999999 as the 'Auto-enter Data' value.
Go to BROWSE mode and enter some records, including one record where CODE has the value 'CITY':
Step 4 - About the Data Files:
Anything will do as data, but I want a file for registring CITIES. Since most cities reside in countries, so I also needed a file to hold Countries. Because I want to use the countries in my cities, I have to create the country file first.
Step 4a - The Country file
Create a new empty file and call it 'Country.fp5'. Create the following fields:
Now, I already know that when I am entering Citynames, I do not want to TYPE the country in, I want to select it from a list. Since I have a file with countries, that's also the place to create such a list because it will be available this way to all other future files that need country codes.
Choose File -> Define Valuelists... and click New. Name the new list 'Countries', click the radio button
and specify the list parameters:
and click OK. Click OK again, and your list window now has 1 list in it:
So far so good. BUT... I also know that I do not want to type the name of a continent each time I add a new country. I want to select it too, just like the country code. Another value list is needed. Click New... and name the list 'Continents'. This is going to be a list that only uses values from already entered continent names. But you can also choose to make it a fixed list. To make it dynamic as I did, click
and choose the field 'Continent':
and click OK. Click OK again, and your list window now shows 2 lists. Click DONE.
Now we want the field 'Continent' to pop up the list we just created. To do so, enter LAY-OUT Mode and select the field 'Continent'. Next choose Format -> Field Format... from the menu and assign the value list as a pop-up list:
and click OK. Go to BROWSE-Mode and enter some coutries and see the pop-up list do its work. Now it's time to create the file CITY.
Step 4b - The City file
Create an empty data file and define the following fields:
I want the country field to hold country codes that exist in the country file and the user (me) is not allowed to override this restriction. To restrict the field select it first and click OPTIONS. Select the VALIDATION Tab and select the following options:
The valuelist 'Countries' must be created to use the value list in the Country file, which is the value list I really want to use. When you select 'Member of value list' the pop-up menu displays '
and then select the file 'Country.fp5' and the value list 'Countries' as shown here:
and click OK. And click OK again. Your list window should look something like this:
Click DONE and click OK. Now, goto LAY-OUT mode and select the field 'Country'. Next choose Format -> Field Format... from the menu and assign the value list Countries to the field as a pop-up list. Click OK. Since you entered some countries, goto BROWSE mode and add a new record and see the country value list pop up when you enter the 'Country' field. This is a Related Value List.
The moment you enter the record, the related record from the country file is active too, so I can show the name of the country too, instead of only the code. Goto LAY-OUT mode and choose Insert -> Field... from the menu. In the window that appears, choose 'Country' from the pop-up menu at the top and select the related field 'Name':
and click OK. Drag the field to a location you like (left to the country code is a logical place) and go to BROWSE mode. And see, the name of the country in your first record appears on the screen. This is the nice thing about a relation. You can use all the fields from the related record on your lay-outs and in your scripts.
Now the moment has come to link the cities file to the sequence numbers file and assign each record a unique sequence number. I already said that a related is immediately active when the relation is established. This means you must create a relation to the sequence number file. But the problem is that relations concern records and we do not want a per record relation to the sequence numbers, we want a FILE relation to a sequence number record. This can be achieved through the use of a global field. Global fields are file-based and do not belong to a record. That is why the field
was created.I want to create a relation for the file CITY to the record with code = CITY. I want to fill the field gSeqCode with the string 'CITY' and create a relationship with this field to the sequence number file. Choose File -> Define relationships... from the menu and click NEW.... Name the relationship 'Sequence Numbers' and specify the file 'Sequence Numbers.fp5' as the related file. Choose the fields that should match eachother to create a relationship:
and click OK. Click DONE.
Now, to make sure the relationship to the 'CITY'-record is always active, you must fill the global field with 'CITY'. Since FileMaker has no 'Auto-enter Data' options or so for global fields and you do not want it to be on the lay-out (why should it be there?), a script will do the job. This script will be used as a startup-script when you open the CITY-file, to make sure the field is filled. Choose Scripts -> ScriptMaker™ from the menu, name the script 'Setup globals' and click Create. Enter the following statement:
and click OK. Deselect the 'Show in menu' option left to the name of the script and click DONE. Choose Edit -> Preferences -> Document... from the menu. Select the two 'Perform script' options and select 'Setup Globals' as the script to run on startup:
and click OK. You do not have to select 'Perform script when closing', I did it just to make sure the field always has the same value. Now close the file en re-open it. The script has run and gSeqCode contains 'CITY' and FileMaker Pro has created the relation with the 'CITY' record.
To fill the field CODE with a sequence number, you have to use a script to retrieve the NEXT value and increment it for the next new CITY record. The NEXT value you retrieved has to be put in the CODE field, but only when the CODE field is empty or zero. Choose Scripts -> ScriptMaker™ from the menu. Name the script 'Update Sequence Number' and click Create. Enter the following script statements :
and click OK. Deselect the 'Show in menu' option left to the name of the script and click DONE.
How you want to implement this script up to you, but for demonstration purposes I will attach it to the field CODE. Go to LAY-OUT mode and select the field CODE. Choose Format -> Field Format... from the menu and deselect 'Allow entry into field' because you don't want users to enter sequence numbers themselves. Click OK. Choose Format -> Button... from the menu and select the 'Perform Script' step and assign it the 'Update Sequence Number' script:
and click OK. Go to BROWSE mode and click on the empty field CODE. The number 1 should appear in the field. If it does, you did it all right. Switch to the 'Sequence Number' file and look at the CITY-record. The NEXT value should contain a 3 if you used the same values I used. Switch back to the CITY-file and enter a new record. Click the empty field CODE and it should contain a 3 after the script has run. The NEXT value in the 'Sequence Number' file now contains 5.
I hope you learned something from this compact lesson. It has all the basic features in it to show how easy FileMaker Pro is. If you keep in mind these things about relations, you can build anything you like.
