1 Reply Latest reply on Jan 25, 2016 8:34 AM by tkemmere

    How to lookup in a large reference-table

    tkemmere

      Dear all,

       

      I have a database the keeps track of rental contracts and should be able to calculate indexation of the rent at some point in the year. There is a table with the CONTRACT-INFO (Including a couple of parameters that affect the indexation-ratio), and I made a related table that will list various YEARS and their correpondig rent-amounts.

       

      Now the indexation options are varied, to say the least. With four different parameters that affect the indexation-ratio and a large numbder of years it comes to about 600 different of indexation-figures. So I made a separate table with INDEXATION-FIGURES.

       

      I would like to look up the right pair of figures, so I can devide one by the other to get the rent-increase-factor.

       

      So far the table with the INDEXATION-FIGURES is unrelated, but I did a web-search on this and conclude that is not the way to go. I thought, there is no relationship... ...or actually, the truth is, there is a relationship from every YEAR-record to every INDEXATION-FIGURES-record. For any YEAR, any INDEXATION should be "lookuppable".

       

      I also found out that many-to-many relationships need to have an INTERMEDIATE table, with linking records.

       

      My questions:

      • Is this the right way to go?
      • And am I right that for every new YEAR-record I start, I will have to run a script that populates 600 related records in the INTERMEDIATE table with their F-ID's pointing at all these INDEXATION-FIGURES?

       

      Regarding the last one, I don't think so.

       

      Is any one available to get me going? Thanks in advance.

      Regards, Thomas.

        • 1. Re: How to lookup in a large reference-table
          tkemmere

          I think I'm getting closer to the answer. I was wtaching this: FileMaker Tutorial - 089 - Lookups pt 1 - YouTube and realised that I too, have corresponding fields in the YEARS-table as well as the INDEXATION-FIGURES table. The parameters.

           

          But they are 4 parameters.

          • - List type, (housekeepings or employes)
          • - Base year, (1995, 2000 or 2006)
          • - Year, (1995...2015)
          • - Month/Year average (Jan...Dec, Year average)

          These 4 fields appear in both tables.

          I'm thinking of dragging 4 relations between the 4 corresponding fields.

           

          And then I should be able to script a lookup like:

          Look up index-figure

          WHERE List type = houskeepings

          AND Base year = 2000

          AND Year = 2013

          AND Month/Year average = Jun

           

          ...Something like that.

           

          It is time to go home now, but I'll pick it up tomorrow.

          Cheers, T