11 Replies Latest reply on May 29, 2014 11:02 AM by deninger

    I am new to Filemaker Pro and have a question about merging database files

    annelinton

      I work with a database that gets updated everymonth with new information( records) as well the database itself may be changed in some way. For instance their may be a new field added or the value lists may change. So I was wondering how to import or merge the old database with the new. I have no problem importing the records but am wondering if there is a way to merge or update some of the nuts and bolts of the database if it was changed at all.

       

      Thanks

      Anne Linton

      Wenzel Coaching

        • 1. Re: I am new to Filemaker Pro and have a question about merging database files
          timwhisenant

          Hi Anne,

           

          So you have someone else send a new revised database each month, if I understand correctly?

          If that is the case and schema (fields, value lists, etc) is changed/ updated, then why not import the old database into the new one?

          That way the database will have both the latest changes and all of the data.

           

          Tim

          • 2. Re: I am new to Filemaker Pro and have a question about merging database files
            annelinton

            Thanks Tim

             

            I did that but it still doesn't seem to import the new value list that I am

            looking for. Is there a way to update value lists? Or should that happen

            automatically?

             

            thanks for your help,

            Anne

            • 3. Re: I am new to Filemaker Pro and have a question about merging database files
              timwhisenant

              Anne,

               

              If you copy the old data into the newly received file (with the value list modified), then yes, It will stay there and be available going fwd.

               

              There is no process or functionality for importing the value list per-se.

               

              Tim

              • 4. Re: I am new to Filemaker Pro and have a question about merging database files
                techt

                If the schema has changed and the new one is the master, you'll want to import your (old) copy into the (new/recently received) copy, that way you're sure to get the schema and structure, if I understand your question correctly.

                 

                Value list are another beast. I've found that there are typically two types of value lists. First is one where the data never changes as those values are used to control various processes in the database and a change would break those processes. Think of a status for a record, active or inactive. We might want to make a client record in the database inactive or active and any point, but we wouldn't want to make them "peach" for example. Subsequent finds or list views key off of this status field for counts, reports, etc..

                 

                The second type of value list changes to the needs of the users and is built upon and modified over time. Services that are offered, titles, etc. are examples of these.

                 

                In situations of the first, there isn't a problem since the list is static. In the second situation, I've found it easier to build a table that holds the value list values and that in turn follows any upgrade, update, or import/merge into a new file. Most value lists of this type can be managed with just a simple list view.

                 

                HTH,

                 

                TT

                • 5. Re: I am new to Filemaker Pro and have a question about merging database files
                  annelinton

                  thanks sorry I didn't get back to you over the holidays but yes this makes sense to me now...

                   

                   

                  AL

                  • 6. Re: I am new to Filemaker Pro and have a question about merging database files
                    RossHurley

                    As described in another recent thread, a separate file for user value lists may be your answer. If the user regularly modifies value lists, they could have a separate 'List Setup' file with no data, just their custom value lists in it. This file could live on the user's own machine or be on the server. The value lists in the master file just draw their values from the equivalent list in the List Setup file. I doesn't matter, then, what you do with the master file, their value lists remain unaltered because you're not updating that file. Techt highlights where some value lists used by the system must never change. I leave these lists in the master file, along with lists which can only be changed by head office. That way, users can't inadvertently modify system or head office lists and, when you send updates of the master file to users, it will include any changes made to head office-maintained lists.

                     

                    Hope this also helps.

                     

                    Ross Hurley

                    Adelaide, Australia

                    • 7. Re: I am new to Filemaker Pro and have a question about merging database files
                      milanm

                      I am sorry if i did not understand the question well, but there is a built in utility, when you import records from file, that allows you to import new data and choose to merge it with the old data as long as you have similar unique values to compare, or add the new records on the top of the old records. I guess everybody here knows that, but maybe pays to mention, sometimes things could be really simple to solve.

                      There is also a possibility to choose to populate your value list from a particular field, rather then using custom values, this way every new value that comes into this field will be added to your value list, seams less complicated then what Ross suggested, though you could do it that way as well.

                      The only problem i can see are the new fields required, those you have to add manually, i suppose there should not be a lot of them if you planned your tables and fields well initialy. If some field have to be changed frequently signifies that it tends to grow and become defined as a separate value that could then change in each set of records (hope this makes sense).

                       

                      Cheers, Milan

                      • 8. Re: I am new to Filemaker Pro and have a question about merging database files

                        Filemaker lacks an import for manualy entered value lists. You would have to copy and paste the changes into the matching value list.

                         

                        One way around this is to use a field in the table as the contents of the value list.

                         

                        To create a manual list that is updateable you could use a text field in a special table. One simple method would be to use just one record in the table and add a field for each value list. The text field would have the values entered and that field would be targeted as a value list in the manage value list editor. The manual entries could be imported from the old file.

                        • 9. Re: I am new to Filemaker Pro and have a question about merging database files
                          Stephen Huston

                          This just brought to mind a system I built for a Products / Services value list where the client needed to be able to add items which had not yet entered into any records, and remove some old items which were in existing records but were no longer to be offered, but we didn't want to have to keep editing several value lists which relied on the same overall set of info, but with different items in each of the lists.

                           

                          I created a table where each master value-list-item got its own record, with these basic fields:

                          • Item Name
                          • IncludeIn (a checkbox field showing the names of the available value lists, which, incidentally, could include this very list -- self-referencing)
                          • VLname1
                          • VLname2, etc. for as many fields a there were value lists.

                          Each of the VLname fields had an auto-enter by Calculation set to EVALUATE based on whether the pattern count included the correct value list name, and, if so, to auto-enter the value from Item_Name, or, if not, to leave it blank.

                           

                          Then every value list was pointed at the contents of the correct VLname# field as its source.

                           

                          This gave a table which, in list view, allowed to adding or removing of any existing value to any value list, or the creation of new values with a single new record with the approriate checkbox(es) marked. All of our dynamic value lists could be updated quickly and easily in one place without having to edit multiple lists. We even used this technique for updating lists of staff (i.e. Sales Person VL) to add or remove people so lists were easy to keep both current and dynamic.

                          • 10. Re: I am new to Filemaker Pro and have a question about merging database files

                            RULE ONE IN MODIFYING/UPDATING COPIES of a database: Do Not Change the names of fields, layouts, etc. Filemaker does handle these changes in some cases but don't gamle. Do not change calculated fields.

                             

                            RULE 2: Creata value lists that use the contents of fields. These will always remain accurate but there may be some problems such as an item disappearing if a record is deleted.

                             

                            RULE 3: there are other rules as you will learn.

                             

                            IF you create a copy of the master database or use the latest backup for your revisions, then it will contain all of your value lists as created in the master. The only value lists you will need to worry about are the ones where the developer can modify the contents of a fixed value list or users can add values of their own.

                             

                            When you are finished with your revisions, backup the actie database, close the active database, import the existing data from the active master into your revision. Replace the active master with your revision. Rename the revised database with the active database name, etc.

                             

                            This will work if you can use the most recent backup to make the changes.

                             

                            The developer should use the most recent backup, clone it, and transfer his changes into this file. Then import the data into this revision.

                            • 11. Re: I am new to Filemaker Pro and have a question about merging database files
                              deninger

                              If plan on updating your schema and need to do the export / import steps to update your client, don't forget to consider your primary key if it is a simple integer that is incremented with each record. You need to develop a way to record the next value and restore it at the end

                               

                              GetNextSerialValue ( fileName ; fieldName ) to grab it then

                              Set Next Serial Value [ <table::field> ; <value or formula>] to restore it.

                               

                              OR, if you are starting from scratch or are not too far down the development path, consider using

                               

                              Get (UUID) as your PK for each table (as a calculation).  UUID is a 16 byte (128 bit) string that represents a universally unique identifier that takes the form of

                               

                              E47E7AE0-5CF0-FF45-B3AD-C12B3E765CD5

                               

                              NOTE: Be sure your PK field is set to a STORED CALCULATION.

                               

                              The nice thing about using this as a PK is that you don't have to worry about your next PK matching a previous PK after you import your data.