What are some methods of getting the "match field" or "primary key"?

   We are all using FMP 5.5 under Windows XP or Windows 2000 and
FileMaker Server 5.5.  I need to make a new database listing where all
our roof ladders are located (I work for a county government) in each
building.  I already have a Building file that lists all of our
buildings along with their names, address, city, zip, etc etc.  But I
would like to make it a relational file also so that no one needs to
update the Buildings file or the Ladders file if the building names or
address should change.  [They probably won't but the idea is also to
have the building names and addresses be exactly the same.]
   Luckily the Building file already has a "match field" or "primary
key" number system set up in it.  But as I thought about this new
Ladder file, how exactly would someone other than me place the right
number in the right spot?  As someone kinda familiar with FMP I could
go to the Building file, look up the number and then go to the Ladders
file and plug the number in the right spot and all is done.  I snagged
the Building file from the server and renamed it and experimented with
both files to give it a go, and it worked.
   Only now I had a problem.  How could I get other people to easily do
this?  Some of them are not the sharpest pencils in the drawer so I was
trying to come up with a way that from the Ladder file they could get
the number from the Building file and thus have the building's
information automatically inserted.  But my FMP skills aren't that
sharp or deep for this one.  Do I use a portal?  Or maybe a script?  Or
would a calculation be better?  I would greatly appreciate any pointing
of the way (a book, an Advisor article, etc) that would give me an idea
of where to go.

   Just delete the dx in the address if you need to do so.
0
2/6/2008 3:34:08 AM
comp.databases.filemaker 11218 articles. 0 followers. amosw01 (46) is leader. Post Follow

3 Replies
110 Views

Similar Articles

[PageSpeed] 1
In article <050220082034088388%somewhere@sea.ocean>, Popeye Hornblower
<somewhere@sea.ocean> wrote:

>    We are all using FMP 5.5 under Windows XP or Windows 2000 and
> FileMaker Server 5.5.  I need to make a new database listing where all
> our roof ladders are located (I work for a county government) in each
> building.  I already have a Building file that lists all of our
> buildings along with their names, address, city, zip, etc etc.  But I
> would like to make it a relational file also so that no one needs to
> update the Buildings file or the Ladders file if the building names or
> address should change.  [They probably won't but the idea is also to
> have the building names and addresses be exactly the same.]
>    Luckily the Building file already has a "match field" or "primary
> key" number system set up in it.  But as I thought about this new
> Ladder file, how exactly would someone other than me place the right
> number in the right spot?  As someone kinda familiar with FMP I could
> go to the Building file, look up the number and then go to the Ladders
> file and plug the number in the right spot and all is done.  I snagged
> the Building file from the server and renamed it and experimented with
> both files to give it a go, and it worked.
>    Only now I had a problem.  How could I get other people to easily do
> this?  Some of them are not the sharpest pencils in the drawer so I was
> trying to come up with a way that from the Ladder file they could get
> the number from the Building file and thus have the building's
> information automatically inserted.  But my FMP skills aren't that
> sharp or deep for this one.  Do I use a portal?  Or maybe a script?  Or
> would a calculation be better?  I would greatly appreciate any pointing
> of the way (a book, an Advisor article, etc) that would give me an idea
> of where to go.
> 
>    Just delete the dx in the address if you need to do so.


Why bother with a Ladder file at all?? (Maybe you're just using
"ladder" and "building" as examples.)

I would probably just add a Text field called "Ladder" to the Building
file into which you can type the ladder's ID / inventory code (or
however you are identifying individual ladders). You can stretch the
Text field's height so that multiple ladder ID codes can be entered if
necessary.

It's unlikely that a ladder has much information which needs to be
stored and updated about them, so a spearate database file seems like
overkill. Unless there's literally many hundreds of ladders, it would
be easier to have a simple Excel spreadsheet or Word document that
lists the ladder's ID code and the few relevant details (eg. height,
not sure what else there would be).


