1 2 Previous Next 16 Replies Latest reply on Aug 19, 2016 9:37 AM by ORSA1965

    Nested Loop - need some help (free beer)

    ORSA1965

      I have two tables.  One table has 14 records in it with a field named criterion.  The second table has 10 Key Performance Indicators (KPI) in it.

      I am trying to create a new table that has a KPI and criterion pair so the new table would have 140 records in it.  So I am trying to have a loop that goes to the third table and then creates a record and drops in KPI1 and then creates KPI_1 Cr_1; KPI_1 Cr_2; ... KPI_10 Cr_14 then goes to the next KPI_2 and does it again until all KPIs are in the table.

       

      Any help is appreciated.  If I see you at a conference, I offer a beer or your fav drink.  Thank you !! - Barry (bezell@odu.edu).

        • 1. Re: Nested Loop - need some help (free beer)
          ORSA1965

          Table 1

               

          Criterion 1
          Criterion 2
          Criterion 3
          Criterion 4
          Criterion 5
          Criterion 6
          Criterion 7
          Criterion 8
          Criterion 9
          Criterion 10
          Criterion 11
          Criterion 12
          Criterion 13

          Criterion 14

           

          Table 2

               

          KPI 1
          KPI 2
          KPI 3
          KPI 4
          KPI 5
          KPI 6
          KPI 7
          KPI 8
          KPI 9
          KPI 10

           

          What I need table

               

          KPI 1Criterion 1
          KPI 1Criterion 2
          KPI 1Criterion 3
          KPI 1Criterion 4
          KPI 1Criterion 5
          KPI 1Criterion 6
          KPI 1Criterion 7
          KPI 1Criterion 8
          KPI 1Criterion 9
          KPI 1Criterion 10
          KPI 1Criterion 11
          KPI 1Criterion 12
          KPI 1Criterion 13
          KPI 1Criterion 14
          KPI 2Criterion 1
          KPI 2Criterion 2
          KPI 2Criterion 3
          KPI 2Criterion 4
          KPI 2Criterion 5
          KPI 2Criterion 6
          KPI 2Criterion 7
          KPI 2Criterion 8
          KPI 2Criterion 9
          KPI 2Criterion 10
          KPI 2Criterion 11
          KPI 2Criterion 12
          KPI 2Criterion 13
          KPI 2Criterion 14
          KPI 3Criterion 1
          KPI 3Criterion 2
          KPI 3Criterion 3
          KPI 3Criterion 4
          KPI 3Criterion 5
          KPI 3Criterion 6
          KPI 3Criterion 7
          KPI 3Criterion 8
          KPI 3Criterion 9
          KPI 3Criterion 10
          KPI 3Criterion 11
          KPI 3Criterion 12
          KPI 3Criterion 13
          KPI 3Criterion 14
          KPI 4Criterion 1
          KPI 4Criterion 2
          KPI 4Criterion 3
          KPI 4Criterion 4
          KPI 4Criterion 5
          KPI 4Criterion 6
          KPI 4Criterion 7
          KPI 4Criterion 8
          KPI 4Criterion 9
          KPI 4Criterion 10
          KPI 4Criterion 11
          KPI 4Criterion 12
          KPI 4Criterion 13
          KPI 4Criterion 14
          KPI 5Criterion 1
          KPI 5Criterion 2
          KPI 5Criterion 3
          KPI 5Criterion 4
          KPI 5Criterion 5
          KPI 5Criterion 6
          KPI 5Criterion 7
          KPI 5Criterion 8
          KPI 5Criterion 9
          KPI 5Criterion 10
          KPI 5Criterion 11
          KPI 5Criterion 12
          KPI 5Criterion 13
          KPI 5Criterion 14
          KPI 6Criterion 1
          KPI 6Criterion 2
          KPI 6Criterion 3
          KPI 6Criterion 4
          KPI 6Criterion 5
          KPI 6Criterion 6
          KPI 6Criterion 7
          KPI 6Criterion 8
          KPI 6Criterion 9
          KPI 6Criterion 10
          KPI 6Criterion 11
          KPI 6Criterion 12
          KPI 6Criterion 13
          KPI 6Criterion 14
          KPI 7Criterion 1
          KPI 7Criterion 2
          KPI 7Criterion 3
          KPI 7Criterion 4
          KPI 7Criterion 5
          KPI 7Criterion 6
          KPI 7Criterion 7
          KPI 7Criterion 8
          KPI 7Criterion 9
          KPI 7Criterion 10
          KPI 7Criterion 11
          KPI 7Criterion 12
          KPI 7Criterion 13
          KPI 7Criterion 14
          KPI 8Criterion 1
          KPI 8Criterion 2
          KPI 8Criterion 3
          KPI 8Criterion 4
          KPI 8Criterion 5
          KPI 8Criterion 6
          KPI 8Criterion 7
          KPI 8Criterion 8
          KPI 8Criterion 9
          KPI 8Criterion 10
          KPI 8Criterion 11
          KPI 8Criterion 12
          KPI 8Criterion 13
          KPI 8Criterion 14
          KPI 9Criterion 1
          KPI 9Criterion 2
          KPI 9Criterion 3
          KPI 9Criterion 4
          KPI 9Criterion 5
          KPI 9Criterion 6
          KPI 9Criterion 7
          KPI 9Criterion 8
          KPI 9Criterion 9
          KPI 9Criterion 10
          KPI 9Criterion 11
          KPI 9Criterion 12
          KPI 9Criterion 13
          KPI 9Criterion 14
          KPI 10Criterion 1
          KPI 10Criterion 2
          KPI 10Criterion 3
          KPI 10Criterion 4
          KPI 10Criterion 5
          KPI 10Criterion 6
          KPI 10Criterion 7
          KPI 10Criterion 8
          KPI 10Criterion 9
          KPI 10Criterion 10
          KPI 10Criterion 11
          KPI 10Criterion 12
          KPI 10Criterion 13
          KPI 10Criterion 14
          • 2. Re: Nested Loop - need some help (free beer)
            thurmes

            Set up Table 3 with the proper fields

            #Go to a Table 1 layout and gather criteria from Table 1 as a ¶-delimited value list (typically by starting at 1st record, gathering info, going to next record, etc)

            set variable $Criteria to this value list

            #Go to a Table 2 layout and gather KPI from Table 2 as a ¶-delimited value list

            set variable $KPI to this value list

            #Go to Table 3

            Set Variable $count to 0

            Loop

            Exit Loop If [Let ( $count = $count + 1 ; $count > ValueCount ( $KPI ) ]

            Set Variable $count1 to 0

            Loop

            Exit Loop If [Let ( $count1 = $count1 + 1 ; $count1 > ValueCount ( $Criterea ) ]

            Set Field Table 3::KPI to GetValue ( $KPI ; $count )

            Set Field Table 3::Criterion to GetValue ( $Criteria ; $count1 )

            End Loop

            End Loop

            1 of 1 people found this helpful
            • 3. Re: Nested Loop - need some help (free beer)
              user19752

              I like SQL feature for making all combination of records,

              SELECT * FROM table1,table2

              But you need setting up ODBC sharing, or parsing text result of ExecuteSQL() into records in table, or using plugin , etc...

              1 of 1 people found this helpful
              • 4. Re: Nested Loop - need some help (free beer)
                aarjunmuthu

                Hi,

                 

                As per your requirement i made this sample, KIndly find file...

                 

                Dropbox - new.fmp12

                 

                Thanks,

                aarjun

                1 of 1 people found this helpful
                • 5. Re: Nested Loop - need some help (free beer)
                  thurmes

                  I was thinking about this today and realized I had forgotten the Create New Record step right after the second Set Field. Also, ExecuteSQL is the most efficient way to gather the criteria and KPI, but take a bit of experience to write.

                  1 of 1 people found this helpful
                  • 6. Re: Nested Loop - need some help (free beer)
                    bigtom

                    I created a sample file named FreeBeer.fmp12 but I guess I forgot to press the post button. Looks like you have a solution already.

                    • 7. Re: Nested Loop - need some help (free beer)
                      ORSA1965

                      I would still like to see it please.   Thank you for taking the time.

                      • 8. Re: Nested Loop - need some help (free beer)
                        ORSA1965

                        Thank you for sharing the file.  Will this work in both Mac and PC environments?

                        • 10. Re: Nested Loop - need some help (free beer)
                          ORSA1965

                          aarjunmuthu, Here is the file I am trying to convert to the example you provided.  Would you take a look and see if it is possible to bring over a few more fields in addition to KPI Name and Criteria Name.  I am also trying to bring over four more fields. https://www.dropbox.com/sh/c32iviyts05o2qw/AABcEqcMDhvo5PD-ujhyZNfaa?dl=0

                          • 11. Re: Nested Loop - need some help (free beer)
                            bigtom

                            Why in the world is this database built this way?

                            • 12. Re: Nested Loop - need some help (free beer)
                              beverly

                              Actually, this is the quickest answer! If you tab-delimit between fields and export (or copy) the result to a text file, save the file as .tab or .txt. Then IMPORT into FileMaker as tab delimited.

                               

                              The Query likely should only contain the two fields needed:

                              SELECT table1.KPI, table2.CRITERION FROM table1, table2

                              When the tables are listed this way in the FROM, you get the "X" you would get if you linked the two tables in the RG (relationship graph). In SQL this is called an IMPLICIT JOIN. If you have no WHERE clause, you get the Cartesian JOIN (every value matches every value).

                               

                              This is what I got with the above query (obviously your values would be different) where I used " | " between for clarity:

                              KPI 1 | CRITERION 1

                              KPI 1 | CRITERION 2

                              KPI 1 | CRITERION 3

                              KPI 1 | CRITERION 4

                              KPI 1 | CRITERION 5

                              KPI 1 | CRITERION 6

                              KPI 1 | CRITERION 7

                              KPI 1 | CRITERION 8

                              KPI 1 | CRITERION 9

                              KPI 1 | CRITERION 10

                              KPI 1 | CRITERION 11

                              KPI 1 | CRITERION 12

                              KPI 1 | CRITERION 13

                              KPI 1 | CRITERION 14

                              KPI 2 | CRITERION 1

                              KPI 2 | CRITERION 2

                              KPI 2 | CRITERION 3

                              KPI 2 | CRITERION 4

                              KPI 2 | CRITERION 5

                              KPI 2 | CRITERION 6

                              KPI 2 | CRITERION 7

                              KPI 2 | CRITERION 8

                              KPI 2 | CRITERION 9

                              KPI 2 | CRITERION 10

                              KPI 2 | CRITERION 11

                              KPI 2 | CRITERION 12

                              KPI 2 | CRITERION 13

                              KPI 2 | CRITERION 14

                              KPI 3 | CRITERION 1

                              KPI 3 | CRITERION 2

                              KPI 3 | CRITERION 3

                              KPI 3 | CRITERION 4

                              KPI 3 | CRITERION 5

                              KPI 3 | CRITERION 6

                              KPI 3 | CRITERION 7

                              KPI 3 | CRITERION 8

                              KPI 3 | CRITERION 9

                              KPI 3 | CRITERION 10

                              KPI 3 | CRITERION 11

                              KPI 3 | CRITERION 12

                              KPI 3 | CRITERION 13

                              KPI 3 | CRITERION 14

                              KPI 4 | CRITERION 1

                              KPI 4 | CRITERION 2

                              KPI 4 | CRITERION 3

                              KPI 4 | CRITERION 4

                              KPI 4 | CRITERION 5

                              KPI 4 | CRITERION 6

                              KPI 4 | CRITERION 7

                              KPI 4 | CRITERION 8

                              KPI 4 | CRITERION 9

                              KPI 4 | CRITERION 10

                              KPI 4 | CRITERION 11

                              KPI 4 | CRITERION 12

                              KPI 4 | CRITERION 13

                              KPI 4 | CRITERION 14

                              KPI 5 | CRITERION 1

                              KPI 5 | CRITERION 2

                              KPI 5 | CRITERION 3

                              KPI 5 | CRITERION 4

                              KPI 5 | CRITERION 5

                              KPI 5 | CRITERION 6

                              KPI 5 | CRITERION 7

                              KPI 5 | CRITERION 8

                              KPI 5 | CRITERION 9

                              KPI 5 | CRITERION 10

                              KPI 5 | CRITERION 11

                              KPI 5 | CRITERION 12

                              KPI 5 | CRITERION 13

                              KPI 5 | CRITERION 14

                              KPI 6 | CRITERION 1

                              KPI 6 | CRITERION 2

                              KPI 6 | CRITERION 3

                              KPI 6 | CRITERION 4

                              KPI 6 | CRITERION 5

                              KPI 6 | CRITERION 6

                              KPI 6 | CRITERION 7

                              KPI 6 | CRITERION 8

                              KPI 6 | CRITERION 9

                              KPI 6 | CRITERION 10

                              KPI 6 | CRITERION 11

                              KPI 6 | CRITERION 12

                              KPI 6 | CRITERION 13

                              KPI 6 | CRITERION 14

                              KPI 7 | CRITERION 1

                              KPI 7 | CRITERION 2

                              KPI 7 | CRITERION 3

                              KPI 7 | CRITERION 4

                              KPI 7 | CRITERION 5

                              KPI 7 | CRITERION 6

                              KPI 7 | CRITERION 7

                              KPI 7 | CRITERION 8

                              KPI 7 | CRITERION 9

                              KPI 7 | CRITERION 10

                              KPI 7 | CRITERION 11

                              KPI 7 | CRITERION 12

                              KPI 7 | CRITERION 13

                              KPI 7 | CRITERION 14

                              KPI 8 | CRITERION 1

                              KPI 8 | CRITERION 2

                              KPI 8 | CRITERION 3

                              KPI 8 | CRITERION 4

                              KPI 8 | CRITERION 5

                              KPI 8 | CRITERION 6

                              KPI 8 | CRITERION 7

                              KPI 8 | CRITERION 8

                              KPI 8 | CRITERION 9

                              KPI 8 | CRITERION 10

                              KPI 8 | CRITERION 11

                              KPI 8 | CRITERION 12

                              KPI 8 | CRITERION 13

                              KPI 8 | CRITERION 14

                              KPI 9 | CRITERION 1

                              KPI 9 | CRITERION 2

                              KPI 9 | CRITERION 3

                              KPI 9 | CRITERION 4

                              KPI 9 | CRITERION 5

                              KPI 9 | CRITERION 6

                              KPI 9 | CRITERION 7

                              KPI 9 | CRITERION 8

                              KPI 9 | CRITERION 9

                              KPI 9 | CRITERION 10

                              KPI 9 | CRITERION 11

                              KPI 9 | CRITERION 12

                              KPI 9 | CRITERION 13

                              KPI 9 | CRITERION 14

                              KPI 10 | CRITERION 1

                              KPI 10 | CRITERION 2

                              KPI 10 | CRITERION 3

                              KPI 10 | CRITERION 4

                              KPI 10 | CRITERION 5

                              KPI 10 | CRITERION 6

                              KPI 10 | CRITERION 7

                              KPI 10 | CRITERION 8

                              KPI 10 | CRITERION 9

                              KPI 10 | CRITERION 10

                              KPI 10 | CRITERION 11

                              KPI 10 | CRITERION 12

                              KPI 10 | CRITERION 13

                              KPI 10 | CRITERION 14

                              Beverly

                              p.s. the answer from user19752user19752

                              should be marked as CORRECT.

                              1 of 1 people found this helpful
                              • 13. Re: Nested Loop - need some help (free beer)
                                ORSA1965

                                Ha ha.  I don't know another way.  The user determines the names and number of Key Performance Indicators and the user can add or take away from the number of criteria.  I think I need a new table for them to assess each KPI-criterion pair.

                                 

                                Of course if the KPIs and criteria ere fixed, this would be simpler. - Barry

                                 

                                PS  I can send the actual file if you want to see it.

                                • 14. Re: Nested Loop - need some help (free beer)
                                  ORSA1965

                                  user19752, bigtom

                                   

                                  The link below is a better representation of the file.  You see I would like to create the new table with the KPI - Criterion pairs and also include the criterion's weight.  Thank you Beverly for the solution.user19752

                                  https://www.dropbox.com/s/pkrxw1gi8sgn58y/KCtestfile.fmp12?dl=0

                                   

                                  There are three tables.

                                   

                                  Table C has fields Cr_UID, Criterion Name, Weight to be added to the KC table

                                  Table K has fields KPI_UID and KPI Name to be added to the KC table.

                                   

                                  The plane is once that I have these two tables combine, the scores can be added by the user.

                                   

                                  I am happy to share the final solution once I have it.

                                   

                                  Barry (bezell@odu.edu)

                                  1 2 Previous Next