11 Replies Latest reply on Dec 22, 2015 3:00 AM by reink-dawg

    How can date be automatically entered when only one field is modified?

    reink-dawg

      I have records that contain a Starting Quantity field, a Current Quantity field, a Quantity Sold field which is a simple calculation field and a Date Sold field.  I would like the Date Sold field to automatically show the current date whenever the Quantity Sold field is increased (or the Current Quantity field is decreased.

       

      Setting the Date Sold field to "auto-entry" Modified, sets the current date whenever any field is modified which I don't want.

       

      Any suggestions?

        • 1. Re: How can date be automatically entered when only one field is modified?
          Markus Schneider

          depending on the way FileMaker calcultes/updates the field value, You can have a date- or timestamp-field with that formula

           

          TheTimeStampField

          case

          (

          MyFieldToCheck01 = MyFieldToCheck01; Get(SystemTimeStamp);

          MyFieldToCheck02 = MyFieldToCheck02; Get(SystemTimeStamp)

          )

          • 2. Re: How can date be automatically entered when only one field is modified?
            coherentkris

            Because you want to track modification timestamp under very specific conditions you'll have to leverage object level script triggers to capture the field value before edit, compare the after edit value with the before edit value and set the timestamp appropriately.

            Understanding and using Script Triggers | FileMaker

            • 3. Re: How can date be automatically entered when only one field is modified?
              siplus

              Your original setup has "no memory". Once you do a sell, you change the current quantity and the quantity sold and there is no clue for what the last transaction was, the data reflects only the current one.

               

              You should rethink your implementation and create a sell record each time you sell a quantity of a specific product.

               

              Your original problem will autosolve, as the date of the record just created will reflect the sell date.

              • 4. Re: How can date be automatically entered when only one field is modified?
                reink-dawg

                Thank you.  I have limited experience with scripts and/or calculations but I have set up some rather simple scripts.  Not exactly sure that I understand how to set this up.

                • 5. Re: How can date be automatically entered when only one field is modified?
                  reink-dawg

                  Thank you.  Having limited experience with scripts I’m not sure I understand how to accomplish this with your suggestion.

                  • 6. Re: How can date be automatically entered when only one field is modified?
                    reink-dawg

                    Thank you.  I realize that the data only reflects the last transaction.  If I understand your solution, a new record would need to be created for each sale.  That would seem to make the file rather cumbersome.  The file is set up to reflect the creation date of any new record now.  I really don’t need the complete sell dates of the products, just the latest one.

                    • 7. Re: How can date be automatically entered when only one field is modified?
                      johan

                      You should set your Date Sold field to an auto-enter calculation. Remember to uncheck "Do not replace existing value...".

                       

                      The calculation will use the Evaluate function, like this:

                      Evaluate ( "Get ( CurrentDate )" ; [ Starting Quantity ; Current Quantity ] )

                       

                      Whenever any of the fields in brackets are modified, the Evaluate function will trigger a re-evaluation.

                       

                      Note that you have to quote the calculation you want to perform when you use the Evaluate function.

                      • 8. Re: How can date be automatically entered when only one field is modified?
                        RalphLearmont

                        Sometimes this type of dependency situation comes up where you want one field to re-evaluate itself dependent on certain conditions, and in most cases it's when another field simply changes (or is re-entered).  Your situation is similar except you stipulate that it's when the "Quantity Sold" field is *increased* (not just changed).  That makes it a tighter stipulation.

                         

                        As others have explained earlier, this tighter requirement means you need comparison fields; alternatively you need a scripting trigger to capture values just prior to making field changes.  This way, it's possible to use a calculation to determine what's happened and go from there.  For example if the current Quantity was increased rather than decreased, you don't want the Date to change.

                         

                        I've taken my own approach and introduced a field "Sold Today".  Reason is to allow a simple comparison to be made, and to keep a single record situation (which you just mentioned as being preferred)...  That is my approach follows this reasoning (right or wrong) that you can enter a number, be it daily, hourly or whatever, and that number is tied in with your other fields and will update them accordingly - subject to the condition that the number "Sold Today" is going to be positive.  If it's less to equal to zero, those other fields won't update.

                         

                        To allow re-setting of fields, you need to allow override, and this means using auto-enter calculation fields instead of normal calculation fields.  As I've mentioned in the demo file here, to re-set (initialise) fields, first clear the "Sold Today" field first.

                         

                        Ralph

                        • 9. Re: How can date be automatically entered when only one field is modified?
                          reink-dawg

                          Thanks for the possible solution.  As I look at the formula, any change in starting quantity or any additions to current quantity would trigger the date to be added to the “date sold” field.  There are days that I add product to Current Quantity without actually making a sale.

                           

                          Would this possibly work better:

                           

                          Evaluate ( “Get ( CurrentDate )” ;  )

                           

                          This wouldn’t solve the problem of adding to current quantity.  Does the “Evaluate” formula need two or more fields after the “Get CurrentDate” or could it just use one?

                           

                          Glen

                          • 10. Re: How can date be automatically entered when only one field is modified?
                            RalphLearmont

                            As I look at the formula, any change in starting quantity or any additions to current quantity would trigger the date to be added to the “date sold” field.

                            It doesn't happen.  Try entering something into these fields and you'll see they don't affect the "Date Sold" field. They are only dependent on what's in the "Sold Today" field.  That is, their calculation mentions field "Sold Today", not field "Date Sold".  The reason I used calcs in them was to assist in their update (whenever you sell stuff), but those calcs can be disabled or else modified to suit your needs.

                             

                            Would this possibly work better:

                             

                            Evaluate ( “Get ( CurrentDate )” ;  )

                             

                            It will also work but you'll need to add one or more triggering fields in the manner explained earlier by Johan Småros Dec 22, 2015 2:17 AM


                            That is, use:   Evaluate ( "Get(CurrentDate)" ; Sold Today )


                            or use:    Evaluate ( "Get(CurrentDate)" ; Quantity Sold )   if you prefer.


                            Doing it this way means ANY change (up, down, or anything) ( to any referenced field) will cause an update - as opposed to your earlier stipulation of only any *increase* in Quantity Sold.


                            Does the “Evaluate” formula need two or more fields after the “Get CurrentDate” or could it just use one?


                            One, two or whatever fields you require...  Suggest you check out Filemaker Help for how the Evaluate function works.


                            Regards

                            Ralph


                            • 11. Re: How can date be automatically entered when only one field is modified?
                              reink-dawg

                              Thank you so much for your suggestions - you are obviously very knowledgeable about the workings of FileMaker.

                               

                              Glen