4 Replies Latest reply on Jul 7, 2012 11:46 PM by mickwilli

    Quickly and Silently Creating and Auto Entering Records in Scripts

    mickwilli

      Title

      Quickly and Silently Creating and Auto Entering Records in Scripts

      Post

      Hi all,

      One of my new projects is to re-write our companies main database that effectivly keeps the company functioning. There are a lot of improvements and aditional features that I'm working on implementing into this re-write.

      One of them is the ability to automatically log actions in the database, such as user logins / logouts, creation of new records in particualr datbases and so on.

      What I want to know is, is there a way to silently create a new record in an unrelated table, enter some data into it by way of a script and or calculation and then carry on, with the user totally unaware of what just happened? I have seen the "Execute SQL" script step and had hopes that this might allow me the ability to create a new record and write data to it with one quick script step, but it appears this is only intended for external SQL data sources etc.

      I'm obviously aware of the usual, new window, feeze/hide window, go to layout, create new record, set field XYZ to XYZ, commit record, close window, way of accompiishing the above, but it's obviously a long winded bit of code for the sake of quicky writing a log entry to a seprate table.

      Any ideas or comments would be greatly appreciated.

        • 1. Re: Quickly and Silently Creating and Auto Entering Records in Scripts
          philmodjunk

          Execute SQL can be used with any table in your file. See these two threads for two uses of it:

          FMP 12 Tip: Summary Recaps (Portal Subtotals)

          A new way to count unique values in FileMaker 12

          But Execute SQL extracts data from your tables for display purposes. It only supports the Select statement so you need another method.

          Here's a scample script that logs each time a user opens the file. You should be able to adapt it to a number of similar situations:

          Freeze Window
          Go to Layout [UserLog ( UserLog ) ]
          New Record/Request
          Set Field [UserLog::AccountName ; Get ( AccountName ) ]
          Go To Layout [//Specify the layout you want to be the first layout that the user sees when they open the file here]

          Use File Options to set this script to run anytime the file is opened. I've used a set field step purely to show where you might use Set field to update such a newly created record. Fields in such a record can actually auto-enter a number of useful bits of information such as the date, time and the account name such that just creating the new record in this table logs all that data automatically.

          We use something similar to log key data in our invoicing system so that info about the employees and the invoice are secretly logged at key points in the process of creating and printing an invoice. This is to help make employee fraud a bit more difficult in our case.

          • 2. Re: Quickly and Silently Creating and Auto Entering Records in Scripts
            mickwilli

            Hi Phil,

            That's about what I thought. I gather that the Execute SQL script step was updated in Filemaker 12 to allow you to query the Filemaker database but obviously the Select command isn't going to do anything useful in this regard.

            At least I've confirmed my assumption.

            • 3. Re: Quickly and Silently Creating and Auto Entering Records in Scripts
              philmodjunk

              Execute SQL was newly added in FMP 12. It did not exist in older versions.

              But you do have a workable alternative that will do the job as I've illustrated in my previous post. The user does not see any visible change when a script patterned after this example is performed.

              • 4. Re: Quickly and Silently Creating and Auto Entering Records in Scripts
                mickwilli

                Yes, that will work fine.

                I was just looking for something that would make for a wee bit of tidier code. With that script I require 7 script steps and 1 otherwise pointless layout to do what i could do with one Insert SQL statement.

                Thanks for your input all the same.