6 Replies Latest reply on Jan 27, 2017 2:52 AM by k-meson

    Update "Modified On" and "Modifed By" with a Portal Object Trigger ... or?

    lkeyes

      Hi all, need ideas on the following:

       

      I have a table "committee" with a related portal "members". that displays the members of the commitee.

      I've created fields in both the master table as well as its child table that are autoupdate fields for create date, modify date, and creator (account name) and modifer (account name)

       

      Now, when I create a new committee, the autoupdate happens flawlessly. However, I also want to change the modifed date and modified account name in the master record, whenever the user makes changes within the portal displaying the members. In other words, if the user adds, deletes or modifies a member record within a committee, I'd like the *committee* record to reflect that the change was made.

      Right now that doesn't appear to happen.

       

      I'm thinking that I could create a script trigger on the portal object (on object exit) that runs a script to do the update such that the script is something like

       

      SetField [Committees::Modifer; Get(AccountName)]

      SetField[Committeees:ModifyDate; Get(CurrentDate)]

       

      is this best practice, or is there some other approach I should be taking?

       

      Many thanks.

       

      --- L

        • 1. Re: Update "Modified On" and "Modifed By" with a Portal Object Trigger ... or?
          taylorsharpe

          You could just leave the last modified fields as they are and then create a calculation field in the committee table that shows the most recent date between the two.  So the calculation might be:

           

          If ( Committee::LastModifiedDate > Members::LastModifiedDate ; Committee::LastModifiedDate ; Members::LastModifiedDate )

           

           

          I recommend leaving the LastModified fields alone because they do what they are designed to do.  Having another table update the LastModified date really isn't doing what that function is designed as.  If you do, make sure to comment so people understand the LastModified in the Committee table is not really the Last Modified in Committe... it might be Last Modified in Members. 

          • 2. Re: Update "Modified On" and "Modifed By" with a Portal Object Trigger ... or?

            Hi Taylor,

             

            I believe that your calculation will only see the first related Member.  You know this of course when you look again - Members is the many side.

             

            Hi L,

             

            As Taylor indicates, a new date calculation can handle it but you must aggregate to compare against all child records, something like this calculation (result is DATE) in Committees:

             

            Max ( ModifyDate ; Max ( Members::ModifyDate ) )

             

            ... this will produce your latest modification and it will automatically be unstored so it will stay up to date.  But be careful where you place this field since it can be a bit resource hungry ( don't place it on list view with found set of 1,000 for example ).   

             

            Firstly, triggers do not run on calculation changes so it would be layout-dependent to know when to fire (I suggest using layout trigger such as OnRecordCommit instead of attaching triggers to each field.  However, there is something a bit off in the logic and I might misunderstand your need but ... if you write to a Committee record with the maximum Members::ModifiyDate ( when a Members field changes and is the latest change ), that will trigger the auto-enter Modification Date in Committees which will update and make IT the new 'last modified'.  I am not sure how that would work for you because the Members would never be the last.

             

            If I really needed this functionality in this way, I would be tempted to create a 1:1 relationship to a new table called Modifications ( not saying I would - just that I would be tempted ).

             

            ID ( its own unique serial )

            CommitteeID

            ModificationDate ( auto-enter modification date )

            Modifier (text) ... set via script

             

            A relationship from Members to this table would directly SET the single matching Modifications record when you set the Modifier with the current Account Name (and could fire from either table).  The Modification date would update because it was auto-enter so it will take care of itself.  The relationship on the Members connection would look like:

             

            Members::CommitteesID = Modifications::CommitteesID  <--- note that I changed this relationship

             

            From the Committees side ( if you decide to include it although it isn't necessary ) it would be the same ... joining on CommitteesID.  A single script step which writes to Modifier account name when a record is committed is fairly safe - you won't have record-locking because two Users can't modify same record at same time. But then if you go that far, why not provide your Users an audit log ( they are not difficult and NightWing Enterprises has great demo called Ultra Log ). 

             

            Just a few things to consider while you work through this.

             

            UPDATE:  Be sure you have the relationship checked to 'allow creation of related' on the Modifications table side so if a Committees record does not exist in Modifications, it will be created.

            • 3. Re: Update "Modified On" and "Modifed By" with a Portal Object Trigger ... or?
              taylorsharpe

              LaRetta is correct that my calc will only see the first related record and I was assuming a 1-1 relationship, which I realize is not accurate.  So you would want to use the max function like LaRetta suggests.  Of course, I would probably do it differently using ExecuteSQL because I am having fun getting to use this new feature.  But either way works. 

               

              Take particular note that LaRetta mentions that this can be a resource hog because unstored calculations really slow a database down, especially the bigger the database gets.  I avoid using unstored calculation fields in layouts in list or table view.  Technically, calcluation fields are very unusual to the database world and a fairly unique thing to FileMaker.  But it sure is a nice feature to have available.  Just use it with caution. 

              • 4. Re: Update "Modified On" and "Modifed By" with a Portal Object Trigger ... or?

                taylorsharpe wrote:

                 

                Of course, I would probably do it differently using ExecuteSQL because I am having fun getting to use this new feature.  But either way works. 

                 

                Thank you for the reminder ... every day now I'm finding myself saying, "Wait!  I could have had a ...SELECT! "   ExecuteSQL() alone is worth 12 in my view.  

                 

                ADDED:  I had also wanted to mention that I still do not know which of these ( or other ) methods I would choose to provide this functionality.  We simply do not have enough information at this point.   Influencing factors might be:  Platform, FM version, server config, number of users, existing structure and table configs, where/how the information will be used, displayed and reported on, whether it will be pushed to external sources and so forth.  And that is why I am hedging on which I would choose.  Even now.

                 

                Message was edited by: LaRetta

                • 5. Re: Update "Modified On" and "Modifed By" with a Portal Object Trigger ... or?
                  lkeyes

                  Hi, LaRetta and Taylor...  Many thanks for the discussion.... I wasn't ignoring your replies, I'm just cogitating.

                   

                  1. Since conceptually changing the members of a committee is a change to the committee, (from the user's perspective) I guess I'm less worried about the intrinsic integrity of the updated "modified on" and "modified by" fields in the commitee table from a programming perspective. I definitely agree that if that I end up going this way,the documentation needs to reflect the non-standard configuration. The user has requested that if there are any changes to the committee....the change should simply reflect who made it.  This isn't a real audit trail that would show change history.

                   

                  2. I like the SQL idea... will probably mess with that. I am interested in the performance issues...as I don't want to bog down performance, and don't reall want to add and maintain all the create/modify fields on the join table between members committees

                   

                  LaRetta....the current configuration is that the database is running on a Windows LAN, hosted by one Windows workstation with occasional changes being made by two other Windows workstations.  Pretty small potatoes at this point,  max of three users.  There is a join table between the committee and members tables.  

                  • 6. Re: Update "Modified On" and "Modifed By" with a Portal Object Trigger ... or?
                    k-meson

                    I needed this feature too but simply achieved it by creating a timestamp field on the related table with autoenter modified date and time. Create a second relationship to the related table and order it by the timestamp field in descending order. The most recent updated record is always the first in this related table so you can display any of the data from there or use a calculated field based on the relationship to show the latest update info.

                    Seems simpler than using SQL etc...