If you really want a separate database file, then you need to create a
new field in the Ladder file (BuildingID) which can be used for a
Relationship in the Ladder file to the Building file using the existing
"match field".
eg.
     rel_WhichBuilding     match records when 
                BuildingID = BuildingFile::MatchField

There's no need for a Portal since presumably a Ladder can only be at
one Building at a time ... unless you're listing ladders by type (eg.
there are five 3ft ladders, but only one Ladders file record for them
all).

On the Layout you can of course put other related fields to display any
further details you want from the Buildings file.
eg.

    [BuildingID] [re_WhichBuilding::BuildingName]
                 [re_WhichBuilding::BuildingAddress]



To get around the user data entry problem, you can format the
BuildingID field in the Ladders file as a pop-up menu or pop-up list -
as long as there are not hundreds of buildings.

Create a Value List which will display all the possibilities from the
Building file in the pop-up menu.
eg.
      vl_AllBuildings      use values from other field
                           file: Buildings    field: MatchField
                           also display values from BuildingName
                           sort by BuildingName

Change the BuildingID field on your data entry Layout to be a formatted
as a Pop-up Menu or List using this Relationship. Now when users click
on the field they will be shown a list of possible buildings, but the
field itself will only store the Building ID code for the
rel_WhichBuilding Relationship. For example, the pop-up menu / list
might display:

    13A  Main Street Offices
    13B  Main Street Storage
    15   Maintenance Depot
    27   Parks Department

when the user chooses the third option, then BuildingID field will
store the ID "15" and the rel_WhichBuilding Relationship will cause the
name "Maintenance Depot" and coressponding address to automatically be
displayed in the other fields.



Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)
0
helpful_harry (1512)
2/7/2008 5:21:47 AM
In article <070220081821470007%helpful_harry@nom.de.plume.com>, Helpful
Harry <helpful_harry@nom.de.plume.com> wrote:
 
> Why bother with a Ladder file at all?? (Maybe you're just using
> "ladder" and "building" as examples.)

   No sir, we are truly using such file names.  The Building is done
and been in use for years, the Ladder will be new.



> I would probably just add a Text field called "Ladder" to the Building
> file into which you can type the ladder's ID / inventory code (or
> however you are identifying individual ladders). You can stretch the
> Text field's height so that multiple ladder ID codes can be entered if
> necessary.

   Yes, we could.  The idea though (for me) is working on good db
developer habits.  I'm trying to keep repetive information as a
relationship set up instead of FMP 5.5's one table for everything
model.  Since the db for building names, address, building number, etc.
already exist I don't really want to retype or import the information. 
That way if something changes like a building name I or someone else
will not have to update several files like we do currently.  I like
like to start down the path of making relational data really
relational.



> It's unlikely that a ladder has much information which needs to be
> stored and updated about them, so a spearate database file seems like
> overkill. Unless there's literally many hundreds of ladders, it would
> be easier to have a simple Excel spreadsheet or Word document that
> lists the ladder's ID code and the few relevant details (eg. height,
> not sure what else there would be).

   We have over 300 hundred buildings, most with a ladder.  Some
buildings like the jail have a good dozen ladders.  These are fixed
ladders that are bolted to the building structure and used to access
the roof or to go from one roof level to another.  OSHA requires that
someone go around once a year and do some maintenance or testing (say
the people above me) on every permanently attached ladder.  So the
Ladder db is to keep track of where they are in a building, what
building it is in, when last checked and by who, and how many.  Other
factors (I'm guessing) are height, inside or outside, external cage,
what it is made of, how it is attached, age, etc).

   

> If you really want a separate database file, then you need to create a
> new field in the Ladder file (BuildingID) which can be used for a
> Relationship in the Ladder file to the Building file using the existing
> "match field".
> eg.
>      rel_WhichBuilding     match records when 
>                 BuildingID = BuildingFile::MatchField

   I was able to do that with no problem.  This is what prompted my
original question.



