14 Replies Latest reply on Jun 22, 2011 3:21 PM by aammondd

    Comparing old field value with new one

    brunomesquita

      Title

      Comparing old field value with new one

      Post

      Hope I'm not posting an old questions, but I couldn't find any answers.

      Here is my problem. I built a database to keep track of my product prices - it actually calculates the price based on some criterias. Right now I need to store the value of the field PRICE for later comparison, when the price is changed.

      My idea is to store that field value as OLDPRICE and keep the new calculated price on the field PRICE.

      Is there a way to do that? Hope someone can help.

      Regards,

      Bruno Mesquita

        • 1. Re: Comparing old field value with new one
          philmodjunk

          If you define a separate field for Old price you could do that, but you may find it better to set up a separate price list table where each record records the product ID, the price and the effective date(s) for that price. Then you can maintain a continuous record of all past prices, but an invoicing system can use today's date to look up and apply the correct effective date to each newly purchased item.

          • 2. Re: Comparing old field value with new one
            aammondd

            I would do something similar to how I handle field level audits

            I would setup a Price History Table and a Table of Global fields and use OnObjectEnter Scripts on fields that effect price to write values to the Gobal table then OnObjectSave Scripts to write out the History Record from the Global table. If there were multiple components I might have a custom dialog (or other check) to time the writing of  the history record until all changes effecting the price were complete. (Calculations can have mutiple referenced fields and you may not want to record each individuals change on the whole)

             

             

            • 3. Re: Comparing old field value with new one
              brunomesquita

              Thanks PhilModJunk and aammondd for your prompt answers.

              I like the idea of having a second table to save all princing history, but would you be kind enough to "illustrate" how I would do it? I'm not used with scripts nor custom dialogs to trigger actions. By the way, you're absolutelly right aammondd, I have several other fields reflecting on my final price.

              Thanks again for your support.

              Regards,

              Bruno Mesquita

              • 4. Re: Comparing old field value with new one
                philmodjunk

                Is this for an invoicing system where you have tables with these relationships?

                Invoices---<LineItems>-----Products (your tables may have different names, of course.)

                If so, you can modify your design by adding PriceList a table of prices, productIDs and their effective dates:

                Invoices---<LineItems>-----Products
                                     v
                                     |
                                  PriceList

                You'll need a date field in LineItems so that a relationnship to PriceList only matches to the currently effective price. This, in many cases can be an auto-entered creation date field.

                Define your relationship between lineitems and PriceList like this:

                LineItems::ProductID = PriceList::ProductID AND
                LineItems::TxDate < PriceList::effectDate

                In the set up for this relationship sort the PriceList records by EffectDate in descending order or this relationship will not work. (The second pair of fields makes it possible to set up prices changes that become effective in the future.)

                Now a unit price field in LineItems can use a Looked Up values field option to copy the current effective price from the Price List table.

                • 5. Re: Comparing old field value with new one
                  brunomesquita

                  You guys are fast! :)

                  And I'm starting to feeling a little dizzy... so many options and choices... :)

                  Right now my database has only one table (Products) where I keep all my fields. I that table I have two date fields (creation and modification). The setup you suggested, PhilModJunk, is to create a third date field, EffectDate, and use it to lookup the price for products. Now I'm wondering, can I "program" a price change based on that same EffectDate field?

                  Back to my orginal question (sorry for getting carried away), I'll be usign scrips to do so? Would you, pretty please, elaborate a little more on that subject?

                  Thanks again!

                  Bruno Mesquita

                  • 6. Re: Comparing old field value with new one
                    philmodjunk

                    No scripts needed. Any time you need to effect a price change, you add a new record to price list, enter the Product ID and the date you first want the change to go into effect. The relationship won't look up from that new price until the date in the txDate field is greater than or equal to the effective date. (The sort order specified in the relationship keeps older price changes from interfering with this process.)

                    The reason you don't do this in the Products table is that there you have one record for each product. In PriceList, you'll eventually have many records for each product as you log price changes.

                    You can also make a new table occurrence of Products and link it to price list by Product ID, that could enable you to display product names etc. on a layout where you add price change records. Also a portal from a layout based on such a table occurrence could list the pricing history for the current product record.

                    Scripts might indeed become handy if you want to be able to change prices for multiple products all in one "batch" operation--such as boosting prices by 2% for all products of a specific category.

                    • 7. Re: Comparing old field value with new one
                      brunomesquita

                      I'll try and let you guys know the result.

                      Thanks,

                      Bruno Mesquita

                      • 8. Re: Comparing old field value with new one
                        aammondd

                        Well you can create 2 tables: (Im only going to illustrate a few fields)

                        1) Product Price History Fields:(_pkPPHID, ProductID,LastPriceDate,Price, Reason) {later refered to as PPH}

                        2) Global_Audit_Hold Fields:(GHK_ProductID, GHK_Price, GHK_ChangedField_IN_1, GHK_Changedield_Out_1,)[ChangeFields correspond to the compnents that can effect price] (later refered to as GAH)

                         

                        For most of this I urge you to search the Filemaker Help to learn how  to use  the script steps: (I tend to want people to learn as they go so I point out the structural direction you will have to understand where your own fieldnames etc apply)

                        On my Product layout I would create a script that attaches to the OnRecordLoad Script trigger

                        this Script called (script 1) would use the Set Field script step (x2)  to Set the GAH::GHK_Product ID field  to the Product::ProductID and the GHK_Price field to Product::Price

                         

                        then I would create a script (called script2) this would be attached to the first compnent fields OnObjectEnter script trigger. This script would use Set Field to set the GHK_Changed_In to the value of the that field.

                         

                        I would create a third (called script 3) script that would be attached to the OnObjectSave ScriptTrigger

                        This uses the If[] Script Step to compare the value in the GAH field with the field and see if they have truely changed if so it would set the GHK_ChangedField_Out to the calculation GAH::GHK_ChangeField_IN_1 & " to " & current field

                        Then show a custom_dialogbox  with the message write history now? with just ayes and no buttons

                        You can set a variable to the value of the button choice by using the function Get(LastMessageChoice)

                        You can use more if logic to evaluate the variable If they chose the yes button

                        We would use the Perform Script Step to execute a "Write script"

                        Else we simply exit the script.

                        This same logic can be applied for as many components as there are fields you just have to setup pairs of ChangeIN/Out fields in your global table a copy of the script with the set field values changed. We could equally bypass the dialog box and write a new record for every change (you will have to decide but it would allow you to reuse the Change in/out fields.) 

                        Our write script is simply going to go to the Layout (PPH) add a new record and set all the fields to their appropriate values.

                        for the LastPriceDate you can use Get(CurrentDate)

                        for  the reason field we can use the cocatenation of the change out fields for as many as we have (if we have suspended writting the change other wise we can just set it to the change out field)

                        we will commit record after we have set all the fields in the PPH table and return to the Product layout. and we will then set the Change in/out fields to ""

                        Hopefully that gives you enough detail that you can start working on it on your own.

                         

                         

                         

                         

                        • 9. Re: Comparing old field value with new one
                          aammondd

                          big caveat in my method these global audit Hold fields need  to be set to global storgae (I tend to use that Global word without thinking some times because everytime i use it i mean a field set to global storage)

                           

                          • 10. Re: Comparing old field value with new one
                            brunomesquita

                            aammondd, your solution seems to be the most complete one, but it requires more work.

                            I'm tended to follow PhilModJunk solution which seems less complicated. In that matter, how do I transport values from my products table to my price log table?

                            Thanks,

                            Bruno Mesquita

                            • 11. Re: Comparing old field value with new one
                              philmodjunk

                              What values would you transport?

                              To set up an initial set of price list records, you can use Import Records to import ProductID's and Prices from the Products Table to the PriceList table. Once you have that in place you can set up a drop down list or pop up menu of product ID's so that you can add new PriceList records to document a change in price by adding a new record to PriceList.

                              You can also link a separate table occurrence of Products directly to PriceList so that you can access Product Description fields and other data from Products when working with your Price List records.

                              1. Select your products table in manage | Database | Relationships by clicking it.
                              2. Click the button with two green plus signs to create the new occurrence. (This is not a new table, just a new "pointer" to the Products table.)
                              3. Double click it to change it's name if you want.
                              4. Then link it by ProductID to PriceList.
                              • 12. Re: Comparing old field value with new one
                                brunomesquita

                                Hummm... I was looking for a more "automated" approuch... kind like this:

                                1. Create a new record [id] on TableProducts --> get same record [id] on TablePriceLog;

                                2. Change a price on TableProducts --> copy that price change to TablePriceLog;

                                3. In a report, print the last (let's say) three price changes...

                                Could that be done in a simple manner?

                                Thanks,

                                Bruno Mesquita

                                • 13. Re: Comparing old field value with new one
                                  philmodjunk

                                  There are a number of possible approaches.

                                  I'd link a different table occurrence of price list to Products. Then a portal to PriceList on the Products layout will list all price changes in the portal. You can then add new price list records directly in the portal. You can even sort the portal records to put the most recent price list record at the top of the portal, but then you may want to add a button to click to add a new price list record.

                                  here's one example for using a button to add a new price list record to the portal:

                                  Define a global date field gEffectiveDate and place it on your products layout above the price list portal. Put a button next to it to perform this script:

                                  IF [ YourTable::gEffectiveDate ]  // script only does something if field is not empty
                                      Freeze window
                                      Set variable [$ProductID ; Products::ProductID]
                                      Go To Layout [ PriceList ]
                                      New Record/Request
                                      Set Field [PriceList::ProductID ; $ProductID ]
                                      Set Field [PriceList::effectiveDate ; YourTable::gEffectiveDate ]
                                      Go To Layout [original layout]
                                  End If

                                  For your report, while I don't normally suggest using a portal in a report, it should work well here. Create a layout based on your Products table and add a portal to PriceList with just 3 rows and sort the records by effective date in descending order so that only the 3 most recent prices are listed in the report.

                                  • 14. Re: Comparing old field value with new one
                                    aammondd

                                    If you go the portal route and future date price changes you might add a filter on the portal of

                                    effective date < = Get (Current Date)