1 2 Previous Next 24 Replies Latest reply on Mar 10, 2017 7:12 AM by Licorice

    "Living" Database


      One thing that is important for the database here is that we are able to update it at any time. Once it goes live, we wish to have the capability to edit it, changing or adding various sections. We also have very little time the database can be offline, it is understood if it is needed to change the database but it would be highly preferred if we never had to take the old system offline. Editing the database also will happen separately, it must be able to be edited and still be up to date.


      So the question is, what is the best method to go about this? Currently I have a simple Import Records script that Imports every table. The drawback is that any new tables need to be added manually and it seems to take half a minute to a minute with just some false data. As that doesn't go to each layout, I also needed to add a second loop just to include a Show All so all old records don't need to be shown by the user. I also have a Delete script that I run before the Import that gets rid of any test data. The import is pulling from a file that I would pull off the servers when I am ready to switch databases.


      As have might have been inferred, this is not really an optimal solution. We have short periods where the database needs to be offline and it may take time to import and then change whatever needs changing to the old records. So the question is how would you prevent this issue... if it is indeed preventable? Is there a better method? Reliability is also top priority here, one of the reasons I have an Import Records script is because I have heard that importing from another file tends to be less stable.

        • 1. Re: "Living" Database

          If you use Data Separation, where your tables are kept in one file and your layouts, scripts etc. are kept in the other file. You can deploy an updated user interface file without having to import records.


          Changes to your hosted files that do not involve Manage | Database, can be made while the file is in use if you are careful. Such changes can in many cases be copy/pasted from an offline copy where you have tested the change to be sure that it will work.


          And if you want to make data level changes, you can:

          A) close the file on the server

          B) open it with FileMaker Advanced

          C) make the needed changes--often by pasting from a development copy

          D) re-open file on server.


          This is often much faster than importing records.

          1 of 1 people found this helpful
          • 2. Re: "Living" Database

            So mostly just keep a test version on hand to test the changes, but copy/paste content over to the main system when you are confident it will function? That does make more sense, make the changes to the file with the updated records, not a totally new file that will need to pull all data from an old one. Plus it makes sense that it wouldn't break if you know how it performs on your test and do it in the exact same fashion.


            I have heard about Data Separation, but I also heard that it was often unstable and that it was a better design choice to have everything in a single database. Have I been misinformed? If I have, how would you go about Data Separation when the database is already built up? If the Data Separation has everything but the real data, since I only have test data at the moment, am I fine or are they referring to fields in general?

            • 3. Re: "Living" Database

              There is nothing to data separation that makes it more unstable than a unified file would be to my knowledge. Multiple file solutions--either to divide it into modules, do data separation, or both is a very common approach. I support a DB that has over 100 files to it, for example.


              And please note that I said "if you are careful". A change can catch a user "between chairs" or by surprise and thus cause a problem for them. And changes to table/field definitions should not be made to the live DB. All users/scripts/schedules will be locked out of a table while changes are committed to it causing possibly catastrophic issues with your data if you lock a table right in the middle of a batch update or something. Plus, there is an increased chance of damaging the file if you get a "glitch" at the wrong moment.


              Even after careful testing problems can arise after a change if you missed some key detail during testing so it's also important to document every change and have a means for rolling back the change quickly should the need arise.


              In addition to making frequent and incremental backups, I disable script changes that I need to change or remove and put the new steps just above or below. I then put a comment with name and date above and below this block of new and disabled scripting. If a problem is identified, I can disabled the new, enabled the old and thus revert the script to it's former design. For extensive changes, I duplicate the script and put an "expiration date" into the script name of the duplicate. If all else fails, I can replace all the lines of the working copy of the script with all the lines copied from this duplicate to revert the script in case of emergency.


              To cut down on "clutter", I make it a general rule of thumb that if I open a script in the scripts works space and find such comments and disabled scripting that is more than a month old, to then delete the comments and disabled code. (I keep an entry documenting the change in a comments header block however.)

              1 of 1 people found this helpful
              • 4. Re: "Living" Database

                Hmm, that is promising indeed. It is quite a confusing world for me, I am trying to wrap my head around how a layout and a field can exist in totally different locations. Reading up on data separation definitely seems like that is the way to go, now I just need to find a way to translate it so I can understand what is needed to be done.


                Indeed, the worst thing that can happen is that the entire file becomes corrupted due to the conflict between the editor and user. Definitely intimidating, but it is just a matter of being wary and knowing what you are doing. Changes requiring data to be altered should be taken offline just to ensure integrity remains.

                Indeed, I was thinking along the same lines. I have a changelog script included in my database that I plan to document with comments describing every change once the database file goes live.


                Commenting out changes definitely sounds like a useful trick to do things, I am rather inexperienced with coding but had heard that was a popular method there. It sounds like, the changelog is useful for telling what was done, but it is also better to retain the old style in some way to ensure functionality is retained if you need to revert.


                Glad you mentioned to clear the "clutter", one possible concern I had was the fact that the commented out sections would start to get overwhelming if left unattended. A month sounds like a pretty good time to make sure something doesn't falter on the user, I assume in my case the comments header would be the changelog I am referring to.

                • 5. Re: "Living" Database

                  One odd question about the data separation model, but it sounds like the user needs to have access to both files. If this is the case, how does one ensure that the user enters data in the correct file?

                  • 6. Re: "Living" Database

                    Changes requiring data to be altered should be taken offline just to ensure integrity remains.

                    Not quite. What I meant was data LEVEL changes. That is changes made to a table of field definition should always be done "off line".

                    Reading up on data separation definitely seems like that is the way to go, now I just need to find a way to translate it so I can understand what is needed to be done.


                    A quick outline of how to "split" an existing file:

                    1. Make a second copy of the file from your OS, by downloading from the server or by using "Save a copy as" from the File Menu. Change the file names so that it is clear which copy will become the UI (User Interface) file and which will be the data copy. (The "connection" between the two copies is based on file name and relative location. So rename the files first and keep them in the same folder when making the following changes. When you put them back up on the server, keep them in the same folder on the server also.)
                    2. Have both files open
                    3. In the file that will become the interface file, go to manage | Database | Relationships and double click one of the relationship's Table Occurrence "Boxes". In the data source drop down , select "Add FileMaker Data Source". Use the dialog box that opens to find and select the data copy of the file. Then select the table from this copy that corresponds to the table originally specified for that table occurrence. The name of the table occurrence will change to italic style to show that it references data from an external source. Repeat this for every table occurrence in your relationship chart. In cases where you have more than one occurrence of the same table, you only need select "add FileMaker Data Source" once. After that, you can just select the external data source table off the list that appears in this drop down.


                    That's the minimal changes needed to split your solution into UI and data copies. You do have to pay attention to Manage | Security and any scripts that "run with full access privileges" in order to modify data as they will no longer be able to do so  if perfomed from the UI file and you'll have to set up a different way to modify the data if that's the case.


                    You can delete all but one "utility layout" for each table from the data copy as well as nearly all scripts and value lists. You can also look at the relationship graph in each file and remove table occurrences/relationships from the data copy if the relationship is only used in the interface copy (such as to put a portal on a layout). The only relationships that you need keep in the data copy are those needed in order for a calculation field or auto-entered calculation to work.

                    1 of 1 people found this helpful
                    • 7. Re: "Living" Database

                      In the "Share with FileMaker Clients" sub menu in Sharing, there's an option to hide a selected file from the launch center. You can use this option to keep the data copy from being visible when the users go to connect to your solution.

                      1 of 1 people found this helpful
                      • 8. Re: "Living" Database

                        I believe I was referring to field definition changes, I just am pretty good at stumbling over terminology


                        Hmm, I was starting to perform something similar to what you described, but I made the data file into an External Data Source so I could simply reference the same file for each occurrence. I am curious why you specifically stated to manually add the data source to each table. Is there a negative detractor by using it as an External Data Source in comparison to the method supplied? I assumed it was a simpler method to do what you described.


                        So in regards to security I am assuming you simply need to target the Data Fields in question as well to have them function?


                        I think a big reason why I was confused was that I was thinking about it backwards, I was assuming the user would be going to the Data file and entering the data and somehow the new layout would be drawn from the layout file. Now, if I am correct, the users would use the Layout file but the relationship means all data entered is going to the other file. Makes more sense from this direction for sure!


                        Interesting... that makes a bit more sense and enlightens me a bit. I had a weird assumption that users would just be connecting through a remote connection to access the server, which in hindsight makes a lot less sense.


                        I appreciate the explanation and detailed info you have given me. It definitely gives me a bit more confidence going forward to know I have a more reliable game plan for maintaining the system in the future

                        • 9. Re: "Living" Database

                          Setting up the external data source first will work just fine. I described it differently as it can be less confusing to a new user as you work with one less dialog in windows and is easier to describe when writing up the steps, but the results are identical.

                          So in regards to security I am assuming you simply need to target the Data Fields in question as well to have them function?

                          I don't know what you mean by that. Initially, both files have identical sets of accounts and privilege sets. When a user uses their credentials to open the UI file, the references to the data file will take the credentials used to open the first file and will open the second file in the background without asking the user to enter account name and password. For any new users, you'd need to add identical account names and passwords to both files to maintain this behavior. (This is one reason why we use external authentication as it makes this task easier to manage).

                          I had a weird assumption that users would just be connecting through a remote connection to access the server

                          Again, not sure what you mean. In a hosted solution like this, both files are normally hosted from the server and accessed just as your current file is. The only difference is that you hide the data file from view to minimize possible confusion. It IS possible to put local copies of an interface file on every client machine, but this becomes a major support headache best avoided unless there is some overpowering reason to do so.

                          1 of 1 people found this helpful
                          • 10. Re: "Living" Database

                            That is good to hear. You are quite correct, a new user might be quite confused about an External Data Source as it requires navigating yet another area. I guess I am lucky to be aware of this area and how to use it .


                            I was asking if, to make security work, I would need to reference the new data fields in regards to permissions; instead it sounds like you just have it being set up identical on both. Interesting. It should be mentioned that security is one part of my database that hasn't really been set up yet as the end user needs to plan out what permissions should be granted and to whom. External authentication sounds like an interesting route, especially if it would be easier to maintain and possibly allow for more control. I am assuming when you mean that you mean there is another database file that manages the security?


                            When I normally access my file, I go to where it is located and open it up. Locally this is done just on my computer, while on the sever (which has mostly been done for testing purposes, we have yet to implement it live) you simply need to use the Remote Desktop application on Windows to access the server and navigate to the file. I was saying I had assumed the method would be similar for others to access, which I say is weird because I realize how nonsensical it would be to allow everyone access to the server computer to open a Filemaker file.

                            • 11. Re: "Living" Database

                              Multiple users directly accessing a FileMaker file located in a shared directory can damage the file. Do not do that under any circumstances. You would use FileMaker Server or FileMaker Cloud to host your file and then your clients would use FileMaker and the Open Remote option inside that application to find and open the hosted file. (Or you publish to the web and then they use a web browser.)


                              FileMaker Pro can also host the file, but the connection is not encrypted and only a very small number of users can connect at the same time.


                              With regards to security, What you can or cannot do to data in the solution will be controlled by the access privileges specified in the data file. You need matching accounts and passwords in both files, but the privileges do not need to match. They will when you first split the file as they started as two identical copies of the same file, but this need not stay this way.


                              External Authentication is a subject that you will need to research. It's particularly useful if you have a large number of users or a large number of files. (we have both). A data base file is NOT used for this. You use other software such as Active Directory to set up your security to your computer systems as a whole and then set up one account for each Active Directory group in your file.

                              1 of 1 people found this helpful
                              • 12. Re: "Living" Database

                                Thanks for the information, that is definitely something to be aware of and to be sure to prevent before it becomes an issue. I have Filemaker Server to host the file, I guess my understanding was just off .


                                So you need a username/password for reference for the Layout file, but the Data file is the one that actually controls the permissions. Makes sense. Speaking of data, I am a bit curious about the process of adding information now that the files are split. Say I want to add a new table, do I create it in one of the two databases, duplicate it to the other, and then reference the Layout table back to the identical one in the Data file?


                                Hmm, it definitely sounds like another can of worms I might want to consider. I will definitely do more research into it. We are not relatively large at the moment, but we plan on expanding greatly and that is definitely one of our major goals in regards to Filemaker. I am appreciative you could clarify the logic of the Seperation Model when the information online seemed to make it more confusing.

                                • 13. Re: "Living" Database

                                  Say I want to add a new table, do I create it in one of the two databases, duplicate it to the other, and then reference the Layout table back to the identical one in the Data file?

                                  You could, but there's an easier way. After adding the new table to data, open Manage | Database | Relationships in the UI file. If you click the far bottom left button, you get a new table occurrence and the same dialog that I described earlier pops up for you to select your new table as the data source table for this table occurrence.

                                  1 of 1 people found this helpful
                                  • 14. Re: "Living" Database

                                    Interesting, but that means the table doesn't exist in the UI file, correct? Say I wanted to use this in a script, on my layout, or in any general way that is only seen by the UI file. Would the reference be enough to make everything work properly? If so, I am assuming it is possible to delete all tables from the layout? Or are the tables automatically created as references?

                                    1 2 Previous Next