> There's no need for a Portal since presumably a Ladder can only be at
> one Building at a time ... unless you're listing ladders by type (eg.
> there are five 3ft ladders, but only one Ladders file record for them
> all).

   I'm sorry that I was not more clear earlier (my first post).  These
are all ladders that don't move.  Some buildings have none (and most
likely we won't even bother to list them in the Ladders db), most have
one, and several have two or more.



> On the Layout you can of course put other related fields to display any
> further details you want from the Buildings file.
> eg.
> 
>     [BuildingID] [re_WhichBuilding::BuildingName]
>                  [re_WhichBuilding::BuildingAddress]

   I was able to do that also in my experiment.  It worked just fine. 
Now I need to work on passwords and groups as to who can do what to
these files.



> To get around the user data entry problem, you can format the
> BuildingID field in the Ladders file as a pop-up menu or pop-up list -
> as long as there are not hundreds of buildings.

   I'm sorry to say that there is over 300 buildings.  I see I messed
up again on the original post.  I'm sorry.



> Create a Value List which will display all the possibilities from the
> Building file in the pop-up menu.
> eg.
>       vl_AllBuildings      use values from other field
>                            file: Buildings    field: MatchField
>                            also display values from BuildingName
>                            sort by BuildingName
> 
> Change the BuildingID field on your data entry Layout to be a formatted
> as a Pop-up Menu or List using this Relationship. Now when users click
> on the field they will be shown a list of possible buildings, but the
> field itself will only store the Building ID code for the
> rel_WhichBuilding Relationship. For example, the pop-up menu / list
> might display:
> 
>     13A  Main Street Offices
>     13B  Main Street Storage
>     15   Maintenance Depot
>     27   Parks Department
> 
> when the user chooses the third option, then BuildingID field will
> store the ID "15" and the rel_WhichBuilding Relationship will cause the
> name "Maintenance Depot" and coressponding address to automatically be
> displayed in the other fields.

   That might work.  We currently have a Service Request database that
does something like that with the building names from the Buildinf
file.  Someone created it and then retired so I had no idea how that
was done (as I could not find anything under Define Relationships
feature of FMP).  I find it a little clunky because the "link" is based
on the name of the building and if it is changed all the Service
Request records that had that building name now come up with no
building.
   Thank you sir.  I will give that one a go and I'll let you know how
it fairs.
0
2/8/2008 1:08:47 PM
In article <080220080608478011%somewhere@sea.ocean>, Popeye Hornblower
<somewhere@sea.ocean> wrote:

> In article <070220081821470007%helpful_harry@nom.de.plume.com>, Helpful
> Harry <helpful_harry@nom.de.plume.com> wrote:
>  
> > There's no need for a Portal since presumably a Ladder can only be at
> > one Building at a time ... unless you're listing ladders by type (eg.
> > there are five 3ft ladders, but only one Ladders file record for them
> > all).
> 
>    I'm sorry that I was not more clear earlier (my first post).  These
> are all ladders that don't move.  Some buildings have none (and most
> likely we won't even bother to list them in the Ladders db), most have
> one, and several have two or more.

Since the ladders don't move, it would make more sense to just have a
Text field in the Buildings file itself (algon perhaps with other
fields, eg. dat of last check). I think using an extra Table / file is
going to be like using a sledge hammer to crack a peanut.

Helpful Harry                   
Hopefully helping harassed humans happily handle handiwork hardships  ;o)
0
helpful_harry (1512)
2/8/2008 10:01:55 PM
Reply:
Similar artilces about - What are some methods of getting the "match field" or "primary key"?:

What are some methods of getting the "match field" or "primary key"?
We are all using FMP 5.5 under Windows XP or Windows 2000 and FileMaker Server 5.5. I need to make a new database listing where all our roof ladders are located (I work for a county government) in each building. I already have a Building file that lists all of our buildings along with their names, address, city, zip, etc etc. But I would like to make it a relational file also so that no one needs to update the Buildings file or the Ladders file if the building names or address should change. [They probably won't but the idea is also to have the building names and addresses be exactl...