9 Replies Latest reply on Jun 26, 2015 7:37 AM by BerryHoning

    Scripting problem, repetitive fields

    BerryHoning

      Title

      Scripting problem, repetitive fields

      Post

      I'm having a recipe file that has been made up in the old days, so with repetitive fields. Like this:

      material x (repetitive, 30 field); name (repetitive, 30 fields); percentage (repetitive, 30 fields), etc.

      After we made the recipe, we want to write all the separate ingredients into a log file. Therefore I made a script without loops, which is quite long. It copies each field seperately to the next file. It looks like this:

      Go to layout 1

      New record

      Go to layout 2

      Copy ingredient name

      go to layout 1

      paste ingredient name.

      And so forth, for all the variables I want to copy and this a 30 times for each repetition. It works, it only takes a few seconds, but the script could be better. It now consists of 1114 script lines.

      Can you suggest other means by doing this? I think especially about creating a value by which I can add up the repetition, and by a loop.

       

      B.

        • 1. Re: Scripting problem, repetitive fields
          philmodjunk

          You can use import record to copy blocks of records from one table to another in one step. In the process, the import step can split the repeating fields into individual records. Thus, you can copy the data in one or many records all in one step.

          You should never copy data to the clipboard in order to move it somewhere else. That destroys any data the user might have copied to the clipboard previously. Instead, set a variable to the value of the field in place of copy and then use set field to move the data from the variable to the target field in place of paste.

          • 2. Re: Scripting problem, repetitive fields
            BerryHoning

            Jeez... how can I interpret what you write here... You should know, I'm a user since 2.1. So, this file dates back to 1993 or so. Layout is like this:

            Productname - product number - quantity

            raw material 1 - raw material name 1 - percentage 1 - RM quantity 1

            raw material 2 - raw material name 2 - percentage 2 - RM quantity 2

            (etc.)

             

            And I want to go to a file like this with one hit at a button:

            Product number - product name - quantity - raw material 1 - raw material name 1 - RM quantity 1

            Product number - product name - quantity - raw material 2 - raw material name 2 - RM quantity 2

             

            I'm not sure I understand you if you say: copy blocks of records from one table to another in one step. It's the structure of both files that confuses me I guess, or FM made something nice up in the mean time.

             

            • 3. Re: Scripting problem, repetitive fields
              BerryHoning

              Oh, and file #1 is one record, file #2: each line is a record.

               

              • 4. Re: Scripting problem, repetitive fields
                philmodjunk

                And that's exactly how I understood things to be. Since FileMaker 3.0, Import records comes with an option to split repeating fields in the source table into individual records in the target table. Presumably, this was done as a way to make it easy to phase out the use of repeating fields by replacing them with a related table of individual records. But it will do what you describe needing to do in this thread.

                The process works both manually and via a script.

                Let's say you want to copy the data from one record in your recipe table into multiple records in the log table, one record for each set of parallel repetitions in your repeating fields. (First target record get's the first repetition of each of the repeating fields, the second target record gets the second repetition of the same fields...)

                First, isolate that one record in a found set of a single record. You can perform a find or show all records, omit the record you want to copy from and then show omitted only.

                Then, (if manually importing), go to a layout based on the target table and select import records. Select the very same file that you already have open if both source and target tables are in the same file. Use the field mapping dialog to align the fields from which you want to copy data in the source table to fields in the target table. When you click the import button, a small funky dialog appears asking you two questions. One asks if you want to split the data in repeating fields into separate records. Select this option and import your data.

                The result will be that the data from the one record in your recipe table has been copied into up to 30 individual records in your log table.

                The number of steps is much shorter than your current script and the above steps are nearly the same for either a manual or scripted use of Import Records to copy over the data. (In a script, you don't have to change to a layout based on the target table.)

                • 5. Re: Scripting problem, repetitive fields
                  BerryHoning

                  Yeah, just found it, sorry I let you write another reaction, we just crossed each other.

                  I was just trying, following the script, connecting all the variables and found the 'write away as seperate records' option, ran the script and here it is...

                  The only thing I need to do now is to get rid of the empty records now.

                  Many many thanks.

                  • 6. Re: Scripting problem, repetitive fields
                    philmodjunk

                    A script can perform a find and delete them. But when I use this method (have inherited a DB where student attendance data is stored in repeating fields and have to split the data up in order to do analysis of it.) I don't get blank records. If the repetition is empty, no corresponding record is created in the target table.

                    To find a record where a given field is empty, enter find mode and put a lone = operator in the field. (You probably know this already). This can be a manual or scripted operation.

                    • 7. Re: Scripting problem, repetitive fields
                      BerryHoning

                      Yeah, I already had this step. Is quite easy... Just need to be careful now, I have for example three recipes under the same product code. I need to copy only the actual record. Find/omit could be dangerous. (I might use wrong terms, I use dutch version...)

                      • 8. Re: Scripting problem, repetitive fields
                        philmodjunk

                        Apologies for stating the obvious but "three recipes under the same product code" does not sound like a good thing. You need some sort of ID that uniquely identifies each recipe so that you can treat them differently from each other. That can be a field other than the product code. (Perhaps your recipes produce the same product but substitutes different ingredients such as using different forms of sugar for different recipe variations of the same product?)

                        • 9. Re: Scripting problem, repetitive fields
                          BerryHoning

                          Yes I know. These are older versions of the same recipe.

                          I'm trying to look if I can find something to select only the actual record, if not, I must get rid of the doubles.