3 Replies Latest reply on Sep 9, 2014 10:25 AM by philmodjunk

    Nested Loop to create multiple records



      Nested Loop to create multiple records


      Each month, we track 5 performance metrics for 7 versions of our product. We use a spreadsheet now but I'd like to move this to FileMaker. 

      I would like to create a single data entry form in FMP (screenshot with sample data attached) to simplify this process. I'd like to create a script (AddMonthlyMetrics) that created 35 new records (7 versions * 5 metrics), populated the fields shown below and displayed the information in a grid structure similar to the screenshot.


      • Reporting Period = date (YYYY-MM)
      • Software_Version = v1, v2, ...v7
      • Metric = m1, m2, m3, m4, m5

      After setting the Reporting Period to the same value for all 35 new records, I'm thinking I'll need a nested loop to step through setting the Version and Metrics for each new record.

      Is this the best way to approach this or is there a better way to create this monthly data entry form in grid structure?

      What script steps would be needed to step through all 35 combinations of version and metric?


        • 1. Re: Nested Loop to create multiple records

          An interesting question is whether you should have 35 records here or 7 records with 5 "metric" fields in each record. Both are possible, which is best depends in info not found in your initial post.

          Without knowing more about how you will use this data, I "lean" towards 35 records, but to show 35 records on the layout you show here, you'd need either a list view layout with 5 1 row portals or a form view layout with 35 one row portals. Either way, the underlying context for your records can be a record in a Report period table where you enter the report period shown once for your entire set of data. (This is not a spreadsheet program so there are ways to enter data a single time rather than repeat it over and over for a set of records...)

          But you can definitely create a looping script that creates either 7 or 35 records. Since you have to use a loop to create the new records, you can set up set field steps inside that same loop to assign values to specific fields in each new record.

          • 2. Re: Nested Loop to create multiple records

            Thank you.

            Rather than creating 5 or 35 single row portals, couldn't I create a portal based on a self join (Reporting Period)? The first steps in the script create 35 records all with the same Reporting Period?

            Assuming 7 versions of the product (v1 ... v7) and 5 metrics (m1 ...m5), how do I structure the script to step through each combination? I can't quite get my head around how to increment this properly.

            I think I understand how to create 35 new records and how to set the same Reporting Period for all 35 but I don't understand how to step through the records to set the version (5 records with the v1, 5 with v2, etc.) and the metric.

            Should this be done sequentially with several individual loop steps (1 loop to create 35 records; 2nd loop to set the version, 3rd loop to set the metrics) or with one nested loop?

            Any examples you could point me to?


            • 3. Re: Nested Loop to create multiple records

              The single row portals would be needed to display the data on the layout design shown. This is one of the issues encountered when you specify that this data be recorded in to 35 records instead of 7 records with 5 different metrics fields each. With 7 records, you don't need any portals at all for what you show. And other layout designs could display the data for 35 records without using any portals also.

              I can't tell from your post that any data would be set in the "metrics" field(s). You only show data for reporting period and version. The metric fields appear empty.

              The other thing that I was attempting to get you to consider about your underlying data model is whether you need a related parent record for the reporting period:

              Reporting Period------<MonthlyMetrics

              ReportingPeriod::__pkReportPeriodID = MonthlyMetrics::_fkReportPeriodID

              This makes it possible to enter the Reporting Period data (2014-08) once in the ReportingPeriod parent record before generating 7 or 35 individual records.

              Go to Layout ["Reporting Period" (ReportingPeriod) ]
              New Record/Request
              Set Field [ReportingPeriod::Period ; Year ( Get ( CurrentDate ) ) & " - " & Month ( get ( CurrentDate ) ) ]
              Set Variable [$RPID ; value: ReportingPeriod::__pkReportPeriodID ]
              Go to Layout ["MonthlyMetrics" (MonthlyMetrics) ]
                  Set Variable [$K ; Value: $K + 1 ]
                  Exit Loop if [$K > 7 ]
                  New Record/Request
                  Set Field [MonthlyMetrics::Version; "v" & $K ]
                  Set Field [MonthlyMetrics::_fkReportPeriodID ; $RPID
              End Loop

              To use this method to create 35 records, you'd need another loop inside of this one and another counter that counts up to 5 before resetting to 1. You might even set up a related table for the 5 "metric" records linked to the primary key of each of the 7 version records created in the above script.