1 2 Previous Next 27 Replies Latest reply on Jun 11, 2014 2:54 PM by ibrahim_bittar

    Why a System Table ?


      Why should a System table be included in a solution ? What is the usefulness of such a table ? And why should it contain only one record ?

      I cannot find a good documentation source to answer those questions ?

      Can someone help out there ?



        • 1. Re: Why a System Table ?

          Hi, Jean-Marc:


          Such a 'System' table is very useful for several purposes. It can be used for storing settings, preferences, default values or graphics, such as icons and logos. Here, this data can be easily accessed via constant relationships or via global fields, depending on your need.


          With a System table, there's no need to create these fields in other tables where they don't really serve a direct purpose.


          The System table can be useful as a 'neutral territory' from which to create dialog layuts and perform other processing.


          It never hurts to have one.

          • 2. Re: Why a System Table ?

            Very well Erik, but why is it preferable that it contains only one record ?

            • 3. Re: Why a System Table ?

              why is it preferable that it contains only one record ?


              Typically there is only one set of system settings, so a single record is able to represent them all. If your needs are more complex you may want to have settings for different groups or for different privilege sets.



              • 4. Re: Why a System Table ?

                Is there some documentation that explains how to set it up ? Although this construct has appeared quite a time ago, I have not been able to come across a comprehensive documentation source.

                • 5. Re: Why a System Table ?

                  I've never seen it "documented",  but a Google search for FileMaker Preference Table will find other posts




                  > Is there some documentation that explains how to set it up ?

                  • 6. Re: Why a System Table ?

                    I think it's a developers preference based on years of working the way FM does. There are no 'system tables' such as you might find in the SQL world. Each FM database stores things you might otherwise find in these kinds if SQL tables.


                    -- sent from my iPhone4 --

                    Beverly Voth


                    • 7. Re: Why a System Table ?
                      Stephen Huston

                      In my opinion, the main reason for having only one record is that most of the fields in that table are best set to "global" storage settings, so one value is all they can hold anyways, and that form of storage makes those fields accessible from anywhere in the system -- without requiring a relationship to other tables.


                      Another field type that I use in this table is calculations, which I generally set to a fixed text value. This is great for updating things such as Version Numbers in a solution, and these calcs can be changed even after it's on the server.


                      I generally include global fields for dialog input in this table: 3 of each kind -- text, date, number-- in case some dialog needs that many of one type.


                      I also place a series of Set Field steps in my OnFirstWindowOpen triggered script to either clear or set default values to each of the fields which users will encounter.


                      I also set Set Field steps in my OnLastWindowClose script to clear those fields by setting them to "", so the file doesn't have left-over values in those globals before I place it on the host/server.

                      • 8. Re: Why a System Table ?

                        As Stephen says, the System table is often used to contain single values in one or more fields. Yes, many of these can be globals, but for that, you don't even need one record. Some settings and data, such as graphics, may require a non-global field, thus one record. Having one record also allows some relationships to the System table that can't be accomplished otherwise. I often have a constant1 field in all tables, including the System table. That way, I can create a non-global relationship from any record back to the System. At least pre-13, this was handy for instance with icon graphics that I want to disappear in Find mode or when there are no found records.

                        • 9. Re: Why a System Table ?
                          Stephen Huston

                          I found that globals don't always behave as expected if there is no record in the table.


                          For instance, using globals to capture scripted password creation, I tried to test Length(globalfield) and found the test failed--though not consistently--on entries which were of adequate length, probably because the calc test was trying to read a field from the table but there was no record, so it found nothing to test!


                          That one took several minutes to work out, as my other global behaviors were just fine. Because there are lots of times I need to check a dialog-field entry before continuing with a script, I will stick with making sure there is a record in my table of globals.

                          • 10. Re: Why a System Table ?

                            Usually you want to make sure there is only one record in this System or Preferences table.  I make sure the opening and closing scripts verify there is one and only one record.  If there is none, then I add one and if there is more than one, I delete all the ones greater than the first one. 


                            Remember globals are remembered based on the last time opened locally by FileMaker Pro.  They will always open set to that value in the future, which can be annoying to a server deployment.  But once a session is running, the given value for a global stored field is unique to that session, can be changed, and is not shared by anyone else.  Just remember those values in the global field will be forgotten as soon as you log off and reset to the last value opened locally by FileMaker Pro.  On a server, it means they will always be reset to the same value for everyone.  I usually try to erase all global values locally before uploading them to the server to avoid any confusion when connecting on the server. 

                            • 11. Re: Why a System Table ?
                              Stephen Huston

                              That's why I mentioned my onLastWindowClose script in my first post to this thread. I prefer to start with those values empty and set them on a session-by-session basis rather than relying on whatever was in them by default. Placing the onLastWindowClose script to clear them in place before hosting it takes care of it for me.

                              • 12. Re: Why a System Table ?

                                One option I like to use is to put "real" (i.e., non-global) fields in the system table that parallel the global fields. In this way, you can change the values for the globals and have the OnFirstWindowOpen script set the global values based on the values in the "real" fields. As a plus, you can change the values in the globals just by changing the values in their parallel "real" fields in the system tables (i.e., no need to shut down the file). Particularly useful if you have global containers (such as a company logo or the like). And, for non-container globals, sometimes it's nice to be able to see the values in a table without needing to go to the script where they're set and look at (or modify) that.


                                Just a thought.



                                • 13. Re: Why a System Table ?



                                  I also like the first table created to be without any fields, a "blank" default layout is created. Here I put main menu type navigation, if desired. This would be the layout that would be opened by file references so no sorts, triggers, etc. when a file is opened by a relationship.

                                  • 14. Re: Why a System Table ?
                                    Stephen Huston

                                    Hi Mike,


                                    I also use global calcs pointed at a real stored single-record fields, usually in the same table for convenience and ease of reference. As you note, the flexibility of having both a global and being able to change it's default value while it's being served can be a huge benefit.

                                    1 2 Previous Next