3 Replies Latest reply on Jan 8, 2009 10:52 AM by TSGal

    multi-criteria calculation problem - filemaker 5

    agreer

      Title

      multi-criteria calculation problem - filemaker 5 & 5.5

      Post

      I have been struggling for several weeks (off & on) with this problem:

       

      I have 4 tables: Press Monkey, Customers, Products, and Special Plates. From Press Monkey you select which customer wants which product, and then 3 fields automatically fill out the size, which press, and whether or not the order requires a special plate (or it's supposed to).

       

      My trouble is automatically filling in the 3 fields. When the orderer fills in the customer and the product number, I need FileMaker to search the Special Plates database for a record that matches both criteria (customer and product #), and if it finds it, fills in the appropriate rest of the information. If it doesn't find it, then it should use the information from the Products database.

       

      Here's what I have, which only *sometimes* works - the size, press, and special plate fields (all type text) are calculations:

       

      Size:

      If(Exact(Trim(Product Number),Trim(Special Plates::Product Number)) and Exact(Trim(Customer),Trim(Special Plates::Customer)), Special Plates::Size, Products::Size)

       

      Press:

      If(Exact(Trim(Product Number),Trim(Special Plates::Product Number)) and Exact(Trim(Customer),Trim(Special Plates::Customer)), Special Plates::Press, Products::Press)

       

      Special Plate:

      If(Exact(Trim(Product Number),Trim(Special Plates::Product Number)) and Exact(Trim(Customer),Trim(Special Plates::Customer)), "Yes", "No")

       

      All the fields are of text type and all the calculations are text results, and no two customers have the same name. Customers can have more than one special plate and one product can have more than one special plate; each record is unique, but no specific field is unique.

       

       If anyone has a solution or partial solution, I'm all ears!

       

      April

        • 1. Re: multi-criteria calculation problem - filemaker 5 & 5.5
          Jens Teich
             You want to find a combination of two fields (customer and produkt) in a remote table.

          [Hint] If you have a chance to upgrade to FM 9 this problem would be much easier because the new versions support realtionships with multiple conditions.

          But it is also possible in your version. You need a calculation field combining both fields
          product &"-"& customer
          in both tables. Then you connect the new fields with a relationship. It will show only related records which exactly match in both conditions.

          Jens


          • 2. Re: multi-criteria calculation problem - filemaker 5 & 5.5
            agreer
              

            Jens -

             

            Danke! This works great! I appreciate your help and your quick response. May I ask one more question? How do repeating fields work in this situation? (i.e. the Press Type can change from print to print...if it were a repeating field with all the possible Press Types would the user be able to select the appropriate one?) Does FileMaker just use the first record or is there a way to let the user select which record to use?

            • 3. Re: multi-criteria calculation problem - filemaker 5 & 5.5
              TSGal

              agreer:

               

              Thank you for your post.

               

              A repeating field is a single field that contains many values.  In a calculation, to use a field with a repeating field, use the Extend() function.  For example, if you want to use a non-repeating "Product" field with the repeating "Press Type" field, the calculation would be:

               

              Extend (Product) & Press Type

               

              In the calculation, make sure you specify the number of repetitions.  Otherwise, you will only see the first value.

               

              If you want to select a specific repeating value, it can be a bit cumbersome.  You would need to create an additional checkbox field and a repeating calculation field that when the checkbox field is checked the value from that repeating value is used, and then use another calculation field to grab the value from the repeating calculation (through use of Max function).

               

              Although Repeating fields are easy to use, they can be very limiting in the long run.  As you gain more knowledge with FileMaker Pro, you should look at converting these repeating values to separate records in a related table.

               

              Good luck, and let me know if you need clarification for any of the above steps.

               

              TSGal

              FileMaker, Inc.