Jump to content

using relationships to "filter" field lookup


elbokonon

This topic is 7282 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

< traditional newbie disclaimer >

i've had a hell of a time finding a solution to my problem relating two databases:

one database (related file) is a list (~250 names) of realtor info (first name, last name, and branch office). the other db (master file) is layed out for me to type in work orders sent in by the realtors' assistants (realtor first, realtor last, assistant name, office, etc.) with requests for different photography requests .

however, some branches have realtors with the same last name. furthermore, the request forms are rarely filled out completely. usual info: assistant first name, realtor last name.

so, how do i setup my relationship(s) so that the relevant info (first name, last name, office branch, assistant) is filled out when there are two SMITH's at the same branch, or different branches? which should i chose as match fields and/or related fields?

do i need to learn scripting?

thank you.

< /traditional newbie disclaimer >

FileMaker Version: 6

Platform: Mac OS X Panther

Link to comment
Share on other sites

A central concept of relational database design is that the keys that relate two tables/files must be unique. The best way to do is to define in every table/file a numeric ID field that will have auto-entered serial numbers. In your example, the Relator Info file would have a field named RealtorID. Set the field's auto enter to serial numbers and check Prohibit modification of value. Now you have a method ensuring that realtors with the same names in this file are differentiated by the unique RealtorID.

To relate to the proper realtor in the master file, the RealtorID just created is used. But you need a "local" copy in each record so you can use it to relate to the actual data. Define a field in your master file named RealtorID (some folks use the same name, some use other naming conventions, pick a style and be consistant). Now define the relationship in the master file between the local RealtorID and the related file's RealtorID.

OK, the relationship & duplicate name part is solved. The next question is how to setup layouts in the master file to enter/view the Realtor info. Some folks choose to use a popup list/menu to select a realtor (using a value list created from the related file's values). Others prefer to open a window with a portal and let the user select the realtor from the portal. It's a matter of style, I prefer popup menus when possible because for most users they are quicker. When they're large they are hard to navigate & slow, a portal may be better.

Another central database concept is that data should not be duplicated unless absolutely necessary. In this case, there is no reason to have a realtor's name in both files. In fact, having it in both will cause problems other than duplciate names. If a realtor gets married, you now have to manually change every record with their name in both databases. Again, the solution is to make sure every table/file of data has a unique ID field to differentiate each record.

Does this make sense?

Link to comment
Share on other sites

  • Newbies

thanks for the lengthy reply. but i'm still a little confused. i did setup the REALTOR ID field in the REALTOR db (related file) and imported the info (first, last, office branch), and it automatically assigned ID numbers. then i setup a similar REALTOR ID field in the master file, but did not fill it with anything. the master file is the template into which i enter the work orders i receive. an extra piece of info that can be used is the REALTOR ASSISTANT from the work order. the ASSISTANT's name can be narrowed to a BRANCH office, but not a specific realtor (assistant shuffling can be common).

but i still don't understand how entering either of REALTOR FIRST, REALTOR LAST, or ASSISTANT names would differentiate between jane smith and joe smith working at the same branch office?

Link to comment
Share on other sites

Let's make an analogy to the real word: A file cabinet of folders, each with a realtor's info in it (related). If the folders are labeled with the realtor's name, it is possible to confuse 2 realtors both named Bob Smith.

If the folders are each given a unique number (e.g. from one of those rubber stamps that increments the number each time you stamp), the Bob Smiths will never get confused. How this works is that in any other file cabinet, wherever a form requires the realtor, the number on the folder is put in instead of the realtor's name. So, when you look at the info of a transaction in the master file cabinet, you'll see the number, then go to the other file cabinet and grab the correct realtor's folder of info.

I've attached a simplified version of what you're doing in three files: WorkOrders, Realtors, BranchOffices. I did not add assistants (like teachers say, "I leave that as an exercise for the reader." wink.gif ). Note how the two Bob Smiths are easily differentiated.

Look at how the relationships and IDs are setup between the three files, and how value lists are used. Look at how Realtors uses BranchOffice.ID to keep the unique ID of a branch office with each realtor's record. Look at how WorkOrders uses Realtor.ID to keep a unique ID of a realtor with each work order's record. Note how the work order can also access the branch office info (I did this the simple way, there are more sophisticated methods). Look at how the realtor is entered into a work order (there is a normal field with entry turned off and background set which overlaps the popup menu field).

Another quirk: the popup menu for realtor in WorkOrders doesn't display the realtor's branch office. This is solvable, learn a bunch about relationships, calculations, indexing, and value lists before tackling this!

You've got questions? We've got answers...

FileMaker Version: 7

Platform: Mac OS X Panther

Realtors.zip

Link to comment
Share on other sites

Thanks.

Once you grasp the concepts it will be a much less daunting task. Hang in there, look forward to when it all sinks in and you exclaim "Eureka!"

Don't feel shy about asking lots of questions, FM Forums is friendly and we all mentor each other. One good compliment keeps me answering for a few days!

Link to comment
Share on other sites

This topic is 7282 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.