Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Multi Layered cross referencing databases

Multi Layered cross referencing databases

Multi Layered cross referencing databases


I'm trying to wrap my head around building some databases that cross reference each other but I'm trying to minimize the fields as much as possible etc.  Since it's hard to explain what I'm doing I'll try and create a simple example here.

Database #1 - Final Solution db lets call it an invoice of sorts
Database #2 - has thousands of items to be called upon by the invoice

On db1 we input options of
client name
client age
item name (used for final lookup to pull info from db2)

On db2 we have the following
item name (used for final lookup to export to db1)
item size
occupation restriction
age restriction
(and any number of other information to be pulled to db1)

Now what I want is to create a value list on db1 for "area1" and "area2" with all the names of items available to those particular areas specific to that client based on information entered.

I can pull stuff to both areas using a simple reference by creating an "area_itemsize" calculation in db2 compared to an "area1_size1" calculation in db1.  

My problem is that I also need to reduce the list due to age restriction.
So if in db1 "age = 20", I want to only pull items that match "area_itemsize" where "age" is say "15...20" or maybe just ">19"
I also need to match it to occupation and occupation restriction
where occupation might = fire and occupation restriction is a field that uses a value list and check boxes and can say "fire, water, dig and/or all" and I want "fire" to match to "fire" AND "all".

So I've been buiding multiple databases and trying things and I can't seem to wrap my head around how to get it to pull the information based on so many criteria that could be different for each "client"

RE: Multi Layered cross referencing databases

We are talking about which version of FileMaker ?

"Several databses" is pointing to the pre-7 version....

RE: Multi Layered cross referencing databases

Yeah... I wish I had 7 or better :(
I believe we're working with 5.5 here.

I may eventually import it into 7 or better but it has to work with 5.5 first.

RE: Multi Layered cross referencing databases

Basically you could work with multi-value keys and base your relationships on those keys.
Next step is to make the valuelist based upon the relationship.

Harder is it for the date restriction.

I would use the same system for the dates, and make dynamic date range, where I calculate the min and max date in the application, to avoid a range from 1000+.

Take a look at http://www.onegasoft.com/tools/smartranges/ how to build ranges.

There is no possibilty in FM 5.5 to use operators in relationships, so, you have to make calc fields to reach the same outcome.

I don't think that minimize fields is an option here.
It's more to find the place, the file, where to put your calc fields to have the most benefit.
Not knowing your structure this is hard to predict.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close