5 Replies Latest reply on Nov 19, 2015 6:47 AM by beverly

    relating two non-related tables


      I have a Table with Person-information (Persons) and I have a Table with Job-information (Jobs).

      Persons get a score on certain criteria, Jobs also. (for example: a Person who wants a Day-job gets scored on that item, if the person also wants an administrative job he also gets scored on that item.


      I now want to show (in the Person-layout) all the Jobs from the Job-table that have those two criteria (Job during the day, administrative Job).


      There are +100 Criteria that can be filled in.


      How do i start?

        • 1. Re: relating two non-related tables

          So, you want to show the JOBS that meet any of the 100+ criteria?


          What fields do you have in JOBS that have the criteria that could match?


          What does the criteria in PERSONS look like? do you have 100+ fields or do you have a related table of possible criteria?


          More details, please.


          • 2. Re: relating two non-related tables

            Indeed, i want to show JOBS that meet any of the 100+ criteria.

            I do not have a related table of possible criteria, but maybe i should (criteria are the same in the Persons- and Job-table, i have the same fields in both tables))


            Example, i have a PERSON who says he wants this job:

            * during the day

            * administrative

            * French-speaking.


            I want to return those JOBS (in a portal or a report) that have the same criteria (person who fills in the JOB should be searching for a day job, got to have administrative skills and should be able to speak French): On top of the list i want to show those functions that meet on all the 3 criteria, after that jobs that meet on 2 of the 3 criteria, after that jobs that meet on 1 criterium).

            • 3. Re: relating two non-related tables

              Then, yes a CRITERIA table is helpful (to pull value lists for both PERSONS & JOBS).


              One method is to have a related table to list those for each






              Then you could have a many-to-many match on the criteria. thus showing which JOBS might match:


              PERSON-<Persons_Criteria::criteria_name >-< Jobs_Criteria::criteria_name>-JOBS


              The above uses "join tables" and might only be necessary if you have other information that needs to go into the join tables.



              Another method, if it's simply matching the criteria_name in both, then FileMaker allows a "many-to-many" match on what's called "multi-line key". A set of checkboxes in each would work:


              PERSONS:criteria_name = JOBS:criteria_name


              "criteria_name" being a checkbox field containing the same values (as listed in the CRITERIA table, so it's consistent in both).


              This can be a problem, if for example, you need to report on the values. Then the join tables are advisable.


              Think through it a bit more and get back with questions!


              • 4. Re: relating two non-related tables

                If i use a CRITERIA-table, i should only use this to create a value-list in both the PERSON- and JOBS-table?

                Or should i also create records within the CRITERIA-table?

                • 5. Re: relating two non-related tables

                  I would create the Criteria table to use with the value-lists, regardless of the method used (join tables or not). It would allow you to add other things, like more full descriptions of the criteria, should you need that. And in a table would give you better edibility.


                  Be cautious in editing any value list used as checkboxes, because it is possible to make a "selection" in one of your tables using the list. If you change (edit or delete) a value for that list, it will still "exist" in the field.


                  A tip on checkbox fields: put the same field on the layout (for your viewing only) and don't allow entry, but also do not format as checkboxes. Then notice how the field actually gets populated. The order in which you check/uncheck, is the order of the contents of the field! This is how you can see any value set (and subsequently edited or deleted).