4 Replies Latest reply on Feb 18, 2013 11:48 AM by davidanders

    Converting pre fp7 files with repeating fields to newer versions



      Converting pre fp7 files with repeating fields to newer versions


           I've used fm5.5 for years now to manage my contacts, bids and invoices for a freelance business. Bought fm8 several years ago to update and convert the files.  Everything worked except the repeating fields in the invoice and estimate files.  It's a pretty simple set up. Enter quantity, then item number (from price list file) then description, price per, total cost per item come up.  With the converted files, the lookups only work on the first repetition.  I'm very much a novice and am trying to figure out about tables, portals, etc. and can't seem to make it work.  Does anyone have any ideas, or maybe know where I can find a template that might do it?  The converted files from earlier years show all the data I need and are now only used for archival purposes, but I'd like to be able to continue using this basic setup in the future.  I don't want to change to something like Quickbooks and have to manually reenter all the 2000+ contacts.  Advice or help from anyone would be greatly appreciated.


        • 1. Re: Converting pre fp7 files with repeating fields to newer versions

               Is FileMaker 8 the version that you are still  using? Or have you acquired a newer version such as FileMaker 12?

               FileMaker has a very useful option you can select when importing data that will split a repeating field into a set of records with one record for each repetition. Thus, you can take one table (file in pre-7 versions) and import the data into two different tables. Omit the repeating field from the import for one table and include it in the import for the second table using that import option to split up the records. You'll need some field in this table/file before you start importing that uniquely identifies each record. YOu may need to add an auto-entered serial number for this purpose and then use Replace Field Contents to assign each existing record a serial number. If you then include this field in both record imports, you can use it to link the records fo the first table to the "split up" records of the second and use a portal to display those related records.

               Here's a very simple demo file that may be useful in showing you how to set up related tables with a portal for invoice "line items": http://fmforums.com/forum/showpost.php?post/309136/

          • 2. Re: Converting pre fp7 files with repeating fields to newer versions

                 I'm still actually using 5.5 (believe it or not). I have version 8 and never started using it because I couldn't solve that one problem with entering multiple items on invoices and estimates.  If I can figure that out, then I'll be able to update to version 12 when I update the OS on my Macs. Like I said earlier, all my old files converted fine because all I may need to do with them is read them occasionally for archive purposes.  So it's not a problem with importing the old data. The contact, job and price list files don't have any repeating fields and so I can enter data into them with no trouble.  The current year invoice and estimate files do all the lookups from these non-repeating fields fine.  It's when I try to enter multiple items into that old style repeating field that it only does the lookups for the first line.  I guess I'm trying to do this without having to recreate the whole system from scratch, and I haven't been able to figure out how to design new fields that will work and replace the old repeating fields.  I just want to enter varying quantities of different price list items into one record and be able to print or pdf an single page invoice for my clients.  Thanks already for your help.


                 PS, had already found that invoices demo you suggested, but hadn't made heads or tails of it yet

            • 3. Re: Converting pre fp7 files with repeating fields to newer versions

                   What I am suggesting is that you NOT use repeating fields for this purpose. This change may not require redesiging your whole system. It does require addinga  related table that you use in place of the repeating fields. The demo file illustrates this approach as you have a portal to Line Items on the Invoices layout that is used for data entry.

                   The importing info that I provided is how you can move existing data from a repeating field into a table of related records with one record for each repetition as a simple file conversion won't correct this issue.

              • 4. Re: Converting pre fp7 files with repeating fields to newer versions

                Converting repeating fields to portals
                     How do I convert a solution that uses repeating fields to use portals?

                     Repeating fields = Death   November 21, 2010

                     Repeating Fields #Filemaker