8 Replies Latest reply on Feb 15, 2013 2:05 PM by user18951

    Lookup Failing

    user18951

      Probably a basic question, but:

       

      Table Quotes:

       

      pkQuoteID

      fkConstructionID

       

      Table Construction:

       

      pkConstructionID

      Description

       

      Table UnitPrice:

       

      pkUnitPriceID

      fkConstructionID

      Quantity

      PricePerThousand

       

      Table QuoteQuantityPrice:

       

      fkQuoteID

      Quantity

      UnitPrice

      Extended Price

      blah

      blah

       

      Each Quote has one construction and can have multiple prices.

      The problem is this pricing was set up on a matrix. In the UnitPrice table, there may be a quantity for 500, 1000, and 2500. Each with a seperate per thousand price.

      I need to get the unit price into the QuoteQuantityPrice table, but the amount quoted might be 1250 or 2000, and I need to lookup the price for the lower quantity.

       

      I've tried lookups, but I'm not getting anywhere.

       

      Any advice would be appreciated.

        • 1. Re: Lookup Failing
          PalmDBS

          Create a separate Table Occurence for UnitPrice called UnitPrice_lookup. 

           

          Ensure that there is a record for a quantity of 1 to catch unit prices for quantities before the first price break. (For example, if your first price break is at 500, but only 300 are ordered, you need to have a price for 1-499 -- if your mimumum order of 500, then you'll need other validation in place).

           

          Create a relationship where QuoteQuatityPrice::Quantity >= UnitPrice_lookup::Quantity, and sort UnitPrice_lookup by Quantity DECENDING.  This will find the biggest price break met by the order.

           

          Then from the context of QouteQuantityPrice, you can set the QuoteQuantityPrice::UnitPrice to UnitPrice_lookup::PricePerThousand / 1000

           

          hth,

          Mike

          • 2. Re: Lookup Failing
            user18951

            Mike -

             

            Another attempt at this this morning.  Read you advice with clearer eyes:

             

            "Create a separate Table Occurence for UnitPrice called UnitPrice_lookup."

             

            step1.png

             

            "Ensure that there is a record for a quantity of 1 to catch unit prices for quantities before the first price break. (For example, if your first price break is at 500, but only 300 are ordered, you need to have a price for 1-499 -- if your mimumum order of 500, then you'll need other validation in place)."

             

            step 2.png

            I will also add other validation later to ensure this doesn't get used.

             

            "Create a relationship where QuoteQuatityPrice::Quantity >= UnitPrice_lookup::Quantity, and sort UnitPrice_lookup by Quantity DECENDING.  This will find the biggest price break met by the order."

             

            step3.png

             

            Done.   I'm unclear if I'm sorting this right.  I opened a layout based on the lookup TO, which appears to have 55 blank records (??), and applied a sort.

            step4.png


            I also sorted the original table in it's layout.

             

            step5.png

            When I re-open it, it no longer appears to be sorted.  I'm doing something wrong here.

             

            "Then from the context of QouteQuantityPrice, you can set the QuoteQuantityPrice::UnitPrice to UnitPrice_lookup::PricePerThousand / 1000"

             

            I'm Just trying to pull the price for this quantity from the table at this point, for troubleshooting.

            step7.png

            No matter what construction or quantity Set in my Layout, I keep getting 999 for the price.

            step8.png

            Not sure which way to go now.

             

            Any guidance is appreciated.

             

            Dave


            • 3. Re: Lookup Failing
              keywords

              Why don't you try a different approach? Instead of populating the field by Lookup, try Autoenter by calculation. Your calculation can then be set to reference the appropriate price, or the prices for different quantities can be set within the calc itself---I assume your logic is some sort of % discount off the base price based on the quantity purchased.

              • 4. Re: Lookup Failing
                user18951

                Based on input, that's what I'm now attempting ( I think ). Filemaker is actually more confusing in this aspect than MySQL.

                 

                David Goodnature

                President & General Manager

                Sterling Business Forms, Inc.

                (541) 779-3173 tel - fax (541) 857-4128

                www.sbfnet.com

                • 5. Re: Lookup Failing
                  keywords

                  Here is a file that does what I suggest. Enter any number in the quantity on the order table and the price that comes through is adjusted as per the autoenter calculation.

                  • 6. Re: Lookup Failing
                    user18951

                    I appreciate that input!

                     

                    Unfortunantly, I the quantity breaks differ based on the construction.

                     

                    One item might break at 250, 500, 1000 while another may be 600,1200,1800, etc.

                     

                    They want to be able to alter the price breaks based on what they enter into the construction table with the unitprice portal.

                     

                    I have much to learn about filemaker!

                    • 7. Re: Lookup Failing
                      keywords

                      My test file was merely a starting point. The principle is the same though, but your formula will need to be more complex.

                      • 8. Re: Lookup Failing
                        user18951

                        Thank you.  I'll keep trying.