10 Replies Latest reply on Jun 28, 2016 8:55 AM by JamesTorr

    Add a "date last line item price change" field to Products

    JamesTorr

      So, I've got the usual products, invoices, line item database. I'd like to create a calculated field in the products to prompt me to know when to change the pricing.

       

      I currently don't follow the "price" field in the product so much and would like to use the line items for each product as an indicator of when the last time the price was changed, ie if I have a line item dated 24/6/16 at £1.20 and all of the line items going back until one for £1, dated 01/1/16 are priced at £1.20, I'd like the field to say 1/1/16.

       

      Make sense? I'm guessing I'm going to have to make some sort of calculation which looks for the price of the most recent line item (can do), then looks for the last one that is different and spits out the date field, but I'm not exactly sure how that should look! Any ideas?

        • 1. Re: Add a "date last line item price change" field to Products
          mikebeargie

          It makes sense, but it's only fuzzy logic. EG if a product wasn't used for a year and the price changed three months ago, your function would say that the price changed a year ago.

           

          You could add an additional table to track price changes, which would give you an absolute accurate history of all price changes, or you could do a similar thing by storing a simple log of price changes in a field in the products table itself.

           

          I'm assuming you're working retroactively at this point, so you could still populate the previous data with the fuzzy history and have the accurate one going forward.

           

          A script to populate that data may look like this:

          Go To Layout [ Products ]

          Show All Records

          Go To Record [ First ]

          Loop

             Set Variable [ $id ; Product::ID ]

             Go To Related Records [ ProductTransactions ; related to current record only ]

             Sort Records [ ProductTransaction::DateOfTransaction ; ASC ]

             Go To Record [ First ]

             Loop

                Set Variable [ $b ; ProductTransaction::ProductPrice ]

                If [ $a <> $b ]

                   Set Variable [ $date ; ProductTransaction::DateOfTransaction ]

                   Go To Layout [ ProductPriceLog ]

                   New Record

                   Set Field [ ProductPriceLog::ProductID ; $id ]

                   Set Field [ ProductPriceLog::Price ; $b ]

                  Set Field [ ProductPriceLog::DateOfChange ; $date ]

                   Go To Layout [ original layout ]

                  Set Variable [ $a ; ProductTransaction::ProductPrice ]

                End If

                Go To Record [ Next ]

                Exit Loop If [ Get(RecordNumber) = Get(FoundCount) ]

             End Loop

             Go To Layout [ Products ]

             Go To Record [ Next ]

            Exit Loop If [ Get(RecordNumber) = Get(FoundCount) ]

          End Loop

          That assumes you add a ProductPriceLog table to keep track of the changes. Since this is a single-use script as well, the performance may be bad, but it would populate the ProductPriceLog table fully based on your entire transaction history.

          • 2. Re: Add a "date last line item price change" field to Products
            JamesTorr

            Thanks for the reply Mike - I'm not looking to log all the price changes so for me that solution is a little excess of requirements. If we ignore the price field in the products for this, all I'd like to do is get the date of the last line item that has a price different to the date of the most recent line item.

             

            I admit I'm winging this a bit here but something similar to this (copied from another post here):

             

               Case (      Price_Last_Line_Item ≠ T15c_products_INVOICE_LINE_ITEMS||id_products|::UnitPrice ; T15c_products_INVOICE_LINE_ITEMS||id_products|::UnitPrice ;    

             

            GetNthRecord ( T15c_products_INVOICE_LINE_ITEMS||id_products|::UnitPrice ; Get ( RecordNumber ) )      )

             

            ---

             

            Where "Price_Last_Line_Item" is a calculated field on the products table telling me the most recent product price. I know there needs to be a date thrown in there but I'm not sure how!

             

            This above calculation just returns a random price of a line item - I'd just like to have the date of that last item with a different price to the last one.

            • 4. Re: Add a "date last line item price change" field to Products
              JamesTorr

              OK So Mike, I gave your suggestion a bash. I've run this script and it isn't populating the table with any kind of relevant data by the looks of this.

               

              Can you spot something wrong here? I'm a bit confused as to how the script sets a price for the difference between $a and $b - and shouldn't the $a variable setting be at the beginning of the loop? Does this account for there being only one product line item that is matching?

               

              Thanks in advance! James

               

               

              Screenshot 2016-06-27 16.18.51.png

              • 5. Re: Add a "date last line item price change" field to Products
                mikebeargie

                your go to related records script step is not going to a layout based on the table occurrence you are using.

                • 6. Re: Add a "date last line item price change" field to Products
                  JamesTorr

                  So there's products and product transactions on your example - mine has products and line items - assume it's the same. They're both related tables, so the go to record was based on your choice of product transactions (mine Line items), is that not correct?

                   

                  I've based the layout selection on the line items as I can't go to the products layout as it's not related.

                  • 7. Re: Add a "date last line item price change" field to Products
                    mikebeargie

                    If you use go to related records and select the related table to go to, the layout you choose should be based on the table you selected. If it's not, then you are losing context and potentially your related found set, which would explain the generation of errant data.

                     

                    Do you have filemaker advanced? you should step through with the script debugger and data viewer to see where you are getting unexpected results.

                    • 8. Re: Add a "date last line item price change" field to Products
                      JamesTorr

                      So, yes I've been using the debugger and it didn't help loads as there were no errors coming up.

                       

                      I had to make sure that the layout I was going to actually had the field.

                       

                      I've also changed the code:

                       

                      Loop

                         Set Variable [ $id ; Product::ID ]

                         Go To Related Records [ ProductTransactions ; related to current record only ]

                         Sort Records [ ProductTransaction::DateOfTransaction ; ASC ]

                         Go To Record [ First ]

                         Loop

                            Set Variable [ $b ; ProductTransaction::ProductPrice ]

                       

                      to:

                       

                      Loop

                       

                         Go To Related Records [ ProductTransactions ; related to current record only ]

                         Sort Records [ ProductTransaction::DateOfTransaction ; ASC ]

                         Go To Record [ First ]

                         Loop

                            Set Variable [ $id ; Product::ID ]

                            Set Variable [ $b ; ProductTransaction::ProductPrice ]

                       

                      Due to the fact that this was setting all the product price change entries to the same product ID. It looks like I'm getting somewhere now, I've got records matching all of the products with line items, only it's just showing the first ever line item rather than the most recent change. I'm making sure that the records are sorted in ascending order in the step above. Any ideas?

                      • 9. Re: Add a "date last line item price change" field to Products
                        JamesTorr

                        As suspected, this is a bit of a headache for me (time to brush up my scripting skills!). So I found a workaround for this:

                         

                        1. I already have a calculated field in Products showing me the price of the last line item using the "last" calculation.

                        2. I've created a new relationship between Products and Line Items matched by the ID, but with another relationship - unit price is NOT EQUAL to last unit price.

                        3. Now I just set up a calculated field in the products that shows me the date for the last item on that table.

                         

                        Phew!~

                        • 10. Re: Add a "date last line item price change" field to Products
                          JamesTorr

                          Actually I've changed that a bit - I've made it an EQUAL TO last unit price relationship and sorted the records by date so the last record that comes out is the first one after the price change.