12 Replies Latest reply on Feb 21, 2013 6:11 AM by lighthouserich

    Deleting Records Within A Repeating Field

    lighthouserich

      Apologies if this is very basic and has already been answered but I have searched the forum before posting this.

       

      I have a fairly strraight forward DB with a field which contains repetitons.

      Said field (along with other repetition fields) are displayed on a layout.

      I need some kind of function so that a user can press a delete button which can ask them which repetition they wish to delete and then delete that repetition only leaving others intact.

      This deletion would need to act for the same repetition on the other fields on the same layout.

       

      For example delete the 3rd repetition of the fields description, sub-description and price. etc etc.

       

      Currently using FM version 11 and will probably be upgrading to v12 soon.

       

      Any help with this will be appreciated.

        • 1. Re: Deleting Records Within A Repeating Field
          nsabel

          In a script use a Show Custom Dialog step to get the input from the user and store it into a global. Then add a set Field step for each field you want to clear. For the Calculated result specify "". For the repetition click the specify target field then at the bottom of that window click the specify repetition field and use your global that was set by the user for this calculation.

           

          You may want to add some additional verification to make sure the user doesnt enter an invalid repetition number.

           

          Nick

          • 2. Re: Deleting Records Within A Repeating Field
            DavidJondreau

            Your description of how repeating fields are being used indicates you probably shouldn't be using repeating fields. A related table is likely a better choice.

            • 3. Re: Deleting Records Within A Repeating Field
              lighthouserich

              Hi David,

               

              Thanks for your reply to my post.  I started off by having the related records in a separate table which was fine and I can see the benefit of having the data separated.

              However, I changed the DB to have repeating fields instead as quite often a record, complete with all of it's related data (either in a separate table or in the repeating fields scenario), needs to be duplicated.  To put that into context for you:

               

              1. Record exists complete with data in multiple fields associated in another table.

              2. Record is duplicated, using Filemakers standard duplicate record command from the menu bar.  The duplicate needs to include the data in the multiple associated fields.

              3. The new duplicated record can then be edited as another version of a quotation leaving the original intact.

              This duplication is common place to create various versions of quotes until the customer finally decides on the final list of equipment to go onto the particular job.

               

              When I was duplicating records the associated table data wasn't being duplicated so switching to repeating fields seemed like a good solution at the time.

               

              The deletion issue (which I originaly posted about) is to simply select and delete a particular record of data across those multiple fields in the related table.

              Of course when deleted I don't want any 'gaps' appearing which I think may well happen whilst using the repeating fields scenario.

               

              Thanks again for your input and for taking the time out to help, it's appreciated.

              Rich

              • 4. Re: Deleting Records Within A Repeating Field
                lighthouserich

                Hi Nick

                 

                Thanks for taking the time out to reply to my post and for your advice with the issue I had.

                Your help is much appreciated.

                 

                Regards,

                Rich

                • 5. Re: Deleting Records Within A Repeating Field
                  BruceRobertson

                  But that is a problem with your incomplete duplication script.

                   

                  It needs to duplicate the child records as well. This is not a good reason to use repeats.

                  • 6. Re: Deleting Records Within A Repeating Field
                    hbrendel

                    If you don't change it back asap you will get into trouble eventually...

                    • 7. Re: Deleting Records Within A Repeating Field
                      darrenburgess

                      I would concur that using repeating fields is opening quite a can a worms.  Basic problem is that is breaks the rules of database normalization.  Now while it may seem convenient now to use repeating fields, a properly normalized database can have huge payoffs down the line in terms of your abiltiy to work with the data.  I realize that sounds rather vague, but do understand that many of us have learned the hard way with respect to repeating fields and poorly normalized data.

                       

                      Look up Normal Forms in wikipedia.  Makes for excellent reading if you are having trouble sleeping ;-)

                       

                      That said, you can Set a specific repetiion in a field using the Set Field script step.  The calculation dialog is available when you specify the repetition, so you could use the user input in a Global Field to specify which repetition to set to ""

                       

                      Set Field   YourField [ GlobalField ] = ""

                       

                      There are some good uses of Repeating fields.  I used them to build a Cross Tab report (although the virtual list technique is perhaps better for cross tabs)

                       

                      Just don't use them to store data that really should live in a related table.  Good luck!

                       

                      Darren Burgess

                      http://www.mightydata.com

                      1 of 1 people found this helpful
                      • 8. Re: Deleting Records Within A Repeating Field
                        lighthouserich

                        Hi Darren,

                         

                        Thanks for your reply to my post.

                         

                        I have taken everyones advice and reverted back to a related table to store the data concerned thus eliminatng the repeating fields and it's associated problems.

                        This now creates a new issue of how to duplicate a record and it's related records in the related table.  Any advice in this area will also be appreciated.

                         

                        As a newcomer to filemaker, can I just say thanks for taing the time to try and help me as it's much appreciated.

                         

                        Regards,

                        Rich

                        • 9. Re: Deleting Records Within A Repeating Field
                          lighthouserich

                          Hi Bruce,

                           

                          Thanks for your reply to my post.

                           

                          I have taken everyones advice and reverted back to a related table to store the data concerned thus eliminatng the repeating fields and it's associated problems.

                          This now creates a new issue of how to duplicate a record and it's related records in the related table.  Any advice in this area will also be appreciated.

                           

                          As a newcomer to filemaker, can I just say thanks for taing the time to try and help me as it's much appreciated.

                           

                          Regards,

                          Rich

                          • 10. Re: Deleting Records Within A Repeating Field
                            lighthouserich

                            Hi David,

                             

                            Thanks for your reply to my post.

                             

                            I have taken everyones advice and reverted back to a related table to store the data concerned thus eliminatng the repeating fields and it's associated problems.

                            This now creates a new issue of how to duplicate a record and it's related records in the related table.  Any advice in this area will also be appreciated.

                             

                            As a newcomer to filemaker, can I just say thanks for taing the time to try and help me as it's much appreciated.

                             

                            Regards,

                            Rich

                            • 11. Re: Deleting Records Within A Repeating Field
                              darrenburgess

                              Rich,

                               

                              You did the right thing!  So how are your script writing chops?  How many script steps are you familiar with?

                               

                              You will learn allot more if you do this yourself as much as possible.  So your first step is to write out, in plain language, each step required to duplicate a record and all of its related records.  I would also consult FileMaker's script step reference guide.  Consult the guide and try to pull out the script steps that will accomplish what you are trying to do with each step.

                               

                              You could even create a flow chart.  Do anything that works to help you understand the logical flow of the problem.  Once you understand how to solve the problem, translating such in to a FileMaker script should be easy.  Plan first, then code.  (that is the second most important thing after Normalization!)

                               

                              Once you plan the script, open the script writer and put in comments for each step of the script.  After you add comments, then you put in the actual code.  Benefit to this practice is that your script will now be nicely commented.

                               

                              You will certainly need two loops.  One for parent records, one for the child records..  You could present your work here to get feed back.

                               

                              Darren Burgess

                              http://www.mightydata.com

                              1 of 1 people found this helpful
                              • 12. Re: Deleting Records Within A Repeating Field
                                lighthouserich

                                Hi Darren,

                                 

                                That sounds like good advice!

                                I'll get working on that and let you know how I get on.

                                 

                                Thanks again,

                                Rich