4 Replies Latest reply on Apr 15, 2014 7:52 AM by taylorsharpe

    Best practice: How to mark a set of records

    Polarpro

      Hey there...

       

      I've tried a few ways to mark a whole set of records, but am not too happy with the outcome. Here's the problem:

       

      A user searches for some records in a "Persons" table, and then calls a script that sends an email message to some other users, this message contains a list with the names of the Persons records in the found set. The script then sets the "Sent" field with the value of the current day in each record, so that we know: This record already has been announced by email on YYYY-MM-DD.

       

      The problem is that one of the records in the found set might be open as an other user is working on it. In this case the Sent field cannot be set, as the record is locked.

       

      I created a record lock test for my script, so that in case a record is locked the script stops and tells the user that he has to make sure that the locked records need to be committed.

       

      The thing is just: I only want to send the email message and set the Sent field if ALL records of the found set are available. So, first when I am sure that ALL records are unlocked I want to send the message and then set the Sent field.

       

      I cannot think of another solution than testing each record if it is locked, if no opening it, and then (if all records of the found set are unlocked), doing the following steps. This seems a little complicated though to me. In particular with a large found set.

       

      Is there a better way of dealing with this issue?

       

      (I currently work with FM 10 and am in the process of migrating my solutions to FM 13.)

       

      Thanks, Mike

        • 1. Re: Best practice: How to mark a set of records
          wimdecorte

          Check out the transactional model: http://www.modularfilemaker.org/2013/04/transactions/

          This way you can loop and open ALL the records you need before sending out the email.  If one of the records can not be opened, you can back out of the transaction and nothing hapens.

           

          Alternatively, you could keep the email notification data in a different table.  That table would have a record for each record in the Persons table that gets emailed, with fields for when the email was sent and by whom.  This way you can see a history of every time that person was emailed to, instead of just the last time (which is what you get with the "sent" field in the Persons table itself).

           

          With that "history" table you also do not have to worry about the record in Persons being locked or not, you are not updating that record.  And since you always create records in the history table there can not be record lock issues there either.

          • 2. Re: Best practice: How to mark a set of records
            DamianKelly

            Or you can have a one to one relationship to another table with only the Person Key and the sent status field? Not ideal for tip top performance as you may be finding over a join but it is not the end of the world with cute use of go to related records.

             

            If you are mega paranoid you can record the history in a second table and take the last value. So a person may have 3 records in the 'emailSent' table, with values of Unsent, Pending and Sent. If you have a creation timestamp field you can display the last over a sorted relationship. Other users struggle to lock records you just created 

            • 3. Re: Best practice: How to mark a set of records
              Polarpro

              Hello,

               

              Thank you very much for your fast advice. I like the transactional model very much, but will stick to a solution that seems to be easier to implement for now. I created a One to One relationship to a new table as Damian suggested. As we don't filter hundreds of records things work still very fast. With this way of dealing with the problem people can click the "Send E-mail" button while other people are still working on the records, and the timestamp gets set correctly.

               

              Thanks again!

              Mike

              • 4. Re: Best practice: How to mark a set of records
                taylorsharpe

                If it were up to me, I would do like wimdecorte suggests by keeping track of sent records in a related table that the other staff don't have access to so they can't lock records.  Plus adding fields to a table by using another table and one-to-one relationships reduces the chances of record locks and increases performance since FileMaker pulls and stores whole records at a time and if you limit the number of fields in a table, this process goes faster.