Using table relationships to show records in a database with matching fields

Posted on 2007-08-05
Last Modified: 2010-04-27
For each product in my jewelry database, I would like to see (in a portal) what other products in the database share matching values.  The four fields I  would use to match are:

A) center stone type B) center stone cut C) style D) Material

The portal should show a list of all matching products based on the fields above, with a number representing  how many of the four fields each product matches on.

I have created a relationship to match each of the 4 fields above.  How do I create a portal that will show every product that matches the current record being viewed, and how many fields it matches on (from the four table relationships already created)

You can view the database here:
Question by:jarrodprice

    Author Comment

    No takers?
    LVL 6

    Accepted Solution

    The structure of your database does not lend itself easily to this task. I have posted a possible solution using a technique that I just learned at DevCon so it may not be too elegant. You can find it at:

    This example does not limit the number of details that are matched. It will match and count all the details for that item. The concept is that you create a multi-Key for an item in a global that is made up of the details in the related records. This is where you would filter out the detail you do not want. Once that Key is made you can obtain a related set and the resulting list is sorted. A looping script is used to count how many times a particular Item shows up in the list. That will be the number of matches. This info is then placed in a global repeating field and is displayed in a tabular format.

    I tried repeatedly to do this with just relationships and my brains just sort of short circuited every tiime.

    Author Comment

    I appreciate the effort!  I have had no luck doing it with relationships either.  I will take a long look at your sample, to figure out if it will work for me.
    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
    Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
    This video discusses moving either the default database or any database to a new volume.
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now