6 Replies Latest reply on May 28, 2012 7:55 AM by beverly

    Programming workflow with privileges and script triggers

    jmc01

      I'm new to Filemaker - about to create my first application using Filemaker Pro Advanced 11. However, I've been using MSAccess since the first version came out (and other programs/languages as well).

       

      I'm designing a database application that will have workflow in it. All users will have rights to view/print all layouts, but only certain users will be able to edit certain fields. For example, "Inputters" and "Managers" will have full access, however users from accounting will only be able to edit the accounting fields (invoice # etc.) on a Job Card layout. Another example, only "Planners" can edit the planning related fields and they are the only ones that will be able to create a Job Card record. From poking around in security, I think this can all be accomplished with privileges.

       

      However, there are certain workflow criteria that I don't think can be accomplished with priviledges alone and I think will also need scripts. For example:

       

      1. Allowing a user to delete a customer record only if there is no historical quotes or jobs associated with the customer.

      2. Allowing a user to delete a Job Request only if it has not been approved by management and a Job Card record has not been created for the Job Request.

      3. Allowing a user to create a Job Card only if the Job Request has been approved by management.

      etc.

       

      I looked at script triggers and saw OnRecordCommit, which I think only triggers when a new/existing record is saved. I didn't see a trigger that will execute before a record is deleted. So what is the best way to go about programming this type of functionality? Any ideas are most appreciated.

       

      Thanks!

        • 1. Re: Programming workflow with privileges and script triggers
          johan

          You should take care of the delete problem using privileges, or else some creative user will find a way to delete a record when they are not supposed to.

           

          In your first example you should set the delete custom privileges for the Customer table to "limited..." instead of "yes" or "no". In the calculation dialog you can specify any calculation, which has to evaluate to True or else the user can't delete the record.

          Your calculation could look something like this:

           

          Count ( Quotes::!id_Customer ) = 0 and Count ( Jobs::!id_Customer ) = 0

           

          You need to have a relation from the Customers table to the Quotes and Jobs tables, you may need to change table and/or field names to fit your schema.

           

          In this example, the user can only delete a record if there are zero related records in the Quotes table and in the Jobs table.

          • 2. Re: Programming workflow with privileges and script triggers
            johan

            Creating records is a whole different thing. There is no "limited..." calculation for the Create privilege, so you will have to either allow or deny record creation in general.

             

            For your third example, you should deny the user to create records in the Job Card table. The only way to create a record in that table should be handled with a script. Setting the create script to run with full access privileges allows the script to create records, even if the user normally does not have sufficient privileges. In your script you can check if all your criteria for creating the new record is met. If not, you just exit the script with a proper error message.

            1 of 1 people found this helpful
            • 3. Re: Programming workflow with privileges and script triggers
              jmc01

              Thank you so much Johan, that's exactly what I needed to know!

              • 4. Re: Programming workflow with privileges and script triggers
                beverly

                I often don't allow delete period. I will have a "marked for deletion" field that can be set.

                 

                You must set up triggers to omit these from found sets.

                 

                Then the very highest privilege set can actually delete. Perhaps there's an archive step or two in there first.

                 

                Just years of oops-I-didnt-mean-to-delete and not wanting to pull from backups.

                 

                -- sent from my iPhone4 --

                Beverly Voth

                --

                1 of 1 people found this helpful
                • 5. Re: Programming workflow with privileges and script triggers
                  jmc01

                  Thank you Beverly... I implemented the "marked for deletion" concept and took it a step further... it checks to see if a record has been "used" in the historical data and won't allow a deletion at all if it has been.  Prevents deletion anomolies.  For example, the users can't delete a supplier if they are associated with historical orders, etc.  In such a case, the Supplier can instead be marked as "inactive".  (so they won't show up in drop down list boxes, etc. anymore)  Nice tip, thank you

                  • 6. Re: Programming workflow with privileges and script triggers
                    beverly

                    Exactly! if there are related data records (of any relationship), then deleting can be hazardous. That's why the "mark-for-deletion" is important. Also Archiving often means archiving related records, too.

                         With today's ability to store lots of records, archiving is probably NOT as necessary as in the past. However, if the number of records get to a point, then searches can be slow.  It's work to archive, but necessary sometimes.

                    YMMV (your mileage may vary)!

                    Beverly