4 Replies Latest reply on Jan 29, 2009 1:21 PM by PatrickHolzer

    Field should display a value according to a script

    PatrickHolzer

      Title

      Field should display a value according to a script

      Post

      People order products in sets of 1, 2 or 3 pieces. For each set I have another price (the more they buy, the less they pay).

       

      I entered the specific prices in my "Product"-table layout into the dedicated fields (they are called "set_1", "set_2", "set_3").

       

      I created a table called "Orders". In the "Orders"-layout, I can see whether people purchased a set of 1, 2 or 3 pieces.

       

      But here's the trouble:

       

      I have a field called "Order Amount" in my "Orders"-table layout. How can I tell that field to display the product price (= order amount) that corresponds with the quantity, automatically?

       

      So that, let's say, if I enter "3" into the "Quantity"-field in the "Orders"-layout, FileMaker will automatically and instantly go to the corresponding product, find the price of a set of 3 pieces for that product and display it right there in the "Order Amount"-field?

        • 1. Re: Field should display a value according to a script
          pvhaute
            

          Hi,

           

          I suppose that your 'order amount'-field is of the type 'number'.

          You could set the 'auto-enter' option of that field to 'calculated value'.

          The calculation could then be to retrieve (=function 'lookup') the appropriate 'set-price' from the related 'product'-table based on a selection made with a simple 'if'-function.

          Ticking the box 'do not replace existing values ...' would have the additional benefit that prices on past orders will not change if you change your price in the product file.

           

          Let me know if this doesn't make sense.

           

          rgds,

          P

          • 2. Re: Field should display a value according to a script
            ninja
              

            Howdy patrick,

            Thanks for the post...an interesting one...

             

            If real life is as simple as you describe it (no sets of 4,5,7,,,300,,,45000, etc.) and there are few products, you could do something as direct as having a "PricePer" field be a calculation field with nested If statements.  This is not easy to maintain, so I would only consider it if there are very few permutations.

             

            Another way is to double-relate two tables.  If your "Orders" table were joined with your "PricePer" table by both Orders:: ProductName AND Orders::Qty, there would/should/could be a record in the PricePer table that gave a Price per unit.

             

            If you want widgetA, Qty=1 it would join to a record, but if you want WidgetB with a Qty=3, it would join to a different record because the relationship is dependant on both fields.

             

            Does that make sense to you?  Your Orders::Product Name would of course be a value list based upon Products::Name field to avoid typos and possibly risk breaking the links to PricePer...

            • 3. Re: Field should display a value according to a script
              pvhaute
                

              Absolutely wright Ninja,

              i understood from the posting that only 3 different prices were possible and didn't want to complicate things ;o)

               

              rgds,

              P

              • 4. Re: Field should display a value according to a script
                PatrickHolzer
                  

                Hi Ninja, Hi St Peter,

                 

                Thanks for your input!

                 

                I solved the problem with "auto-enter Calculation"; my calculation looks like this:

                 

                Case ( Order_Quantity="1"; Products::Retail_Price_1_Set;Order_Quantity="2";Products::Retail_Price_2_Set;Order_Quantity="3";Products::Retail_Price_3_Set)

                 

                Regards,

                Patrick