1 2 3 Previous Next 32 Replies Latest reply on Nov 8, 2012 3:17 AM by ChrisSmythe

    Modifying field values based on certain criteria.

    ChrisSmythe

      Title

      Modifying field values based on certain criteria.

      Post

           Hello, I am using FileMaker Pro 12 on Windows XP.

           I am hoping someone would please be kind enough to help me with the following problem:

           I have a link to my Test 15.fmp12 database (Account Name: Admin & Password: "there is no password" just click OK.

           https://www.sugarsync.com/pf/D0681807_60165357_23551

           I have previously posted this before.  It is under the heading of "Calculation for DeployedStatus Field".  Link: http://forums.filemaker.com/posts/2630e3b0a9

           Unfortunately, being a newbie, I was unsure how to ask for what I wanted.  Forum members such as Sorsbuster & S Chamblee were very kind and gave me correct FileMaker code.  I should have asked the right question, but do not have the requisite experience and knowledge to know exactly what to ask.

           I tried using a script trigger on the DateDeployed field and specified the onObjectModify event to run a script that I named: Change DeploymentStatus to Deployed.  Here is the steps in the script (kindly provided by S Chamblee):

           If [DbUserEdits::DateDeployed = Get (CurrentDate) and DbUserEdits::DeploymentStatus = "Pending"]
               Set Field [DbUserEdits::DeploymentStatus; "Deployed"]
           Commit Records/Requests [No dialog]
           End If

           I do not know how to trigger the event?

      Background on my database:

           My FileMaker database runs in a 24-hour, 7-days per week Call Centre.  We have two people operating on each shift.  There are 3-shifts per day.  The only time our FileMaker database "goes-down" is when our work computers "go-down".  Our work computer has never "gone-down" for a period greater than 48-hours.

      What I need is (possibly a script?) to run at time 0001 (one-minute past midnight) each week day (Monday to Sunday inclusive) in the background (without user intervention).

           I need to change the DeployedStatus field value from "Pending" to "Deployed" when the DateDeployed = CurrentDate for all records in the DbUserEdits table.

      Please note that if our work computer "goes down" --- very rare occurrence, then I still need to undertake the above-mentioned change retrospectively.

           Any help would be greatly appreciated.

           Kind regards,

           Chris :)

        • 1. Re: Modifying field values based on certain criteria.
          schamblee

               Right Click the deploydate field then select set script triggers and then select the onObjectModify, then select script.  Create new script with the above script and then select. Whent DeploymentStatus is equal to Pending and then the DateDeployed is change to todays date then DeploymentStatus will be changed from Pending to Deployed.  The script is trigger any time the DateDeployed field is modified changes only occure when the if statement is true.

          • 2. Re: Modifying field values based on certain criteria.
            philmodjunk

                 Why do you want things to update at a specific time of day?

                 Is this database published to the web or do all your users use copies of FileMaker Pro to access the database?

            • 3. Re: Modifying field values based on certain criteria.
              ChrisSmythe

                   Hello S Chamblee and PhilModJunk, thank you for your reply - much appreciated.  I used the buit-in FileMaker help to work out how to set up a trigger script as described by S Chamblee.  It had me baffled, as to why it originally did not work for me.  After many hours I discovered that I used the incorrect field type for my date fields.  I have now changed my date fields to field type: Date.  Now the trigger script works.  However, it did not do exactly what i expected it to do.  I thought it would change all the records in my DbUserEdits table whereby:

                   If the DateDeployed field = CurrentDate and DeployedStatus field = "Pending"

                   Then Set Field DeployedStatus to "Deployed".

                   Is my assumption incorrect or have I done something wrong?

              Please note that if our work computer "goes down" --- very rare occurrence, then I still need to undertake the above-mentioned change retrospectively.

                   This database will be published to the Web.  We currently have only one licence for FileMaker.

                   I have a link to my Test 16.fmp12 database (Account Name: Admin & Password: "there is no password" just click OK.

                   https://www.sugarsync.com/pf/D0681807_60165357_35551

                   Your continued kind assistance is highly valued.

                   Kind regards,

                   Chris :)

              • 4. Re: Modifying field values based on certain criteria.
                philmodjunk

                     Please note that Script triggers cannot be directly tripped by the user when they access your database from a web browser.

                     Replace field Contents can update a field in every record in your found set. (To update all records, do a Show All Records to move all records into your found set.)

                     There's a calculation option that can be set up to only change the value if values in other fields in that record meet your criterial.

                     Your calculation might look like this for daily updates:

                     If ( DateDeployed = Get ( currentDate ) And DeployedStatus = "Pending" ; "Deployed" ; DeployedStatus )

                     To do it retroactively, you can put a date into a global date field and use:

                     If ( DateDeployed = GlobalDateField And DeployedStatus = "Pending" ; "Deployed" ; DeployedStatus )

                     Replace Field Contents can modify data in literally 1000's of records all in one go and can't be undone. Thus, it's a very good idea to save a back up copy of your file before trying to use this for the first time.

                • 5. Re: Modifying field values based on certain criteria.
                  ChrisSmythe

                       Hello PhilModJunk, thanks for replying and providing me with your valued advice - highly appreciated.  I have been looking through the FileMaker help files and with my very limited knowledge and experience, have no idea on how to implement your suggestions.

                       I tried setting up a Global Variable field (I named it: GlobalDateField) in my DbUserEdits table and set the field type to calculation and in the Otions for Field dialog box I selected the Storage tab and then selected Use global storage (one value for all records).

                       As soon as I chose Data Type: Calculation, the Specify Calculation windows poped-up and I entered GlobalDateField = Get(CurrentDate)

                       I am guessing that this is incorrect?

                       After reading about Global Variables, I really do not understand how they work?

                       I do wish to use your suggested calculation:

                       If (DateDeployed - GlobalDateField And DeployedStatus = "Pending" ; "Deployed ; DeployedStatus)

                       Do I use your calculation in lieu of:

                       If [DbUserEdits::DateDeployed = Get (CurrentDate) and DbUserEdits::DeploymentStatus = "Pending"]
                           Set Field [DbUserEdits::DeploymentStatus; "Deployed"]
                       Commit Records/Requests [No dialog]
                       End If

                       Or, is your suggested calculation part of a script I need to create?

                       I am not sure what I need to do and in what order?  Could you please help? - Also, I understand I need a Global Variable (but do not know what calculation needs to be associated with it?)

                       Kind regards,

                       Chris :)

                  • 6. Re: Modifying field values based on certain criteria.
                    ChrisSmythe

                         Hello, I was wondering if someone could please advise on how to set up a Global Date Variable so that I can continue working on the above-mentioned problem. I need to know what field type to set my Global variable to and what value to assign to it.  I named my Global Variable as: GlobalDateField.  I will be try to use PhilModJunk's suggestion shown above in the folowing statement:

                         If ( DateDeployed = GlobalDateField And DeployedStatus = "Pending" ; "Deployed" ; DeployedStatus )

                         Any help would be greatly appreciated.

                         Kind regards,

                         Chris :)

                    • 7. Re: Modifying field values based on certain criteria.
                      ChrisSmythe

                           I have tried to clarify my question above - hopefully it makes sense.

                           Kind regards,

                           Chris.

                      • 8. Re: Modifying field values based on certain criteria.
                        philmodjunk

                             Apologies, but I did not choose to involve myself in the forum yesterday. I am an "unpaid volunteer" and chose to deal with othe professional (paid) obligations yesterday.

                             My suggestion does not use any global field nor any global variables. (They are two different things in FileMaker.)

                             My suggestion was to pull up a found set of the records to be updated and then use replace field contents to upate them. The If function that I posted was to be used with the calculation option available for use with Replace Field Contents.

                        • 9. Re: Modifying field values based on certain criteria.
                          ChrisSmythe

                               Hi PhilModJunk, no apologies needed - I fully understand.  Just so that I have a clear understanding of what you have suggested thus far:

                               I believe that you are suggesting that I need to create a script that performs a find to locate all records in my DbUserEdits table that need updating and use the Replace Fields Contents - by using one of the below-mentioned calculations whereby:

                               Your calculation might look like this for daily updates:

                               If ( DateDeployed = Get ( currentDate ) And DeployedStatus = "Pending" ; "Deployed" ; DeployedStatus )

                               To do it retroactively, you can put a date into a global date field and use:

                               If ( DateDeployed = GlobalDateField And DeployedStatus = "Pending" ; "Deployed" ; DeployedStatus )

                               Where I am confused, is that I do need to do this retroactively and was wondering do I need to create another field in my DbUserEdits table named GlobalDateField? (I believe I understand this field is not a global field or global variable).

                               Kind regards,

                               Chris :)

                          • 10. Re: Modifying field values based on certain criteria.
                            ChrisSmythe

                                 Hi PhilModJunk,

                                 I forgot to ask, could you please briefly explain how the following If statement works so that I understand what is going on?

                                 if( DateDeployed = GlobalDateField And DeployedStatus = "Pending" ; "Deployed" ; DeployedStatus )

                                 I ask this because I do not understand how the GlobalDateField works retroactively.

                                 Kind regards,

                                 Chris :)

                            • 11. Re: Modifying field values based on certain criteria.
                              philmodjunk

                                   You can use a script, but you don't have to. You can select Replace Field Contents from the Records Menu instead.

                                   To add a field to your database, open Manage | Database, Select a table in the drop down, enter the name of the field and the desired data type (Date in this case) and click create. Then click the options button to open field options, click the storage tab and select the global storage check box.

                                   A global field can be accessed from any layout and script in your file so it does not matter where it is defined in terms of function. I often set up a globals table to better keep track of these useful fields unless I also need to use the field in a relationship--something that you don't need here.

                              • 12. Re: Modifying field values based on certain criteria.
                                ChrisSmythe

                                     Hi PhilModJunk, thank you for your help - very much appreciated.  I have chosen to do a script because I want to make changing a person's DeployedStatus from "Pending" to "Deployed" an easy process for the user of my database.  I named the script: Change Personnel Deployment Status to Deployed.  I also added the new field named: GlobalDateField to my DbUserEdits table.

                                     I have tried to implement your suggestions to do this task.  Unfortunately, I after considerable time, I was unable to get the person's DeployedStatus to change from "Pending" to "Deployed" when their DateDeployed = CurrentDate.

                                     I was hoping you may provide further assistance in getting this task to work properly for me and to assist you I have attached "fake data" in my database at the following link:

                                     https://www.sugarsync.com/pf/D0681807_60165357_37551 (Account Name: Admin    Password: either press <Enter> or click OK as there is no password).

                                     I am not sure what I have done wrong, but are hoping you can help.

                                     Kind regards,

                                     Chris :)

                                • 13. Re: Modifying field values based on certain criteria.
                                  ChrisSmythe

                                       Hi PhilModJunk, I just updated the information in my last post - hope this helps.

                                       Kind regards,

                                       Chris :)

                                  • 14. Re: Modifying field values based on certain criteria.
                                    philmodjunk

                                         You have two problems in your script:

                                         1) the find part of the script incorrectly specifies find criteria.

                                         Change:

                                         Set Field [DbUserEdits::DeploymentStatus ; "Pending"  and DbUserEdits::DateDeployed=Get(CurrentDate) ]

                                         to:

                                         Set Field [DbUserEdits::DeploymentStatus ; "Pending"]

                                         St Field [DbUserEdits::DateDeployed ; Get(CurrentDate)

                                         also

                                         Perform Find [Restore]

                                         should be changed to:

                                         Perform Find []  ----> clear the specify checkbox in this script step.

                                         2) The replace Field Contents step does not specify a field to modify.

                                         You have:

                                         Replace Field Contents [If(DbUserEdits::DateDeployed=DbUserEdits::GlobalDateField and DbUserEdits::DeploymentStatus="Pending" ; "Deployed" ; DbUserEdits::DeploymentStatus) ]

                                         When you should have:

                                         Replace Field Contents [ DbUserEdits::DateDeployed ; If(DbUserEdits::DateDeployed=DbUserEdits::GlobalDateField and DbUserEdits::DeploymentStatus="Pending" ; "Deployed" ; DbUserEdits::DeploymentStatus) ]

                                         Since you are performing a find such that this If function will always evaluate as true, you can simplify this script step to be:

                                         Replace Field Contents [ DbUserEdits::DateDeployed ; "Deployed" ]

                                         And if you click the "no dialog" option for this step, it won't halt and ask for confirmation like it does now.

                                    1 2 3 Previous Next