8 Replies Latest reply on Nov 19, 2016 1:29 PM by keywords

    Obtaining a stored field from unstored calculation

    Wicktor

      In a sales database, for having sales statistics related to clients, I have tagged each purchase with 3 different tags

      FIRST PURCHASE (the first time a client has purchased a product)

      REPURCHASE (every purchase happened after the first time)

      LAST PURCHASE (the last time a client has purchased a product)

       

      The TAG field is calculated as follows:

      Case(

      DATE = Min (SELLS::DATE) and DATE ≠ Max (SELLS::DATE); "FIRST PURCHASE";

      DATE = Max (SELLS::DATE) and DATE ≠ Min (SELLS::DATE); “LAST PURCHASE";

      "REPURCHASE")

       

      It works, but only problem it is an un-stored calculation and counting a few hundred thousands records it takes quite long.

      I tried using a TAG_STORED text field with auto-enter calculation referring to the relationship: it seems not working since the TAG value is calculated only at record commit.

      I tried using a TAG_STORED lookup field. It works, but again it needs to relookup all the database with many hundred thousands records, still quite long.

       

      I guess I am following the wrong direction.

      Anyone has a brighter idea ?

      Victor

        • 1. Re: Obtaining a stored field from unstored calculation
          taylorsharpe

          There are different ways to handle this.  One thing that I often do is reference the modification timestamp field (assuming you have one) because that will force an update any time something is changed in that record.  You can use script triggers for when you exit fields to update or whenever you commit the record or load the record.  You can also run a batch job periodically to go through and update the calculation field.  I often have both fields, one stored and one unstored and will run a batch job at night comparing the stored to the unstored and if they don't match, then put the unstored calc into the stored one.

          • 2. Re: Obtaining a stored field from unstored calculation
            justinc

            Hi Wicktor,

                I hope that I am understanding your workflow and model correctly.  It sounds like you have a CLIENTS table and a PURCHASES or SELLS table.  I will also assume that each SELLS record is related to a specific CLIENT record.  It sounds this Tag field is in Clients also:  Clients::Tag.

             

            I think that you would do well to have this be a scripted process.  This Tag you are creating back in the Clients table will only change whenever a new SELLS record is created, so I don't think that it makes sense to recalculate things all the time via an unstored calc...and this does appear to be your goal. 

             

            I'm not exactly clear on the purpose of the Tags field, though, because it seems like you are overwriting data that should perhaps be stored.  I would also suggest at least 2 fields:  Clients::FirstPurchase, Clients::LastPurchase.  And perhaps you want a third field..."Repurchase"?  (It's not clear to me what "Repurchase" status means from the point of view of the Client record.  If it's in the "Sells" record, then that makes more sense.)

             

            So the scripted process would create the Sells record and relate it back to the Client record.  I would suggest that the script then navigate to the Client record (it's a related record now, so can use GTRR), and it could update the "First" and the "Last" fields.  It would do this calculation by looking back through a relationship from Client -> Sells.  (Is this Product specific?  Then you might need a new relationship that is also keyed by the current ProductID.)  Or you could easily use an ESQL statement to calculate the number of related Sells records for a give Client and Product.

             

            Whenever a new Sells record is created this is always the "Last Purchase", so just set the Clients::LastPurchase field to the current timestamp.  While on the Client record you could also count the number of related Sells records; if there is only one related record, then this is the First Sells created for that Client, and you would set the "FirstPurchase" field to the current timestamp.  (I'd recommend grab a timestamp either from the Sells record you just created or set it in a variable at the top of the script, and then use that value for both the FirstPurchase and Last Purchase fields...don't recalculate the timestamp for each field - they should be the same when it's the FirstPurchase.)

             

            Now...for the Repurchase Tag...that again seems like it should live in the Sells record itself.  I'm not sure how tracking at the Client record if this purchase was a new product or not helps.  But each Sells record that could be interesting...although, this data is somewhat implicit in the list of Sells records itself.  If there is only one Sells record for a particular Client and a specific Product then this is a FirstPurchase; if there are multiple records then this is a Repurchase.  (I assume that a Repurchase only applies to a specific Product...or are you tracking if a particular Client has ever purchased anything from you before?)  So this result is the same result as the calculation I noted above for determining if it's a FirstPurchase:  if there's 1, it's a First; if more than 1, than it's a Repurchase.

             

            You could flag this 'First' and 'Repurchase' data back on the Sells record as well, in the same manner as for the Client side:  use a relationship and count related records, or use an ESQL query to count the same thing.  Tracking the 'Last' purchase on the Sells record is more dubious, because you would have to go back to the previous 'Last' Sells record, clear that flag, and then set it on the current one.

             

            (This turned out rather long...I wonder how many other people have replied since I started this?  )

            1 of 1 people found this helpful
            • 3. Re: Obtaining a stored field from unstored calculation
              philmodjunk

              I think that Justinc has the right idea here. The key is to never have to make a "mass update" of these stored values but to only update the value(s) from a single record at the moment in time where some user action has made a change that affects what value should be stored in that field.

               

              But you will need to be careful to create scripts to handle any data modifications that affect the value now returned by your unstored calculation so that all such events correctly update the stored value. This includes any actions that might delete or void a sale/purchase record--so this can sometimes require either preventing a user from deleting records except by running a script (clicking a button) or by setting up a custom menu where delete record from the records menu runs your script to both delete the record and update the stored value(s).

              1 of 1 people found this helpful
              • 4. Re: Obtaining a stored field from unstored calculation
                Wicktor

                A lot of food for my brain

                For now many thanks for the three answers.

                I need to carefully go through them give a try and I will be back.

                • 5. Re: Obtaining a stored field from unstored calculation
                  Wicktor

                  Hello Justinc,

                  Thank you for your exhaustive explanation.

                   

                  Some answers to your questions:

                  - yes, one CLIENT Table and one SELLS Table

                  - yes, Repurchase is necessary only in SELLS Table

                  - no, not necessary product specific

                   

                  Honestly, I found difficult to reproduce exactly as you explained.

                  I would avoid timestamps because it may happen that some records are not inserted on the actual date.

                  Also, accordingly with your suggestions, I found (I am sure it is my mistake) that CLIENT fields are updated only when SELLS record is committed.

                   

                  So, I adopted a trick that I found easier to implement.

                   

                  1) on SELLS Table: I have set SELLS_TAG as text, stored, auto-enter calculation with the previous formula i had for the non-stored field

                  2) on SELL Table, after exiting DATE field a script is triggered OnObjectValidate

                    a) the script restricts SELLS records bases on actual NAME

                    b) the script updates related records using Replace Field Contents script step (using NAME = NAME) which forces the SELLS_TAG to recalculate in all records

                   

                  In this way, every time a SELLS date is changed, the TAG is recalculated and the “LAST SELL” is also updated every time a new record is created.

                  Enclosed are the pictures showing the above explanation

                   

                  It works, even if I cannot realise yet of any potential limit of this method.

                  I am very interested in your opinion about the above solution compared to what you would do as you explained.

                  Victor

                  • 6. Re: Obtaining a stored field from unstored calculation
                    justinc

                    I see a couple of issues, but only one of them major:  In your relationship graph you connect these tables together via "Name" field.  What happens when you get clients with the same Name?    You should switch to using an ID field of some sort (serial or UUID).

                     

                    But I would start with some additional questions:  how is this 'Tag' being used?  What do you need it for?  Do you want to go to a Client record and look at their 'Last Sell'?  Or their 'First Sell'?  And it's not clear to me what the intention of 'Repurchase' and 'Uniqure Purchase' are - maybe that would be explained by understanding what you use this Tag for.

                     

                    And it sounds like a Sells record can be entered at non-sequential times, edited at any time, and it's Date could perhaps be changed...thus you need to refresh other stored data.

                     

                    The other issues are related to performance.  If this system works for you, and you get the results you want, then that's fine - it works.

                    One issue is in your calculation:  it might not be a huge issue (except for issue #2) but it would probably help your performance to use something like this:

                        Let ( [

                          _max = max (Sells.name::Date);

                          _min = min (Sells.name::Date)

                        ];

                            Case (

                                Date = _min and Date ≠ _Max ; "First";

                                Date = _Max and Date ≠ Min ; "Last"

                    ...

                            )

                        )

                     

                    The improvement here is that you are only evaluating what the Min or Max Date is once each, instead of 3 times each.  Saves you 4 calculation engine passes.

                     

                    The larger issue I see is that you are updating all of your Sells records for a given name every time a Sells Date is changed.  At first, this might be OK if there are only a few records to deal with, but what happens if someone has 30-40+ records?  This process could take a while (especially if this is a hosted solution and you are working over the Internet).  And if you have a slower calculation it will take even longer.

                     

                    But again, this might be better addressed if we understood more about how you want to use this data.  Depending on its usage, it will help determine if you could (or SHOULD) move this calculation to the Clients table instead of the Sells table.  If you can move it to the Client record, then you only have to update 1 record, instead of 30-40 (or whatever).

                     

                    Even if you do move this data to the Clients table/record, the actual calculation might be the same as you have already worked out.  I don't know of a better or 'best' way to calculate Min() and Max() Dates.  You could create relationships that filtered by Name and are sorted by Date...and then depending on if you sort Up or Down (one relation for each type), when you read the first record across that relationship you get the Min() or Max().  You could use an ESQL call to do the Min() and Max() for you.  I'm not a huge fan of using relationship like this though.

                     

                    I also would suggest you use a different name for the Date field...it can become problematic with function names and ESQL reserved words.

                    1 of 1 people found this helpful
                    • 7. Re: Obtaining a stored field from unstored calculation
                      Wicktor

                      Hello Justinc,

                      thank you for your feedback.

                       

                      the Client Table does not accept duplicate Names.

                      If a name is already stored, the user adds a suffix after name (the owner of the database is a friend who asked me some help and he is fine working in that way), then the invoice is printed avoiding the suffix.

                       

                      The Tag is used by the owner for statistical purposes: check how many sells by client divided by Tag or as a total yearly statistics about how many First Sell generated Repurchases, and also based on other variables (age, gender, location, etc).

                      That was the main reason he was looking for a “stored” value, for having much faster results.

                       

                      Thank you for the suggestion about calculation performance, good hint !

                      I will certainly use that.

                       

                      For what I see in the data I have, the average client has some 8-12 purchases, some clients have up to 35 purchases.

                       

                      The idea of having only one calculation on Client Table sounds very good to me.

                      I guess it was your idea when your wrote your first suggestion, in that case I need to work on it, since some steps of what you suggested were not clear to me.

                      • 8. Re: Obtaining a stored field from unstored calculation
                        keywords

                        Re: "the Client Table does not accept duplicate Names. If a name is already stored, the user adds a suffix after name (the owner of the database is a friend who asked me some help and he is fine working in that way), then the invoice is printed avoiding the suffix."

                        Even if this is the case (i.e. you think the field "will not accept duplicates" it would accept "Joe Smith", " Joe Smith", "Joe  Smith" and "Joe Smith " as non-duplicate values—which emphasises why it is bad practice to use human-useable, human-modifiable, human-meaningful fields as match fields. By all means use the name field—with suffixes or whatever you like to distinguish otherwise matching names—as a second, meaningful value in a value list, for example, but don't use it to build FM relationships; it will trip you up somewhere along the line.

                         

                        On the issue you asked about—stored v unstored values—it seems to me you could simply make your field a standard, therefore stored, field with an auto-enter calc, instead of making the field itself a calculation. A First Purchase is that when the record is created and always remains thus. Similarly a Repurchase is a repurchase when made, so could also have that evaluated on creation and then stored forever. The only item in your schema that needs constant re-evaluation is which is the most recent, and your could drive this through a simple date to date relationship.

                        1 of 1 people found this helpful