11 Replies Latest reply on Feb 16, 2011 1:40 PM by mgores

    Repeating Field Problem

    EmilyShep

      Title

      Repeating Field Problem

      Post

      I would like to make a script that will create a clone, open the clone, import the record from the original file and then export that to excel... the reason I need it to do all of that is because I have repeating fields. If there is a better way for me to set up my database please let me know. I currently have 2 different fields that are repeating but still need to be linked to one another along with all of the other information contained in the record.

      Thanks.

        • 1. Re: Repeating Field Problem
          philmodjunk

          I don't see what saving a clone and then importing a single record into it has to do with repeating fields. What problem does that solve for you?

          In many cases, you can replace a repeating field with a reference to a related table where each repetition is now a different related record. Such a structure offers a great deal of flexibility in a number of areas when compared to the limitations of repeating fields.

          Such related records can most easily be displayed in a column of records inside a portal, but there are ways to display them in a row as well.

          • 2. Re: Repeating Field Problem
            mgores

            I'm thinking you are trying to export the record with the repeating fields and are using the clone to make a new file where each of the repitions is a separate record.  What Phil said is the best way to do it.  However, if you've already got a bunch of data entered into the DB with the repeating fields you would need to write a script to get those repeated values into the new fields.

            I now try to avoid using repeating fields after running into those problems, but have written scipts that will get that data out into individual fields.  Something like this:

            Set newfieldAX - GetRepetition(oldrepeatingfield1;X)

            Set newfieldBX - GetRepetition(oldrepeatingfield2;X)

            Set it up to loop and increment X for the number of repetitions and go for each of the repeating fields that you have.  Once you have all of the new fields filled in you can delete the script and the repeating fields and change your layouts to use the new fields.

            • 3. Re: Repeating Field Problem
              philmodjunk

              However, if you've already got a bunch of data entered into the DB with the repeating fields you would need to write a script to get those repeated values into the new fields.

              Actually, you can use Import records to move the data into the related table with each repetition imported into a separate record. There's an option for this you can select in the last dialog that pops up just before you kick off the import.

              • 4. Re: Repeating Field Problem
                mgores

                That would be easier yet.  Just create a clone, change the repeating fields to normal fields then import from the original file like Phil said.

                • 5. Re: Repeating Field Problem
                  philmodjunk

                  And you don't need a clone. You can define a new table in your existing file and import records into it.

                  • 6. Re: Repeating Field Problem
                    EmilyShep

                    I just starting using this program 3 days ago so I'm not aware of all the commands etc... but I feel I should better explain my situation because I'm not sure if making a loop to take all my repeating fields into individual fields would be the best idea.

                    I am making a program to input time sheets, I have a field for the project #, a field for the employee, a repeating field for Cost Code (in a week, employees could work on anywhere from 1 to 15 codes, and in these 1 to 15 codes they can choose from about 100 codes), another repeating field that matches up the hours to the corresponding cost code.

                    In my report I need to print out all of the information corresponding to the employee repeated for each code & hour combo.

                    When I manually save as clone, import fields (separating repeating fields) and print my report it is exactly  how I want it to look however I need to find a way to automate this so that when other people are entering the time they only have to click one button.

                    I appreciate any suggestions you may have!

                    • 7. Re: Repeating Field Problem
                      mgores

                      Phil's answer about setting up a new table within your current file sounds like it will do exactly what you need.  The looping I mentioned earlier is not the best way for what you are doing.  For you report on each employee you could write the script to import all of the fields for that employee into separate records in the "report table"  then go to a layout that shows the project and employee in the header and the related records from the report table in a portal in the body.

                      • 8. Re: Repeating Field Problem
                        EmilyShep

                        I ended up taking Phil's advice and got it all to work! Thanks alot everyone!

                        One more hopefully quick question, I have a script that takes all records from original, imports them into the new table (separating repeating fields) and then exporting to a report.. is there a way that I can make the script answer the prompts as well?

                        • 9. Re: Repeating Field Problem
                          philmodjunk

                          What prompts are you asking about?

                          Why do you need to export your data in order to get your report?

                          I suggest that you modify your design when you get the time so that the repeating fields are not used at all--being replaced with a related table of records instead. This can make for much more flexible reporting from within FileMaker.

                          • 10. Re: Repeating Field Problem
                            EmilyShep

                            I have to send a excel file to payroll so that they can just put it into their system.

                            The prompts I am talking about are the ones that ask you what worksheet, name etc, and which files I want to import (which I already specified so its just a matter of clicking ok)

                            Thanks for your help. I will try and re-design/redo my program but at least it will work this way for now.

                            • 11. Re: Repeating Field Problem
                              mgores

                              If the imports and worksheet are always the same you should be able to select the perform without dialog option in the script.

                              If the worksheet neads to have a different filename each time you could set a variable based on fields to be the filename

                              SetVariable $filename

                              table::field & ".xlsx"