5 Replies Latest reply on Jun 27, 2010 5:01 AM by bluj

    Lookup vs. Calculated Formula



      Lookup vs. Calculated Formula


      I have trying a workaround solution for two linked (not relational) databases;  using FMP 6 on a MAC OS X 10.4.11 (maxed upgrade potential due to Power PC chipset)

      I have a rental equipment pricing program that looks up a rental price by matching item name to a separate FMP database with pricing information; up until now we only had one price for each item; but now we need two distinct prices for each item; I was hoping to use an added field in the master database to help steer the Lookup function to one price or the other; but am confused about whether it's a more complicated lookup or do I need to write a formula for a calculated result using something like a Choose function or If function; tried various options with no success.

      to summarize:   "Rental File" currently looks up rental item price in separate "Rental Equip." Database; based on simple one-to-one relationship; is there proper way to give it two prices to choose from and a modifying field in the "Rental File" to steer the lookup one way or the other?

      Thanks in advance for anyone kind enough to answer


        • 1. Re: Lookup vs. Calculated Formula

          The most flexible approach is to have different records from which to look up values in your database. That way if you later find you have three or more such prices, you just add more records and don't have to change your database design.


          Here's how I'm reading your post:

          If you select "Acme Tools" in field 1 and "Two handed widget" from field 2, you want the price for a two handed widget from Acme Tools to appear. If you select "Pinnacle Tools", you'd want to see the price for a two handed widget from pinnacle tools instead.


          With later versions of filemaker you'd use multiple both fields in your relationship, but I suspect that Filemaker 6 doesn't give you that option. Instead, you have to set up calculated keys.

          Define a field, cItemKey as Supplier & ItemName in your invoice file. In your lookup file you can either define a matching calculation field or use a simple text field that stores the combined supplier and item names.


          Define your relationship to match records using these two fields and you should get the results you need.


          PS. Your database as described IS a relational database.

          • 2. Re: Lookup vs. Calculated Formula


            Terry M wrote:

            using FMP 6 on a MAC OS X 10.4.11 (maxed upgrade potential due to Power PC chipset)




            • 3. Re: Lookup vs. Calculated Formula

              Thanks for the quick reply;  I was tempted to go that route, but the allure of the 'elegant' solution distracted me for a bit....  I think for now I will add more records to make it an easier match...

              the example for my database was more like this  "rental item name", matching the name in a separate list that has a half-week price and a full week price (eg.  $15 or $30); and a new added field in the master database that has a short pop-up value list with 1/2 week or week; and the confusion was trying to add that as a filter to the lookup that existed matching item anme with item name and only having the week price to pull up in the past...


              • 4. Re: Lookup vs. Calculated Formula

                from what that says I should be able to upgrade but have run into a brick wall in the past; my IMac is a little quirky and it will be a large leap forward when it finally dies...

                Thanks for the reply though


                • 5. Re: Lookup vs. Calculated Formula

                  I've never used 'em much but since it's a concrete delineation between each use (week, 1/2 week ... day, 1/2 day) would anyone out there use a repeating field? Maybe not for Terry here because of all the back work to change layouts and scripts but for a new design.


                  Terry, did you end up with 2 products for each item ie widget full week, widget half-week?


                  Also, I first learned fmp at version <3 and have bad ways of doing things ingrained so please forgive me if I sound impertinent but I'm surprised that a choose or if didn't work. My first thought would've been to use a radio button in 'rentalfile' for full/half then a lookup/grab/setfield to the 'rental equip' to pick either field.


                  Can also explain what you mean by 'linked not relational'.

                  Good luck,