Windows Vista / Filemaker Pro Advanced 8.X


Please forgive my request, but I seem totally unable to create a simple pair
of DB's that relate to each other. I've read and read, taken apart a few
relational DB's and fooled with them, but I just don't get it. Must be
because I'm old, because I'm not stupid :-). Your help is very much
appreciated.

My objective is to have a Master Plant List DB that lists plant names, and a
bunch of factors related to that plant. In a second DB, called Location
Guide, when I locate a plant in a specific location, and type in the plant
name, the balance of information will fill in automatically.

Here's my little practice DB that I cannot get to work;

In DB1, I'm creating records with 'names' and 'colors'
Field: Name
Field: Color

In DB2, I want to type in a 'name', and the 'color' field will fill in
automatically if the name exists in DB1
Name
Color
Location

If nothing else, a simple list of the steps to take would help. I get
completely mired down in the help menu system. Worse than doing taxes -
Thanks, Bob




--
USA - Washington State, Kitsap County, City of Poulsbo "Replies welcomed
as a sign of good etiquette" Nostradamus 1560 :-)

Imageman wrote:


Hi Bob,

sounds like you need to understand a little of basic applied relational
theory. There is a good white paper on the FM web site which may help.
They used to be called FM7_key_concept.pdf and fm7_tutorial.pdf; don't
know if they have been upgraded. Primers in FM,

You have 2 files A (DB1) and B (DB2)
First configure A so that it can recognize B: in A File > Define >
Database > Relationships takes you to the relationship graph.

There will be a TOC (table occurrence; TO in FM nomenclature)
representing the Table (presumably singular) you have in A. TOC is not a
Table btw, it is a representation of a table, and you can have
multiple TOC based on the same table.

You need to add a TOC AND File Reference to B
click the bottom leftmost icon (+) to raise the specify table dialogue.
The B file will not yet be listed; you first need to add a FILE
REFERENCE to it. Use the File: pop list in the Specify Table to Add a
File Reference: locate the file B...

Then yo can select the table in file B and add add it. It will appear as
a new TOC in the relationship graph.


In order to be able to link these two tables (that exist in two distinct
files) in a meaningful way, you must create a Relationship between
fields in the 2 TOC, This is click select fieldA, and drag to target
fieldB. The joined TOC are referred to as TOG (Table Occurrence Group;
at least in FM literature) Which fields to form a relationship between
is the issue; you will need multiple such relationships in real world
databases.




In your case, you end the PlantName in A, linked to PlantName in B. This
is NOT good practice (using names) but will get you up and running at
this stage. Unique and immutable match fields are required; and names
are rarely immutable. A Plant_ID field should be added in both A and B,
and in B (the Master Plant list, this text field would be defined as
auto enter (such as PL00001), validation unique and not empty. In A,
this will be just a text field. Why will become obvious with
familiarity. I would suggest implementing the ID fields, you can
initially create the rel between PlantNames. Sounds like you will have
enough to get on top of as it is.


Once you have a rel, you can add related fields using that rel, to the
layout. The layout in A, will be defined to Show records from A
(Layout mode, Layouts: Layout Setup)

So when yo type in EXACTLY the same name in A field PlantName, meaning
exactly the same as an existing value (PlantName) in B, the related B
fields on you A based layout, will display the B data.

Now there are a bunch of other issues to address, auto complete, value
lists, pop up menus, and implementing an ID based relational
architecture, but the above should get you started. Welcome to the world
of relational database design and FileMaker.

regards

Chris

Chris . . . YES! ! ! Thank you so much for your help. The step I was missing
was the last step, in layout mode. I'll take your advice on 'ID's", I get
your point.

In my work life (now retired after 35 years, thank God) I created countless
FMP flat files for managing our 'skunk works', and to avoid bureaucratic
entanglement and cost . The flat files are still being shared and used to
this day in this huge corporation, with nearly 100,000 employees. The DB's
were heartily adopted by the employees because they were so easy to use,
change, with no IT required. It was a revolution. The fun part, is that for
years, no one knew who was creating the DB's, and they just sort of appeared
mysteriously on the network.

My first use of FM was in the era where it fit on a floppy or two, and was
handed out free in by our Apple rep . . my how things have changed.

Thanks again for facilitating my breakthrough . . your write up was very
clear . . . Best Regards, Bob




"Chris Brown" <cbrown (AT) medicine (DOT) adelaide.edu.au> wrote

so that should have been (maybe) ,
Reply =
Let([
FM = FM_Y|N;
RY = "Welcome to the world of relational database design ";
RN = "Welcome to the world of relational database design and FileMaker."
];
Case(
FM = "Y" ; RY;
FM = "N" ; RN
))

regards

Chris