1 2 Previous Next 19 Replies Latest reply on Sep 5, 2016 11:13 AM by BruceRobertson

    Global fields across multiple databases and priviledge sets

    gobbles

      Ok, this may be a silly question but filemaker is causing me much confusion and pain today.

       

      How to I make global variables in a global table that do not get deleted when I log out or close the database.

       

      I have done this in the past and now for whatever reason my changes are not sticking.

       

      I have some values I need to use across some scripts that need to only ever change when edited by users. They are to be used across a number of scripts. I DO NOT want to create a table and relationship for them. They are just a set of constants.

        • 1. Re: Global fields across multiple databases and priviledge sets
          Johan Hedman

          Global table should have 1 record only. Each field in your Global Table have to have Variable as a choice in Field Settings. Then each field becomes unique variable for each user.

          • 2. Re: Global fields across multiple databases and priviledge sets
            alangodfrey

            The easiest way is to create a single-record 'Preferences' table and store the values in there.  They must not be global fields.

            If it worked for you in other circumstances then you were probably setting the values before closing the database - then they will be stored for all users, but when a user changes the value it will only be changes for them: global values are user-session specific.

            If you only want to use them to transfer data between scripts then you can 'send' the data as a Script Parameter, for example.

            What is your objection to creating another table?  I find the Preferences table (for holding fields such as this, and other data) one of the first I create in every database.

            • 3. Re: Global fields across multiple databases and priviledge sets
              Mike_Mitchell

              gobbles wrote:


              I have done this in the past and now for whatever reason my changes are not sticking.

               

              If I had to guess, I'd say you're used to using the database locally, and now you're using it on Server, right? A global field will retain the value stored in it when the file was last closed locally (i.e., not hosted). It'll therefore revert to whatever value it had when it was hosted every time you open it on the server.

               

              To make the values "stick", you'll need to create a parallel field with normal storage and set the global via a script when you first start up the database (use your OnFirstWindowOpen trigger).

               

              HTH

               

              Mike

              • 4. Re: Global fields across multiple databases and priviledge sets
                gobbles

                Ok so I have been trying your method but I am a little confused how to access the Preferences table.

                 

                Say I calculate which Preferences field I want.

                I then try to access it with something like GetNthRecord and it doesn't work. It just returns '?' undefined.

                • 5. Re: Global fields across multiple databases and priviledge sets
                  BruceRobertson

                  You missed an earlier point. The preferences table has ONE record. There is no Nth record to get.

                  There might be fields for let's say, default printer name; company address; default tax rate; etc.

                  • 6. Re: Global fields across multiple databases and priviledge sets
                    gobbles

                    Not sure what your comment means. Yes, my preferences table has one record in it, but I am not aware of a function in filemaker that gets the one-and-only record from a table. It is of course also possible at a later date that a user with enough privileges adds a record - for whatever reason - to that table and such a function would break. Perhaps there is a GetFirstRecord function? I couldn't find it.

                     

                    In the end what I had to do to make it work was create an 'X' relationship between my preferences table (and create a pointless PK to do it) and any tables on any layouts where I wan to insert the text from the preferences field. Then I use GetNthRecord(preferences::field;1) to get the value I want.

                     

                    It seems to be working but what I would rather have done is simply make an arbitrary GetNthRecord call to an unrelated preferences table. Am I correct in thinking Filemaker does not allow such a thing?

                    • 7. Re: Global fields across multiple databases and priviledge sets
                      gobbles

                      Mike_Mitchell wrote:

                       

                      If I had to guess, I'd say you're used to using the database locally, and now you're using it on Server, right?

                      Nope. Always on a server. With 15-20 users with various privilege sets.

                       

                      What I do notice is that mostly in the past this has been container elements for things like logos and images. It has worked every time. In fact, in the table I was having trouble with in this post there is a 'logo' field holding a single global storage image which is not getting removed between logins by anyone.

                      • 8. Re: Global fields across multiple databases and priviledge sets
                        BruceRobertson

                        If you're going to set up a relationship to the single record table, such as by X relation or constant relation, there is no point in getNth. There is only one record. Not sure why you imagine getNth being of some use here but that will never be the case and N will always be 1.

                        Of course you can also use executeSQL to read the pref table and set global field(s) with the result.

                        Thus not requiring a relationship.

                        Or; depending on your needs, you can use ExecuteSQL to read the pref table values and populate global variables.

                         

                        Another way of using singe record prefs table is to set up a global field in that pref table for each standard field.

                        Then use the startup script to nav to a layout based on the pref table and set each global field to the content of the stored field.

                        From that point on, the global fields will be available, without relationship.

                         

                        Don't count on that global container mysteriously retaining its value.

                         

                        "Had to create a pointless PK"

                        No, you didn't.

                        An X relationship does not require a PK or FK.

                        That's the point. It's an X relation.

                        • 9. Re: Global fields across multiple databases and priviledge sets
                          gobbles

                          BruceRobertson wrote:

                           

                          If you're going to set up a relationship to the single record table, such as by X relation or constant relation, there is no point in getNth. There is only one record. Not sure why you imagine getNth being of some use here but that will never be the case and N will always be 1.

                          It is currently working. Is there any reason to change it? Is there a negative to using GetNthRecord? I repeat my problem of future users adding records, if they do this, and I do not specify a record number, how would I get the correct record?

                           

                          BruceRobertson wrote:

                          Or; depending on your needs, you can use ExecuteSQL to read the pref table values and populate global variables.    

                          I should have mentioned this earlier but I only have access to Filemaker Pro and version 11 at that (I do not have a choice in this, it is what I have been told to use and no upgrade is likely any time soon). As far as I am aware I do not have access to ExecuteSQL.

                           

                          BruceRobertson wrote:

                           

                          Another way of using singe record prefs table is to set up a global field in that pref table for each standard field.

                          Then use the startup script to nav to a layout based on the pref table and set each global field to the content of the stored field.

                          From that point on, the global fields will be available, without relationship.    

                          It seems a lot of potentially bug creating scripting when all I want is a limited set of fields that can be referred to by a handful of scripts. I am curious however if this is considered best practice for such fields in filemaker pro? I will do what is most stable and reliable to be honest. I do not work at this location often so changes to the Filemaker DBs they use have to be robust enough not to fall over down the track.

                           

                          BruceRobertson wrote:

                           

                          Don't count on that global container mysteriously retaining its value.

                          I will review all such fields next time I get a chance then. It has been working for several years but if the best practice and opinion of the filemaker pro community is that such practices are frowned upon then I will try to get them fixed.

                           

                          BruceRobertson wrote:

                           

                          "Had to create a pointless PK"

                          No, you didn't.

                          An X relationship does not require a PK or FK.

                          That's the point. It's an X relation.

                           

                          Perhaps it was my antiquated version of filemaker pro but it insisted on a field for the relationship. Additional, the 4-5 guides to X-relationships and the Filemaker Help section I looked up on them all said X should be a relationship on the primary keys. The Databases management UI flat out did not allow me to create the relationship without those keys so I am not sure how one is to make them without it.

                           

                          I called it "pointless" because it really is. From a DB schema point of view it is a meaningless relationship and it is there only to allow the exchange of data in the presentation layer. It should be possible to make an arbitrary query into any table from any layout. It is somewhat perplexing to me that I need to generate a database relationship to be able to do this. But again, Filemaker is not my main area of expertise and I defer to the community, which is why I asked my questions here.

                          • 10. Re: Global fields across multiple databases and priviledge sets
                            gobbles

                            alangodfrey wrote:

                             

                            The easiest way is to create a single-record 'Preferences' table and store the values in there. They must not be global fields.

                            If it worked for you in other circumstances then you were probably setting the values before closing the database - then they will be stored for all users, but when a user changes the value it will only be changes for them: global values are user-session specific.

                            If you only want to use them to transfer data between scripts then you can 'send' the data as a Script Parameter, for example.

                            What is your objection to creating another table? I find the Preferences table (for holding fields such as this, and other data) one of the first I create in every database.

                            Your suggestion was very helpfully and got me on the right track to a working solution thank you.

                             

                            I didn't have a particular objection to something like a preferences table. I just didn't want to - as seems to often be the case when I work with Filemaker Pro - to start creating additional DB relationships just for the sake of layout concerns. It seems like a recipe for issue down the track if the schema changes or a table is deleted. As it happens I did have to create an 'X' relationship to make the preference stable available to my layouts which i would have preferred to avoid if it had been possible.

                            • 11. Re: Global fields across multiple databases and priviledge sets
                              BruceRobertson

                              To create an X relation you DO have to create a relationship.

                              And a relationship definition does require at least one set of fields to be selected.

                              But if you  have truly specified X for the relationship operator, you can select ANY two fields; even global fields.

                              Even in FileMaker 11.

                              You do not need a primary key in the pref table.

                               

                              You want your startup script to control the preference table and positively make sure that there is only one record in it; and delete any others if they are somehow accidentally created.

                               

                              Not sure what you mean by future users creating new pref table records.

                              This is entirely missing the point of a single record pref table.

                               

                              The point of a single record Pref table is to be positively sure that you don't have to wander around finding the pref records and wonder why somebody put the default tax rate in pref record 37. Pref record 37 should not exist.

                               

                              You are mixing up your message on the pref table relationships. It is the primary key in the pref table which is pointless.

                               

                              If you want to eliminate the relationships, then use the matching global field method and startup script.

                              Yes, a pref table DOES require you to exercise stringent control over how it gets populated and maintained.

                              GetNth is just pointless in this case.

                              Even if you have somehow created erroneous pref records, there still is no point to the GetNth method. You want to always point to the FIRST pref record. That is what a standard relationship does. Period. (And hopefully; because you are controlling this; it is the ONLY pref record).

                              • 12. Re: Global fields across multiple databases and priviledge sets
                                alangodfrey

                                gobbles - you are suggesting Filemaker is being confusing and causing you to apparently needlessly create tables, fields, and relationships.  There is a difference between a 'method that doesn't work', and a 'method that isn't being worked', and in this case you are not working the method(s) as suggested.  There are a few arguments in defence of the way FM does what it does:

                                 

                                - your previous method doesn't work in your current conditions, so that is a reason to see what went wrong and to make it more robust

                                - the creation of a Preferences table (as specified) is the work of minutes, adds no overhead, is robust and reliable, is clear to understand, and is very flexible.

                                - however you populate the data into it is open to debate, but a big priority for me is: 'Is my method robust (ie: not liable to break if I change something down the line)?', and 'Will I (or even worse - someone else) be able to easily understand how this solution works in 3 years time?'.  The use of a single-record Preferences Table as described should be immediately apparent and its function clear to a future competent developer.

                                - just because something 'works for now' is not a good reason not to a) understand why and how it now works, b) understand any exposures to the method, and c) follow best 9or at least 'good') practice if possible.  Anything else brings you to a sudden halt some ways down the road, and usually at the most inopportune moment (as decreed by Murphy, I believe; see OP).

                                 

                                To repeat a couple of key differences:

                                - you could create a global field, in any table, and it will always be available, to every user, from any layout, even with no relationship back to the table the field is defined in.

                                - however, by being a global field it has characteristics that are either a strength or a weakness - but usually a strength as long as the designer knows how they work.  They are session-specific, ie: only the current user can see the global data that they set in their current session.  Every concurrent user could set that same global field to be a different value, just for them to use.  That is a great strength when used correctly.

                                - there are exceptions to that when moving to a shared solution (as Bruce has pointed out) and these characteristics may well be why all of your users could see the logos all the time, in every session.

                                - if you make a field a 'normal' field it will be available to all users consistently, but it requires a relationship from the table they are using, back to the table that field is in.  That can be by a Cartesian ('X') relationship between any fields in the two tables, and in this case is perfectly fitted to what you want to achieve.

                                 

                                HTH.

                                1 of 1 people found this helpful
                                • 13. Re: Global fields across multiple databases and priviledge sets
                                  Mike_Mitchell

                                  That means it's pulling the last value it had when the database was closed locally (i.e., not on Server). Which is what global fields will do.

                                   

                                  Most people who implement this sort of solution will create scripts to clear the globals when the database is closed, and then reset them OnFirstWindowOpen. This prevents a value from sticking when you have it shut down on the server.

                                  • 14. Re: Global fields across multiple databases and priviledge sets
                                    gobbles

                                    alangodfrey wrote:

                                     

                                    gobbles - you are suggesting Filemaker is being confusing and causing you to apparently needlessly create tables, fields, and relationships. There is a difference between a 'method that doesn't work', and a 'method that isn't being worked', and in this case you are not working the method(s) as suggested. There are a few arguments in defence of the way FM does what it does:

                                     

                                    - your previous method doesn't work in your current conditions, so that is a reason to see what went wrong and to make it more robust

                                    - the creation of a Preferences table (as specified) is the work of minutes, adds no overhead, is robust and reliable, is clear to understand, and is very flexible.

                                    To be clear, I have - at least as much as I understand it - adopted a preferences table method as you outlined earlier. My method of accessing the contents of that preferences table - GetNthRecord - seems to be viewed unfavorably though I am not sure why. Is there something fundamentally wrong with using GetNthRecord make sure I only ever get the first record in the preferences table? What should I be doing? I am curious which is better from a performance, correctness and robustness point of view. Is that a function that is like to break down the track? Is the intent of the script it is in going to confuse whomever has to modify it down the track?

                                     

                                    Non-tech savy users adding preference records is a real issue. I am obliged to give admin access and I know from experience that from time to time people in the office add this or that layout element or field. I am only there irregularly so have to keep it as simple and bulletproof as possible.

                                     

                                    I have worn my frustrations with filemaker on my sleeve somewhat and I apologize.

                                    1 2 Previous Next