9 Replies Latest reply on Apr 29, 2010 12:30 PM by jms_1

    dynamic calculation based on dates held in a related table

    jms_1

      Title

      dynamic calculation based on dates held in a related table

      Post

      Hi,

       

      Not sure the title is all that accurate???

       

      I am new to FM, but learning FAST!!!

       

      My current problem is as follows

       

      I have a PRODUCTS table that holds info about the products I purchase, and I have a PURCHASE_MAIN table that holds the main info about a purchase (Who it was from, Date purchased, Date paid, total tax for that specific purchase)

       

      These tables are joined with the PURCHASE_DETAIL table - that holds the info on what products were purchased and for how much - it also holds an unstored calc to reduce the price paid for a product to the price / smallest unit for that line(record)

       

      What I want to do is populate the Products table with a 6month Ave, 3month Ave and Latest price per smallest unit - based on the history in the PURCHASE_DETAIL table - the reason for this is I then want to calculate a RECIPE price based on the amount of each product used in a recipe.

       

      I have created a global Get(CurrentDate) field g_prodmain_currentDate and then a prodmain_6monthdate(Current date number - 182) field both are numbers. I can get an average based on all instances of "productX" held in PURCHASE_DETAIL, but I can't seem to limit the average calc to purchase dates that are > the 6 month date (I have also created a field in PURCHASE_MAIN, that is the number version of the date.

       

      I would like to do this via a calc at table level, as I want the price info to be dynamic and not have to have something like a "update prices" script.

       

      Any pointers would be greatly appreciated.

       

      Thanks a mil

      Mark

      FMP 10

       

       

        • 1. Re: dynamic calculation based on dates held in a related table
          comment_1

          In your PRODUCTS table, define a Calculation field c6MonthsAgo =

          Let (
          today = Get (CurrentDate)
          ;
          Date ( Month ( today ) - 6 ; Day ( today ) ; Year ( today ) )
          )


          Set the calculation's result type to Date, and the field's storage to "Do not store…" (NOT global).


          Define the relationship (using another occurrence of PURCHASE_DETAIL) as:

          PRODUCTS::ProductID = PURCHASE_DETAIL 2::ProductID
          AND
          PRODUCTS::c6MonthsAgo ≤ PURCHASE_DETAIL 2::PurchaseDate


          A calculation field (in PRODUCTS) =

          Average ( PURCHASE_DETAIL 2::PurchasePrice )

          should give you the average price in the last 6 months.












          • 2. Re: dynamic calculation based on dates held in a related table
            jms_1

            Thanks for your reply - I was on the same path, just a slightly more round about way - ie reducing all dates to number format

             

            Suffice it to say

             

            The let function populates the date as of 6 months ago, and all the other bits fit together - just when I filter the relationship with the

             

            PRODUCTS:

            • 3. Re: dynamic calculation based on dates held in a related table
              jms_1

              Just created a test layout with the relevant fields - it seems the problem is one of Indexing - but as yet I am not sure how to fix this - I have done the obvious and tried to force indexing on the fields - but the calc fields that refer to related tables can't be indexed??? Any thoughts

               

              Cheers

              Mark

              FMP 10 (Mac SnowLeopard)

              South Africa

              • 4. Re: dynamic calculation based on dates held in a related table
                comment_1

                Please check that:

                1. The field PURCHASE_DETAIL::PurchaseDate is a Date field.

                2. You do have purchases of the selected product in the last 6 months.

                • 5. Re: dynamic calculation based on dates held in a related table
                  jms_1

                  This is the summarised setup of the 3 tables

                   

                  PRODUCT_MAIN

                  product id (autoenter)

                  product name

                  product other info

                  Date 6 months ago (calc results in date- as per your 1st reply)

                  6 month average - calculated reffering to the Cost per RecipeUnit in the TO PURCHASE_DETAILPricing

                   

                  <<related one to many>>

                   

                  PURCHASE_DETAIL

                  purchase detail id(autoenter)

                  fk product id

                  fk purchase id

                  pack size

                  pack unit

                  qty packs bought

                  line price

                  cost per unit - calculated from pack size, unit and price

                  purchase date - calculated from PURCHASE :ie: PURCHASE_DETAIL::purchase date = PURCHASE::Purchase date (results in Date)

                   

                  <<related many to one>>

                   

                  PURCHASE

                  purchase id(autoenter)

                  purchase date

                  company

                  VAT total

                  Invoice Nr

                   

                  I have set up a layout  with the following fields

                  PRODUCT_product name

                  PRODUCT_date 6 months ago

                  PRODUCT_6 month average

                  PURCHASE_DETAIL_cost per unit

                  PURCHASE_DETAIL_purchase date

                  PURCHASE_purchase date

                   

                  This displays as a list, all instances of all products purchased their dates of purchase and cost per unit - all correct for the small amount of test data I have

                  The 6 month average just displays an average of ALL the instances of a particular product

                  if I set up the filter as suggested above - the list "looses" records from the PRODUCT side"correctly" ie according to the date filter,AND the 6month average feild is empty accross all records, but the corresponding records from the PURCHASE_DETAIL and PURCHASE tables are sitll visible

                  (I have tried the filter from the other side of the relationship, and that "looses" records from the PURCHASE table correctly, with no influence on the PURCHASE_DETAIL records.

                   

                  Hope that gives some insight - I have probably committed a horrible FM crime to create this odd situation?????

                   

                  Thanks so much for the insight and guidance!!!! I am now going to try the only scenario left - and that is to try and filter the PURCHASE_DETAIL table through itself.........

                   

                  cheers

                  Mark

                  FMP 10 (Mac SnowLeopard)

                  South Africa

                   

                   

                  • 6. Re: dynamic calculation based on dates held in a related table
                    comment_1

                    Here's the problem: the field purchase date in PURCHASE_DETAIL is an unstored calculation, and cannot be used as the matchfield on the "other" side of a relationship.

                    It would probably be best to change this field to type Date and have it lookup the date from the parent purchase order. There are other methods, but I believe this would be the simplest (and fastest in performance).

                    There is one issue with this workaround, though: due to a Filemaker buglet, a lookup from the parent record doesn't work if the parent hasn't been committed. Make sure to commit a new order before you start creating the line items.

                    • 7. Re: dynamic calculation based on dates held in a related table
                      jms_1

                      You are the MASTER!!!! - thank you for sticking with this one

                       

                      The commit buglet is no problem as the prices are not going to be updated at short intervals, so they'll just update in the background and track changes to recipe cost over time. I have put in a script trigger to commit the PURCHASE prior to entering its line items to cover all bases.

                       

                      on the same scenario, could you provide insight as to how to return the price per smallest unit from the last purchased instance of the product? ie - there'll be 2 prices stored in the PRODUCT table, the 6 month average and the price based on the price of the product when it was last purchased - for some products that will be a day ago, and for some a month or so ago?

                       

                      Cheers and once again THANK YOU

                      Mark

                      • 8. Re: dynamic calculation based on dates held in a related table
                        comment_1

                        I am afraid I don't quite follow: I believe there is only one "last purchased instance of the product" - it can be accessed through the Last() function (provided that records are entered in chronological order, and that the relationship is not sorted otherwise).

                        • 9. Re: dynamic calculation based on dates held in a related table
                          jms_1

                          Nope, you did get it - I can just use the original relationship I use for entering purchases sorted by purchase date and use the Last() function to return the last entered purchase of the related product.

                          Brain fried with a day of trying to get the 6month ave to work.

                           

                          Once again - many thanks for your input - very much appreciated

                           

                          cheers

                          Mark