5 Replies Latest reply on May 10, 2016 6:43 AM by keithstehlik

    Value List using another table

    keithstehlik

      I am converting an Access Database to Filemaker. In the main table for jobs the status, location and such was stored as just a number. Another table stored all the menu items and choices. The menu table has a category field and a status field and description field. The status field can hold the same number, say  5 in all the different catagories. To see the correct description I have been forming two part relationship for each combination. Right now I have 12 occurrences of the same menu table in my relationship graph. I am also having to add category calculation fields to my main table for each one. In the access database this was done thru joins whenever needed so that category is always hardcoded in.

       

      Is there an easier way to do this than making so many occurrences of the same table?

       

      Thanks

       

      Keith

        • 1. Re: Value List using another table
          macwombat

          Hi Keith.  Its a bit hard to follow your description.  Perhaps you could post some screen shots of the relationships and give some actual examples to provide a bit more clarity?  Chris

          • 2. Re: Value List using another table
            keithstehlik

            I'll try Chris.

            I have a table call Menu Choices

            It has a DDCategory Field, a DDID Field which is stored in the other table, and a DDDescription field that explains what it is.

             

            Than I have the main job tracking table

            That stores a number for the status of different processes, programmed, programQC, pulled etc.

            In my layouts I want to display the description from the menu choice table not the status number.

            The DDID number can be in the table multiple time, once for each DDCategory. To get the correct description to display I need both to form a relationship.

            The original job tracker table does not contain category fields. I have been adding those and set them to a static value with a calculation. Than create a separate relationship for each category.

             

            This works, I just have many occurrences of the same table in my relationship graph. Is there an easier way to do this?

            I hope this is more clear

            • 3. Re: Value List using another table
              macwombat

              Hi Keith.  That makes more sense now - thank you.

               

              Just to clarify:

              From your screnshots, if the value in Programmed is 3 and the Category is 2 then the description is "Printer".  Is that right?

               

              In your job tracking table screenshot you've got a field called Category15 and a field called Category37.  For a particular record - when looking up the description for the value in Programmed - what category would you choose?  Does each job tracking record only relate to 1 specific category?

               

              If you only have 1 category for each record then the relationships become easier.  See attached sample file.

               

              We're getting closer. 

              • 4. Re: Value List using another table
                user19752

                This may be a sort of challenge to "make all value list into one table".

                • 5. Re: Value List using another table
                  keithstehlik

                  Chris

                   

                  You are reading that correctly. If the Programmed was 3 and category 2 the description would be Printer.

                  To look up the description for Programmed you would use category 15.

                  Different fields need different categories to find the correct description which is my problem. Category 37 is what is needed to find the description for reship (which isn't showing in the screenshot).

                   

                  Making all the different table occurrences is working I just thought there might be a more elegant way of doing it