9 Replies Latest reply on Jan 13, 2012 8:22 AM by PSI

    New database for 2012


      I just started working for a new client who uses FileMaker Pro 5. We need to create new copies of work orders and invoices for 2012. I have done the following:


      1. Open FileMaker Pro
      2. Open 2011 Invoice.fp5 from a back up copy
      3. Save a copy as 2012 Invoice.fp5
      4. Opened 2012 Invoice.fp5 and see that all scripts and data copied


      I need to format the copy and change color and header to reflect 2012


      Can I copy these down to a PC - edit the format then delete records so 2012 Invoices.fp5 is empty.

      Do the same for other 2012 fp5 files?


      Once done - copy back to server and open under HOST?





        • 1. Re: New database for 2012

          If there are multiple files in this solution (besides the 2012 Invoice.fp5) that have relationships to each other, then you need to do this a different way.  All those relationships will be broken by renaming the file.


          If the files are unrelated, then this method could work, unless there are script steps that call the name of the file - those will be broken by renaming the file the way you have done.


          Best practice:


          1.  move ALL the 2011 files you will be updating to 2012 from the server to a local drive.

          2.  Using a copy of FileMaker Developer Tool from the FileMaker Developer 5 (or 6) software - which hopefully you have, create a project and rename ALL the files in the solution to the 2012 names - this will preserve links and hopefully script steps or calculations which use hard-coded file names.

          3.  Open the new files and check them, then you can either delete all records or save a a copy as Clone - which will also remove any records.

          4.  I would also recommend you save a copy as Compressed - if you are deleting records on a regular basis, the file size will be a bit bloated and compressing the file will remove any leftover bits plus rebuild the file structure - usually making it more stable…


          Be sure to do a lot of backups.  You are working with software that is more than 10 years old, and is no longer supported by FileMaker.


          If you do not have access to the FileMaker Developer Tool, you will have to manually fix all the "broken" relationships and script steps from renaming each and every file.  This will take at least hours and probably days depending on how many files you have.  Go slow and test carefully before putting these files back into production.


          Good Luck!



          Karen Weaver



          Desert Dog Technology, Inc.

          • 2. Re: New database for 2012





            Unless this is a very rare circumstance it is crazy to have to create new copies each year. Obviously, some info must be hard coded into the solution. I would look into what needs to be changed to allow for one set of db’s to contain many years of data.




            I would be interested to know what you find.




            John Morina


            Pueblo System, Inc.


            CCQ-FM Inc.



            • 3. Re: New database for 2012


              What if you have value lists containing project names that are very old?

              How do you archive them so they no longer show on the value list?

              • 4. Re: New database for 2012

                Permit me to give you a bit of advise:

                • FileMaker 5: For many many reasons the solution should be updated to the new version of FileMaker. I can elaborate on that later.
                • The architechture of the solution is most proably not correct. Thus the need to make a new version for a new year and to change the soluton just because you reach a new year.
                • You will need someone with database/filemaker experience to help you out. Or you will need to go through a material like FileMakers Training Series

                Hope that you can forgive me. The advises here are for sure better than continuing with a solution that, for me, seems to be build in a very impractical way. So to say.


                Best regards




                An extra bit of advice: Most +10 years old servers running arcane software like FileMaker Server 5 will be running without proper IT support and probably without a reliable backup. Tread carefully!

                • 5. Re: New database for 2012

                  dchabot wrote:



                  What if you have value lists containing project names that are very old?

                  How do you archive them so they no longer show on the value list?

                  Why do they need to be removed? If you want to find an older project having the project name in a VL would help, no? Either way value list are easily edited.


                  Carsten has suggested converting this solution to the latest version. While your client is eventually going to need to do this you will still have to reengineer the system so new copies don't need to be generated each year.


                  • 6. Re: New database for 2012

                    dchabot wrote:


                    What if you have value lists containing project names that are very old?

                    How do you archive them so they no longer show on the value list?


                    One way to do this is to add a calculation field in the Projects table =


                    Case ( not Archived ; ProjectID )


                    then use this field for your value list instead of ProjectID. Archived could be a date field that is filled when a project becomes "very old".

                    • 7. Re: New database for 2012




                      • 8. Re: New database for 2012


                        Value lists can become too large. I do love comments suggestion.


                        Do you not consider a Payroll program as a good candidate to rollover a new database annually?

                        Because that's what I've been doing, but I'd consider a better way.


                        Deb C.

                        • 9. Re: New database for 2012

                          Hi Deb,




                          I don’t like large value lists either. Enabling auto complete can help but generally if a value list gets that large I use a different method of selection.




                          Anything is possible but I don’t consider any database a candidate for rollover to a new database ever!




                          There could be a need to archive data. In this case I would make a copy of the main db and set it up as an archive. I create scripts to navigate and move records from the main file to the archive version and from archive back to the main version. I have only done this once or twice over the years.




                          John Morina