11 Replies Latest reply on May 12, 2014 10:22 AM by NicoleHartman

    Loop within a Loop

    NicoleHartman

      Title

      Loop within a Loop

      Post

           I have a table that has a list of database IDs and then about 20 categories that are all represented as separate fields. 

           I am trying to loop through each record and capture the field name title and the contents of the field for each record. I can't figure out how to loop through all of the records and capture the information and then move to the next field and reloop through each record. Attached is a screen shot of the script I am currently using, it successfully allows me to loop through the first field and capture the information I need, but I don't know how to get it to iterate through the rest of the fields. 

            

            

      Screen_Shot_2014-05-10_at_6.41.56_PM.png

        • 1. Re: Loop within a Loop
          philmodjunk

               It could be possible to do what you want, but not by using go to field and go to next field as you lose the focus in the current field when your script changes layouts. You'd need to set up a list of field names and use set field by name, get field, along with getvalue to pull field names from your list.

               but a better, much simpler set up would be to replace your "about 20 categories that are all represented as separate fields." with a set of up to 20 related records so that you can loop through records instead of trying to loop through fields.

          • 2. Re: Loop within a Loop
            NicoleHartman

                 Thanks for the advice. 

                 If I use your second approach, how do I relate my records though?

                 In the attached filmmaker file I have my data in the table "Job Functions". This is response data from a survey. The respondent was asked to answer a question about different job functions and then specify if they do it or someone else at their company does it. 

                 The script I am trying to write is to recreate this date in the Tableau Export table by repeating the ResponseID's over and over for each category (currently represented as fields). I ran my current script which is doing what I want it to do for the first category (Preliminary Design)

                 I set up the additional table with my Job Function Categories (there are 32), but I don't understand how I can relate them to my Job Function table and loop through the records.

                 Any additional help you can provide would be so appreciated!

                 Link to database - http://vectorworks.groupdropbox.com/share/f7cee278d54c814681e9a750404617d8

                 Thanks!

                 Nicole

                  

                  

                  

            • 3. Re: Loop within a Loop
              philmodjunk

                   The following link describes a possible set up of tables and relationships for surveys/questionnaires: Need aid on generating a report from a survey layout.

              • 4. Re: Loop within a Loop
                NicoleHartman

                     Thanks for the additional help. Do you not think it is possible to accomplish what I am trying to do with my current table set-up the way that it is?

                     Thanks,

                     Nicole

                • 5. Re: Loop within a Loop
                  FentonJones

                        

                       I don't think Phil is here on Sun., so I'll respond. If you looked at Phil's answers on the other link (which I'm sure you did), then you'll see one post which has a script, which is the "normal" way of handling this kind of data. However, after looking at your file, I see that you have kind of the "opposite" data. It seems the data that you have has the data as the (32) fields, and that what you want to do is turn that data into the more "normal" data of 32 records, one for each of those fields. So, you would need to do the other method which Phil mentioned; which is to do each of those 32 fields, one at a time. Which is not a huge problem, and you can use your "active field" method (if you go to each field).

                        

                       I downloaded your file, then made a few changes to your script. The visual difference is that I split it into the "main" script and a "sub-script"; because it saves having to write many script steps over and over (etc.), as they can run as they are if in a sub-script. That meant a couple of your Variables needed to have "$$", in BOTH scripts (and be cleared at end). But otherwise I think you'll see it's your script.

                        

                       P.S. I turned off "Show All Records", just for me; but also as I wonder how you'll handle any future data with that hard-coded into your script; up to you. (I also don't know why the Sort, but also up to you.)

                        

                       I also have to question whether there is not better ways to store the large pieces of data, just as "Question". The method of that depends to a great deal on whether this kind of data is unique over time and use of the database; kind of different questions of there own. So here is a script(s) that seems to work (I put both as a picture):

                  • 6. Re: Loop within a Loop
                    NicoleHartman

                         Thanks Fenton!

                         Yes, I looked at Phil's responses but it didn't seem like it was accomplishing exactly what I wanted. I am pulling this data straight from our survey software and therefore trying to avoid having to do too much manual messaging of the date because I want the process to be easily repeatable for future projects. 

                         What you provided accomplishes exactly what I am trying to do, I was trying to avoid having to manually set up the script to set the field for each of my Field/Categories though. Is there no other hack I can use that will iterate through the fields or allow me to set-up a list or second table to iterate through them? Phil alluded to a way to use a list or other table, but I didn't understand how to use them. 

                         I am basically using filemaker to take data from our survey software and then reorganize it into a format that works better for our analysis software. I don't mind some set-up work, but having to spell out the fields like that for every possible response from the survey is going to be time consuming and error prone I am worried. 

                         I am new to filmmaker, so I had the show all and sort on just because I always try to create my scripts exactly as I manually do things and those are two things I always manually do :-/

                         Thanks again for both of your help!

                         Nicole

                          

                          

                    • 7. Re: Loop within a Loop
                      philmodjunk

                           If you set up a layout with only the fields you need to process in this list, the following step script will capture the list of field names in a return separated list:

                           Set Variable [$FieldList ; value: FieldNames ( Get ( FileName ) ; "LayoutForFieldNames" ) ]

                           Then a loop can loop through this list of field names and use the GetField function to access the value in each field:

                           Loop
                              Set Variable [$K ; value: $K + 1 ]
                              Exit Loop If [ $K > Valuecount ( $FieldList ) ]
                              Set Variable [$Response ; value: GetField ["Job Functions::" & GetValue ( $FieldList ; $K ) ]
                              #Code to create new record with this data goes here

                           End Loop

                      • 8. Re: Loop within a Loop
                        NicoleHartman

                             Thanks Phil. I tried to implement the script into my current script, but it doesn't seem to be working. 

                             What am I doing wrong?

                             The script isn't iterating through the records or the fields. 

                             Thanks so much for you guys help!

                             Nicole

                              

                        • 9. Re: Loop within a Loop
                          philmodjunk

                               You need two loops--one nested inside the other, the outer loop to loop through the records and the inner loop to loop through the fields that are part of each record--just as your thread title describes. I only outlined what is needed for the inner loop.

                               And your outer loop will need to reset the value of $K to 0 just before the start of the inner loop.

                          • 10. Re: Loop within a Loop
                            FentonJones

                                  

                                 Yes, I agree with Phil, that using a dedicated layout, with ONLY the fields needed for the "categories" is the best solution; as it is likely easier for most people to modify. [ I'd done it earlier using your table of "category names"; which works fine, but the names would have to be exactly the same as those of the other table, hence could be off.

                                  

                                 This is my script using Phil's idea; though some of it is named as I would :-]. I also turned off the Show All Records and Sort Records, as the first is questionable (especially hard when testing), and the Sort slows it all down (I don't see why it matters; each is unique, right?). But either is up to you. This seems to work:

                                  

                            • 11. Re: Loop within a Loop
                              NicoleHartman

                                   Thanks! I have been playing around with it after Phil's last post. I got it to work too! 

                                   Below is my script incase anyone else ever needs help doing this same thing. 

                                   Thank you both soooo much for your help! You make learning filemaker so much easier!

                                   Nicole