9 Replies Latest reply on Jun 5, 2010 5:44 PM by TKnTexas

    Separating database from solution???

    jjfcpa_1

      Title

      Separating database from solution???

      Post

      I know there is a separation model, but I need to go beyond this.

       

      I want to use FMPA to develop an application that can be used to maintain data for multiple clients.  It's called a service bureau application.  Think of Quickbooks.  In Quickbooks you can create an unlimited number of client databases.  This is what I'm after and it is imperative that each client database be a separate file.

       

      Trying to do this in ONE database is a non-starter for security reasons (never a good idea to co-mingle client data), for safety (if the file gets corrupt, only one will need to be replaced/restored), and for mobility (may need to use the data on a laptop in various locations).

       

      Anyone created this type of app using FMP?

        • 1. Re: Separating database from solution???
          RickWhitelaw

          Hi,

           

          I believe you can achieve the type of security you need without creating a different file for each client. Given that each of the files will need to be related anyway (for you to track the numbers, trends etc.), it would seem to me you should design the database to do what it is you or your client(s) need it to do. Filemaker, as you likely already know, doesn't "care" whether a table resides in one or another file. Security concerns are handled, by example, via permission sets. There are ways to allow a certain client or user access to only certain parts of your database. All that said, I must confess to NOT being an authority on networked multi-user solutions. Others on this forum will undoubtedly have much more to offer on this topic.

           

          RW

          • 2. Re: Separating database from solution???
            jjfcpa_1

            Rick

             

            Thanks for your reply, but if you've never developed an application for an accountant, then it's probably difficult to understand the type of application that I am talking about.  

             

            Here are a sample of some applications that would have to be written this way.

             

            Income Tax Preparation 

            Fixed Asset/Depreciation

            Accounting (such as Quickbooks)

            Trial Balance

            Amortization

            Time and Billing

             

            While every one of these could be written to work for just one client, but the real advantage to the accounting profession is when they are written so that they can be used for MANY clients.  Most developers avoid doing this because it requires you to include a file management system so you can backup, copy, duplicate, rename, delete, and of course, create data files.  

             

            Putting all clients in one Filemaker database would certainly be convenient, just not practical for the accounting/legal profession.  

             

            Still wondering if Filemaker is capable of creating an application like this, or if it is really intended to develop the "one client only" type of application.

            • 3. Re: Separating database from solution???
              fitch

              I think this could be accomplished without too much fuss using a combination of the separation model and a plugin such as Troi File.

               

              The plugin gives you the ability to rename files, so you could select the client file you want to use, rename it to the expected data file name, open it, and then restore the client data name when you close it.

               

              Not tested.®

               

              (FWIW I don't completely agree with your premise, but it's an interesting idea.)

              • 4. Re: Separating database from solution???
                RickWhitelaw

                I stand by what I said. There is no need to create separate files or tables for each client. The application I've developed and used successfully deals with recruitment, payroll, taxes, garnishees . . . you name it. I deal with multiple clients and employees per "client". Networked or not . . . there are better ways to deal with security than creating a new file for each client. I would never leave myself open to having a potentially infinite number of files. As I said earlier, there will be people on this forum more experienced with the network aspect.

                 

                RW

                • 5. Re: Separating database from solution???
                  FentonJones

                  The main mechanism for allowing people to see only their records is in Accounts & Privileges, a Privilege Set with a record-level View restriction. The calculation would something like:

                   

                  Get (AccountName ) = data field with their account name

                   

                  FileMaker then takes care of stopping them from seeing data in records which fail the test. It automatically restricts the found set to only records they have access to after doing a Find. It does not however filter the full found set they see when they first open the file, or if they hit Show All Records. They will see others' records, but not the data, only <<no access>> in every field; still secure, but not pretty. Hence you need a routine on startup, and your own replacement for Show All.

                   

                  The two downsides to View retrictions are a large speed hit, and the fact that their account name must be in a data field in every relevant table. I suppose it would be possible to enter it as encrypted data, using a plug-in or Custom Function (?), as an auto-enter calculation. I think would make it insanely slow however, if it had to encrypt Get (AccountName) for each record on the fly, before showing Find results. (I'm not sure if it would just cache it once, likely not.)

                   

                  It would likely be much faster if you did that only once, in the Client's table, then tested against that using a relationship back to client. A relationship would still be slower than just Get (AccountName), but perhaps acceptable. 

                   

                  It seems to me that it would secure. But I'm not a security expert per se (Stephen Blackwell is, and has a book on the subject). You would certainly want a separate file, not shared, with the real account names for each client.

                   

                  The FileMaker login also uses a case-sensitive password. But you have no access to that whatsoever.

                  • 6. Re: Separating database from solution???
                    jjfcpa_1

                    I think you're missing the point.  Security is only one aspect of the solution that's needed, and it's probably the least significant.

                     

                    The real issue is that it is generally NOT advisable to co-mingle client's data.  If for no other reason than portability.

                     

                    Accountant's, by their nature, are very mobile people.  Very often, they do client work not only in their office, but have to travel to the client's site to complete an engagement.  Whether it be an audit, compilation or review engagement; or even just preparation of a tax return, the accountant generally has to do some of the work at the client's office.  

                     

                    With this in mind, the portability of the data is paramount.  Being able to move the client data from computer to computer is something that must be built into the solution.  In fact, comprehensive file management system allowing them to move the database, rename it, copy it, etc. would be ideal.  

                     

                    I worked in public accounting for 15 years and have been developing software for public accountants and tax preparers for the last 25 years while still maintaining my own accounting practice.  

                     

                    I certainly understand that it would be easier to put everything in one database and I would be the first to say, "yea, let's do it that way".  But I have in excess of 20,000 users who would scream bloody murder I did.  But then again, maybe that's why I have 20,000 users...?

                    • 7. Re: Separating database from solution???
                      FentonJones

                      What we're trying to do is tell you what it is possible in FileMaker. It is not clear from what you've written so far what your FileMaker skill level is. So it is hard for us to tell if you know what levels of security are possible, without having to have separate files.

                       

                      Separate files are more secure, for the simple reason that, as you say, if you only have 1 client's data file with you at the time, then only 1 could be lost. 

                       

                      OK, so now that we've ruled out the single data file scenario, what else would work? FileMaker recognizes files by their name. So, if you had 2 different data files, one a "master", with a specific name, then changing the name of another file (while both were closed, unless you want to damage them) with exactly the same structure to the name of the master data file would allow it to be seen as the same.

                       

                      If I had to do this I would go for the Data Separation model, where the Interface file would have all the scripting, layouts,* and also have most globals and constants (which apply to all clients, regardless of who they are). 

                       

                      Then all you'd have to do is rename each client's data file to the master name before using it. However, it also means that any data fields you added to a particular client's file would not work with the common Interface file. You would need to add such changes to the master file.

                       

                      So the problems are then mostly about development and "updates". If you add a new feature, which requires changes to the Data file, then you would have to do a update with Imports (for each table) into the master Data file for that client's data file in order to use them. 

                       

                      All complex systems should have such an Import routine anyway. It is the best and fastest way to completely recover from a crashed file. The Import routine would update the Next Serial ID, a well-made one would check them afterwards. This can all be automated (tho tedious to build).

                       

                      It would be major PITA to do such an update to all client data files manually, if all were getting the new features. But that could also be automated, using an external tool (such as AppleScript on a Mac, or a plug-in, or command line), from a list of the names of the client files. In all cases both the Interface and Data files must be closed when data files are being renamed. 

                       

                      At any given time you could only have 1 client's Data file working with the Interface file, as it would have to be renamed to the master Data file's name. But there would be data within it identifying who it was. If you need more than one, you could duplicate the master Interface file, and put the different systems into named folders.

                       

                      Using the Data Separation model would allow you to have only one Interface file. In my experience most of the functionality is in that file, including most of the relationships. But there also often cases where you need to add fields to the Data file.

                       

                      [P.S. FileMaker recognizes its fields internally using a hidden FieldID. If you mess about, adding fields to the separate Data files, it is very likely to get them out of sequence. If they get out of sequence, then the Interface file will not see the same field in different Data files. It is very difficult to keep this straight. The only sane method is to only work in the master files. It would be advisable to do as much development as you can before having separate Data files. But it can all be done (I think).]

                       

                      *Of course the Data file has basic layouts also, at least one for each table. But they should be considered more as "developer" layouts. I put the Import routine scripts into the Data file, as they have nothing to do with Interface. The Data file has far fewer relationships and simpler layouts.

                      • 8. Re: Separating database from solution???
                        davidanders

                        Have you investigated FMBook$Connector?

                        http://www.fmbooksconnector.com/

                        • 9. Re: Separating database from solution???
                          TKnTexas

                          Let me ask a couple questions:

                           

                          Would you clients be working in a runtime that you create in FMPA and distribute to each?

                           

                          The Service Bureau would be working with a copy of the client's data as had been sent to them.  Would the client be working in their database while it is "checked out" to the Service Bureau?  

                           

                          Would the Service Bureau be changing data in the database of the client before sending it back to them?  Or is it a review and report aspect only?