3 Replies Latest reply on May 16, 2011 9:13 AM by aammondd

    Storing Database "State"

    craig_gee

      Title

      Storing Database "State"

      Post

      Hi There,

      My database is hosted with Filemaker Pro 11 client on a small local network with 8 regular users.

      The databse stores details on around 500 members of a martial arts club and what classes they attend. Each year around 120 kids from our kids classes need moving up to the class above. This is a tricky process and invloves looking at current numbers and what spaces they can move into etc. etc. but I have sorted that interface out and it works beautifully.

      My problem is that the process only happens once a year for about 4 weeks. When it's not currently happening I want the database to take you to a set-up screen when you click on "Moving Up" that explains that it is not currently running and that you need to set it up. When it is running I want it to take you straight to the interface for moving people between classes setting their new class to move in to as well as warning you if you manually move people in and out of classes when they are part of the process.

      As there are multiple users I need to store whether the process is currently running so these things can happen and it needs to be accurate otherwise mass confusion could be caused with so many people switching classes. It needs to remember this "state" when the database is re-opened when staff come into work but also updated as the process is finished so staff don't need to re-open the database for it to take effect.

      I was using a global variable for this at first but then soon realised that global variables are indivual to each user and therefore useless. I have now created a table with the sole purpose of storing this yes or no state and test it by using a script to

      1. Go to the table

      2. Show all records

      3. Go to the first record

      4. Read the field that stores the state.

      I don't allow users to see the tool bar and have used custom menus to remove the ability to add/delete records so they shouldn't be able to add any records and mess up this process but I still use steps 2 and 3 to be extra sure. The real pain is that these steps need to occur everytime I want to test the state of the database which is proving to be quite a few places!

      Is this a ridiculous solution to an easy problem or is this the best I'm going to get. I came up with this myself as I can't find anyone else doing the same thing but I feel I might have created a monster just waiting to screw me over.

      If anyone has come across this need before and has any help/advice it would be greatly appreciated.

      Thanks in advance,

      Craig

        • 1. Re: Storing Database "State"
          RestaurantCharlie

          No it is not crazy, and the way you solved it works. Sure there is probably always a better way out there to solve every one of our problems.

          I have a similar issue, and this is what I did:

          I have a table called MAIN, it only has 1 record. No one can add or delete records. This table is only visited by the start up script, where users can't abort it and end up on that table. It stores GlobalFields such as User Name, User ID, User Authorized Departments, Privileges, etc...

          I also have a hosted database so, if you want to set up a global field in a hosted enviornment you have to stop hosting the file, change the field and re open it. Now that is a monster... So with that 1 table, I have a field called ONE, and it has a one in it. Pretty much all my tables have a global field with a one in it to relate to other tables using fields that filter (calc fields with boolean questions, so if the result is 1, I want to see it and can relate to it from any table). You could implement this and check from your scripts to see if the STATE field in your main table is set to OPEN or Closed. OR you can have your start up script navigate to that field, check the value and store it in a global variable $$state, and you can check against that from your scripts to see if users can access that part of the solution or not.

          Hope this helps...

          • 2. Re: Storing Database "State"
            philmodjunk

            You no longer need the "one" field to link to other tables in this fashion.

            You can use a relationship such as this:

            Table::anyfield X SystemSettings::anyfield (after creating the relationship, you can delete the two fields used and the relationship remains valid.)

            And any record in Table will link to all records in system settings. To create such a relationship, you double click the relationship line and change the default = operator to the cartesian join operator: X.

            And you can avoid the need to create large numbers of such relationships if you use a start up script that updates a global field or a global variable with the stored "System State" field from SystemSettings each time the file is opened. Once you have the value in a global field, that global field or variable is accessible from any layout and script in your file.

            • 3. Re: Storing Database "State"
              aammondd

              One thing I do with my Global Records (I use tables which only contain global fields) rather than go to first record is I have a hidden layout and I Go there and delete all records and create a new one move to a layout with my system setting then populate the global record with any system wide settings before going on to my main layout.  Ive used the cartesian join of the system settings with my "security tables " joined to my global records to apply various non-Filemaker security settings.