11 Replies Latest reply on Aug 22, 2012 1:42 PM by philmodjunk

    Documenting Changes Within Hosted Database



      Documenting Changes Within Hosted Database

      Your post


      I'm looking to host a database through FM server, it will be accessed by several users using FM Go and FM Pro. The database will contain processes that are required to be signed off every time they are either completed or updated. Theses signed processes would then ideally be saved as a report document out with Filemaker for auditing purposes. I've not really used FM server before but have briefly read up on scheduled tasks etc, I'd want the report document to be created as soon as it's signed off though.


      Any suggestions would be appreciated. Cheers

        • 1. Re: Documenting Changes Within Hosted Database

          This isn't really an issue where using Server has any effect on the methods needed to do what you want.

          You can add fields that auto enter date, time and account and/or user names each time the record is modified and/or created. You can also set up a related table that functions as a "change log" and can include a 'sign off' field where the user enters a signature via FM GO or a password via FileMaker. (It's also possible to capture a signature, but only if you set up the correct combinations of hardware and software to capture that signature--in FM GO it's a built in capability.)

          Your report can then be generated from these tables and fields.

          • 2. Re: Documenting Changes Within Hosted Database

            Hi PhilModJunk,

            I would like to do what you have suggested above for Matthew, to add fields that auto enter date, time, and account each time the record is modified, and to also set up a "change log" table with a "sign off" field prompting the user to enter a password via FileMaker.  How do you do that? Thanks.


            • 3. Re: Documenting Changes Within Hosted Database

              Open Manage | Database | Fields. Add a TimeStamp field and click options or double click the new field definition to open field options.

              Click the auto-enter tab.

              You'll find an option on this tab for auto-entering the modification time stamp--the date and time current the last time the record was modified.

              Add a text field and return to the same tab in field options where you can specify a modification account name.

              To add a change log requires adding a related field where you can log a version number, date and summarize what modifications where made since the last update was signed off. See the Opening Layout of the Known Bugs List for one simple example: https://www.dropbox.com/s/jt09b82i0xijbu3/FMP%20Bugs.zip

              To ask the user to enter some kind of password or identifying code, you can use the show custom dialog script step to ask for the input.

              • 4. Re: Documenting Changes Within Hosted Database

                Hi again Phil,

                Thanks for the advice.  So, I set up my database using the "Content Management" Starter Solution template, which has a change log already in it.  It is nice in that it makes new fields for the Revision number, user, and date after you finish adding that info for your Revision, but I am trying to make a log that automatically recognizes that a modification has been made in the record and then prompts the user to record what revisions they made.  Is there a way to do that in Filemaker Pro?  Also, if someone makes a modification to the record, does FMP keep a record of that somewhere?

                Regard another topic, I couldn't get the program to ask the user to enter a password code.  I can show the dialog script, but I couldn't find a way to make a security window open and require a password.  In essense, if the user is on the database, they will have already submitted their password once so I guess it is not a big deal, but if there were a way to do it, it would be a little slicker.



                • 5. Re: Documenting Changes Within Hosted Database

                  You can use the OnRecordCommit trigger to prompt a reminder to log any changes. This trigger cannot be tripped unless you modify data in a field first.

                  With regards to show custom dialog, you can specfiy an input field for the dialog along with the "use password character" option. This is not the FileMaker log in dialog and you can set your script to test for any password that you want. Please remember that you can capture the account name of the current user any time they modify a record without any scripting at all.

                  • 6. Re: Documenting Changes Within Hosted Database


                    Thanks for the tip about the OnRecordCommit trigger.  I found that option, but now my next trouble is programming the accompanying script.  I don't have much programming experience, so I may need you to explain more details than you normally would.  I have a change log (or 'portal') that includes 4 fields:

                    1) a Revision number,

                    2) a Timestamp that records the date/time the modification was made;

                    3) a User field that displays the name of person logged in and making that modification, and

                    4) a Notes field for the User to fill out the details of their change.


                    When someone makes a modification, the OnRecordCommit trigger clearly works when I ask it to "Show Custom Dialog," but I can't get it to go to a specific field for the user to fill out.  Upon OnRecordCommit, ideally I would like to get the database to:

                    a) automatically input a Revision number, starting with "1" and counting up each time a new modification is made to that record, then

                    b) proceed to activate the Timestamp field and User field, and

                    c) go to the Notes field to allow the user to input their notes about the changes they made to the record. 

                    How would I go about doing that?


                    I do have one script that does Parts b and c once you write something in the "Revision number" field, but that assumes the user will voluntarily insert a Revision number first.  Here is that script, but I still have some problems with that script, too.

                    If [/*IsEmpty (Revisions::Timestamp)*/]

                       Exit Script []


                       Go to Field [Revisions::Notes]

                    End If


                    Thanks for any advice or help you can provide.

                    • 7. Re: Documenting Changes Within Hosted Database

                      First, you need a relationshp to your related table used in your portal as a change log.

                      YourTable::__pkPrimaryKey = Revisions::_fkForeignKey

                      Your script might be similar to this:

                      Set variable [$newRev ; value: Max ( Revisions::Rev ) + 1]
                      Set variable [$ID ; value: YourTable::__pkPrimaryKey]
                      Freeze Window
                      Go to layout [Revisions (Revisions)]
                      New Record/Request
                      Set Field [Revisions::_fkForeignKey ; $ID] //links new change log record to current record in YourTable
                      Set Field [Revisions::Rev ; $NewRev ]
                      Go to layout [original layout]
                      Go to Field [Revisions::Notes]

                      Set up the Change log to sort by Rev in descending order so that the most recent revision is listed first.

                      Open up Manage | Database | Fields and select Revision from the table drop down.

                      DOuble click your user field to open field options. Click the auto-enter tab. Select "account name" as the name automatically entered into this field.

                      Do the same for your TimsStamp field, but specify 'creation time stamp' as the value to auto enter.

                      With those settings, the new Record step in your script will cause these two fields to automatically enter data--there's no need for the script to do it.

                      • 8. Re: Documenting Changes Within Hosted Database

                        Hi Phil,

                        Thanks for taking the time to post the script info.  I tried set it up like you suggested, but I couldn't get it to work.  I likely had something else going on that was preventing it from working correctly or didn't quite set it up correctly.  When I tried it, it didn't have a defined value for the __pkPrimaryKey so it never put a value in the Revision field.  What I have works okay assuming people are honest.  So, I'm just going to go with that.  If you can't trust the user to add a note when they make a modification, then I guess you can't trust anything else they put in either.  So, I'll assume we can trust the user.

                        Thanks for your help Laughing

                        • 9. Re: Documenting Changes Within Hosted Database

                          __pkPrimaryKey should be an auto-entered serial numer entered "on creation".

                          You might consider that people may intend to be "trustworthy" but can be forgetfull and/or neglectful--something your system can try to combat by reminding them to add the change log info. Wink

                          • 10. Re: Documenting Changes Within Hosted Database

                            Okay, so maybe I should ask you more about the __pkPrimaryKey and the _fkForeignKey.  Are these fields that I need to create myself?  So, create the __pkPrimaryKey field in "Your Table" and create the _fkForeignKey field in "Revisions" table?  I did that, but I had assigned them as just numbers, but had not set them as auto-entered serial numbers entered "on creation."  So, when doing that, do I just leave "next value" at 1 and "increment by" 1 for both of these fields?  Or, do I need to set the _fkForeignKey field differently since it is dependent on the value of the __pkPrimaryKey?  Thanks for not letting me give up... yet.

                            • 11. Re: Documenting Changes Within Hosted Database

                              It's a standard setup for any one to many relationship such as it typical for a portal. (One record on your layout matches to many records in the portal's table.)

                              Not knowing what fields exist in your tables already, I used these as placholders for what you should already have in place.

                              A primary key field uniquely identifies each record in the field where it is defined. Ideally, it should be internally generated and never, ever changed. A field defined as an auto-entered serial nubmer is almost always the best way to do that in FileMaker. The foreign key field should not be an auto-entered serial number. It gets its value from the primary key field when a record is created in the portal.

                              This assumes that the relationship for your portal would be:

                              YourTable::__pkPrimaryKey = ChangeLog::_fkForeignKey

                              Note that usually, you put the name of your parent table in place of "primary key" and "foreign key" so you if your table is named "specifications", it would look like this:

                              Specifications::__pkSpecificationsID = ChangeLog::_fkSpecificationsID

                              But this is just a naming convention, you can name these fields however makes sense to you.