7 Replies Latest reply on Oct 12, 2009 2:03 PM by philmodjunk

    Calculation between 2 records in same table.

    John@buyliquorbetter

      Title

      Calculation between 2 records in same table.

      Post

      FMP 10, Mac OS 10.6, newer user with a basic understanding of databases.

       

      I have 2 tables, one (pricing) has 2 years of monthly pricing for a product. The other (product) has the product and its attributes. What I would like to do is to track change over time to the pricing of the product. 

       

      I have a portal in my product table that shows all the price records that match the product item code. That works great. What I need is to calculate the price change this month from the previous month. And, another field that calculates the difference this month from 2 months ago. So...

       

      If a product is $10 this month and was $9 last month, I want it to calculate the difference of $1. And, if the price was $8 2 months ago, I want it to calculate the difference this month of $2. But I can't figure out how to tell FMP which records it needs to subtract.

       

      Thank you for your time.

       

      John 

        • 1. Re: Calculation between 2 records in same table.
          philmodjunk
            

          Place a date field in your pricing table that returns the correct month of the price change, but with a day that's always the first of the month:

          Let's assume you have field, ChangeDate that is the precise day that the price was changed.

           

          Date (Month (changeDate), 1 , Year(changeDate)) will then give us the above date that we need, let's name it cMonth.

           

          Two more calculation fields:

           

          cLastMonth, Date (Month (changeDate) - 1, 1, Year (changeDate))

          cLast2Month, Date (Month (changeDate) - 2, 1, Year (changeDate))

           

          Now make two relationships to two new Table Occurrences of your pricing table.

          Select the Pricing Table and click the button with two overlapping green plus signs.

          Name the new TO box, LastMonth.

          Relate it to your pricing table:

          Pricing::cLastMonth = LastMonth::cMonth AND

          Pricing:: productID = LastMonth:: productID

           

          Do the same to create a 2nd new table occurrence, Last2Month

          Pricing::cLast2Month = Last2Month::changeDate AND

          Pricing:: productID = LastMonth:: productID

           

          Now you can refer to these related values to get last month's price and 2 months ago price into your calculations.

          • 2. Re: Calculation between 2 records in same table.
            John@buyliquorbetter
              

            Everything worked perfectly except one. When I use the field for the pricing for this month, it doesn't return this month it returns the first month. For some reason, FMP thinks it is the last record. I have tried sorting the data but it doesn't make a difference. Why is the price returning the first month's price and not the last?

             

            John 

            • 3. Re: Calculation between 2 records in same table.
              philmodjunk
                

              I was assuming you have one price change per month. I now interpret your last post to mean that you might have several changes in one month and you want the most recent change dated from the previous month?

               

              If so,

               

              Specify a descending sort order on the ChangeDate field in the relationship definition itself. (double-click the line in the relationship graph and click the sort button on the last month side.)

               

              If not, I don't understand your description of the problem? Perhaps you could post an example?

              • 4. Re: Calculation between 2 records in same table.
                John@buyliquorbetter
                  

                Nope, the price changes only once a month. The problem was that the original price, the most current price was coming up as the oldest price. I solved it by creating another table like the others that had this month's pricing in it. I just don't understand why the current price shows up as the least current price. Oh well. It works.

                 

                Fixed. Thanks for you help. I really appreciate the attention to detail and the timeliness. 

                 

                John 

                • 5. Re: Calculation between 2 records in same table.
                  philmodjunk
                    

                  "I solved it by creating another table like the others that had this month's pricing in it."

                  Another table (Manage | Database | Tables) or another table occurrence (Manage | Database | Relationships )?

                   

                  You shouldn't need another table to do this. A table occurrence? Maybe.

                   

                  "I just don't understand why the current price shows up as the least current price."

                  I don't either but don't fully follow what you are describing here.

                  • 6. Re: Calculation between 2 records in same table.
                    John@buyliquorbetter
                      

                    I created another table occurrence.

                     

                    I have 2 tables, one is "pricing" for the products with a record month's pricing for every product, the other is the "product" details. The are related by an item code. When I put the field "pricing::price" from the pricing table into the product details table, it show the first record's price not the last. So it is showing the most out of date price instead of the current price.

                     

                     

                    • 7. Re: Calculation between 2 records in same table.
                      philmodjunk
                        

                      Ok, I get it. Your extra Table Occurrence (TO) may be necessary.

                       

                      You can get the "most recent" related record by specifying a sort order in the relationship like I described earlier. If you don't want that in other instances where you use your original TO, a separate TO with this specified sort order will do the trick.

                       

                      There are two ways to specify a sort order for related records:

                       

                      1. In the relationship, the related record that sorts out first will be the top row in a portal or the only visible row in your layout if you just drop the field on it. This specified order will hold for all references to records via that TO unless superceded by...
                      2. In a portal. You can specify a different sort order that is true just for the specific Portal that you are creating.