7 Replies Latest reply on Nov 10, 2010 10:44 AM by philmodjunk

    import line items to new FPro 11 solution



      import line items to new FPro 11 solution


      Hi, just bought FileMaker Pro 11 Advanced having been using version 6 since its release, probably about 10 years ago. Am trying to bring over my 6x solutions to 11x.

      In FileMaker Pro 6 I have 3 databases linked to each other: hours, charities, line items. I create one record per month in hours dB and enter the data using a line items portal window. All entries link to the line items database. I have almost 1000 records.

      Managed to get the concept recreated in FileMaker Pro 11 Advanced by creating 3 Tables and necessary relationships. (Attached sample)

      The layout design isn't working yet but will deal with that once the data is ported over.

      How do I bring over the hundreds of records from the FPro 6x line items dB (via Excel) into each of the respective year/month Hours Table (portal)? (12 records per year)? If I bring them all into the Line Items Table, they won't appear in the respective Hours Table record.

      FileMaker Pro 11 Advanced database:

      Table 1: Charities <contact for each charity>

       Table 2: Hours <Portal Fields from Line Items>

      (1.>date of activity)

      (2.>drop down showing charities from Table 1)

      (3> self-relation sort of events)

      (4> self-relation sort of venues)

      (5>self-relation sort of activity)

      (6>entered or will be calculated from fields not yet in Table (end minus finish = total)

       Table 3: Line Items <collected entries from Table 2>


        • 1. Re: import line items to new FPro 11 solution

          Tables and portals are not the same thing.

          Do all the portals on your respective tabs refer to the same data-source table? (Data source tables are the tables listed in the tables tab in Manage | Database | tables.)

          If not, they should.

          A simpler design approach might be to dispense with the tab controls for this as they require you to create a new tab with each new year.

          You can use a global "year" field that can be used to filter the entries in your portal by year. Then, instead of clicking tabs, you select a year from a the global year field's drop down or pop-up. Now you won't have to add a new tab to your layout each year.

          With all the portals, or the single portal I am recommending referring to the same data source table, you can import your data from the converted FileMaker 6 file by selecting a layout in your new file that is based on the portal's data source table and then select Import Records from the records menu.

          You might also find this Knowledge Base article usful if you haven't already read it:  http://help.filemaker.com/app/answers/detail/a_id/750/kw/converting

          • 2. Re: import line items to new FPro 11 solution

            Hi PhilModJunk:

            I started over rather than figure out how to import my 3 databases from 6x to 11x. That also gave me a chance to learn some of the changes seeing I'm lightyears behind. I created 3 Tables each representing the former 3 databases. Relationships created and working between them. Data entry perfect all collecting into the Line Items Table.

            Is this what you are telling me, or am I complicating things further:

            Global Field: YEAR (drop down)

            Selection Year: 2000 (year 2000 of 10 years so far) 

            Script: go to layout 2000 (10 years = 10 layouts with up to 12 records per) and find up to 12 records (Jan-Dec); display in list or table format so I can see what's found; manipulate existing data or create new record (month)

            If nothing is found, ask whether to create new layout and first record

            • 3. Re: import line items to new FPro 11 solution

              You should only need one layout for all the years. You then use the drop down to control which subset of all these records you want to see. I described a filtered portal approach, but you can also use such a drop down with a script that performs a find on a list or table view layout to get a very similar affect.

              • 4. Re: import line items to new FPro 11 solution


                I decided on the 10+ layouts (one per year) that have the tab control set to default of that year. That default has the portal layout, the others blank (for now). To navigate, I have buttons for each year to go to respective layout and find all records for that year. For now, it works. Hopefully I'll eventually learn how to make it so each tab can be clicked instead of the buttons.

                • 5. Re: import line items to new FPro 11 solution

                  Please download and examine this much simpler approach that can be used to view yearly data without having a separate tab or layout for each year:  http://www.4shared.com/file/cq8CodtT/ViewingDataByYear.html

                  • 6. Re: import line items to new FPro 11 solution

                    Thanks. I downloaded and am using as a report. I got the layouts to look like they're in tabs so will use that for entry. I'll just need to do "maintenance" at end/start of each year. The layout from your URL now displays everything for any year I select. Like that. Similar to viewing the Line Items but with a nice format added.

                    • 7. Re: import line items to new FPro 11 solution

                      This method can also be used for data entry.