4 Replies Latest reply on Jul 2, 2013 5:05 PM by DEC

    Children of a single designated Parent to be duplicated among all of the child-less records in a found set of Parents?

    DEC

      Hi Everybody!

       

      This doesn't seem too complicated at first blush, but I've been wrestling with it for over a week with no success.

       

      Goal: I need the all Children of a single designated Parent to be duplicated among all of the child-less records in a found set of Parents.

       

      This function is useful where the user wants to avoid manually typing into the database duplicate Children records among tons of Parents.

       

      I googled the forums and the net in general for instruction/articles, and couldn't find anything. Any thoughts would be much appreciated...

       

      Thank you!

       

      -DEC

        • 1. Re: Children of a single designated Parent to be duplicated among all of the child-less records in a found set of Parents?
          Malcolm

          Goal:  I need the all Children of a single designated Parent to be duplicated among all of the child-less Parents of a found set.

           

          If you really want to duplicate children Filemaker might not be the right approach

           

          This seems to be a job for a join table.

           

          Parent A has children B and C. In the join table this is represented by two records. That is, AB and AC.

           

          You have a found set of parents, D and E. (you got the found set of childless parents by searching for all parents with children and omitting them).

           

          Get the children of the parent A into a variable X.

          Loop through the found set of parents.

          for each parent in the found set

               loop through the items in X

                    for each child

                         create a new record in the join table

           

           

          Malcolm

          • 2. Re: Children of a single designated Parent to be duplicated among all of the child-less records in a found set of Parents?
            DEC

            Thanks for your feedback, gents; you got me thinking, and I've put together a solution that almost does the trick...

             

            The steps are laid out below.  I tested it with successful results, and it works but for one problem:  the script kicks too early, as described in Step 3 below.  So close!!  I'd certainly appreciate any advisement : )


            Thanks and Warm Regards,


            -DEC



            Propogate Duplicate Issues Step 1 Script: 

            //This script sets the variable for the current Parent record containing the Children that need to be duplicated and assigned to each Parent in a found set of unrelated Parents in the same table.//

             

            Set Variable [ $$TitleIDforNewIssues; Value:Titles::ID_TitleRecord ]

            Perform Script [ “Propogate Duplicate Issues Step 2” ]




            Propogate Duplicate Issues Step 2:

            //This next step enables the user to find the Parents who will have the duplicate children assigned to them.  It accords a pause, so that the user can omit select records from list view before continuing to step 3 below.//

             

            Go to Layout [ “FIND_Titles” (Titles) ]

            Enter Find Mode [ ]

            [ Pause ]

            Perform Find [ ]

            If [ Get(FoundCount)=0 ]

            Show Custom Dialog [ Title: "Message"; Message: "No records match this request."; Default Button: “OK”, Commit: “No” ]

            Perform Script [ “Cancel Find” ]

            Else

            Go to Layout [ “Listview_Titles For Duplicate Issues” (Titles) ]

            Enter Browse Mode

            Sort Records [ Keep records in sorted order; Specified Sort Order: Titles::Titles_DXNumber; ascending ]

            [ Restore; No dialog ]

            Go to Record/Request/Page

            [ First ]

            Halt Script

            End If




            Propogate Duplicate Issues Step 3:

            //This last script sets the variable for the first parent in the found set and sets a variable with it's ID.  It then finds all the issues to be duplicated and assigned to that first parent, and then does so.  Then it moves onto the next Parent and does the same until there are no more Parents left.  The Problem:  The script moves onto the next Parent, processes it, and then quits, despite the fact that there are 184 additional Parents wanting their duplicated Children!//


            Go to Record/Request/Page

            [ First ]

            Loop

            Set Variable [ $TitleIDofChildlessParent; Value:Titles::ID_TitleRecord ]

            Go to Layout [ “FIND_Issues” (Issues) ]

            Enter Find Mode [ ]

            Set Field [ Issues::ID_TitleRecord; $$TitleIDforNewIssues ]

            Perform Find [ ]

            Go to Record/Request/Page

            [ First ]

            Loop

            Duplicate Record/Request

            If [ Issues::ID_TitleRecord ≠ $TitleIDofChildlessParent ]

            Set Field [ Issues::ID_TitleRecord; $TitleIDofChildlessParent ]

            Omit Record

            Go to Record/Request/Page

            [ First ]

            Omit Record

            End If

            Go to Record/Request/Page

            [ Next; Exit after last ]

            End Loop

            Go to Record/Request/Page

            [ Next; Exit after last ]

            End Loop

            Go to Layout [ “FIND_Titles” (Titles) ]

            Enter Find Mode [ ]

            Set Field [ Titles::ID_TitleRecord; $$TitleIDforNewIssues ]

            Perform Find [ ]

            Go to Layout [ “Titles_IssuesViewInPortal” (Titles) ]

            • 3. Re: Children of a single designated Parent to be duplicated among all of the child-less records in a found set of Parents?
              Malcolm

              Loop

                   Set Variable $TitleIDofChildlessParent; Value:Titles::ID_TitleRecord

                   Go to Layout

                   Enter Find Mode

                   Set Field Issues::ID_TitleRecord; $$TitleIDforNewIssues

                   Perform Find

                   Go to Record/Request/Page[ First ]

                   Loop

                        Duplicate Record/Request

                        If Issues::ID_TitleRecord ≠ $TitleIDofChildlessParent

                             Set Field Issues::ID_TitleRecord; $TitleIDofChildlessParent

                             Omit Record

                             Go to Record/Request/Page[ First ]

                             Omit Record

                        End If

                         

              1. at this point you created one record and omitted two, so you are on the "next" record

                        Go to Record/Request/Page[ Next; Exit after last ]

                         

              1. now you have skipped a record!

                         

              1. if it is the last record you are exiting the loop

                   End Loop

                    

              1. The last script step exited the loop

                   Go to Record/Request/Page[ Next; Exit after last ]

                    

              1. therefore this one will too.

              End Loop

                    

              1. safely exited all loops after handling the first childless parent.

               

               

              Test this code:

               

              Loop

                   Set Variable $TitleIDofChildlessParent; Value:Titles::ID_TitleRecord

                   Go to Layout

                   Enter Find Mode

                   Set Field Issues::ID_TitleRecord; $$TitleIDforNewIssues

                   Perform Find

                   if

                        Loop

                             Duplicate Record/Request

                             Set Field Issues::ID_TitleRecord; $TitleIDofChildlessParent

                             Go to Record/Request/Page[ Next; Exit after last ]

                        End Loop

                   End if

                   Go to Layout

                   Go to Record/Request/Page[ Next; Exit after last ]

              End Loop

               

               

              malcolm

              • 4. Re: Children of a single designated Parent to be duplicated among all of the child-less records in a found set of Parents?
                DEC

                This is great, Malcolm, as it got me VERY close.  The only problem with it was that only one child got duplicated and propagated among all the parents in the found set.  The below script did the trick.  Thanks so much for your help, and thanks to everyone on this thread!!!

                 

                Warm Regards,

                 

                -DEC

                 

                 

                Go to Record/Request/Page

                [ First ]

                Loop

                Set Variable [ $TitleIDofChildlessParent; Value:Titles::ID_TitleRecord ]

                Go to Layout [ “FIND_Issues” (Issues) ]

                Enter Find Mode [ ]

                Set Field [ Issues::ID_TitleRecord; $$TitleIDforNewIssues ]

                Perform Find [ ]

                Set Variable [ $TotalIssuesToDupe; Value:Get ( FoundCount ) ]

                Loop

                Exit Loop If [ $TotalIssuesToDupe = 0 ]

                Go to Record/Request/Page [ $TotalIssuesToDupe ]

                [ No dialog ]

                Duplicate Record/Request

                Set Field [ Issues::ID_TitleRecord; $TitleIDofChildlessParent ]

                Omit Record

                Set Variable [ $TotalIssuesToDupe; Value:$TotalIssuesToDupe - 1 ]

                End Loop

                Go to Layout [ “Listview_Titles For Duplicate Issues” (Titles) ]

                Go to Record/Request/Page

                [ Next; Exit after last ]

                End Loop

                Go to Layout [ “FIND_Titles” (Titles) ]

                Enter Find Mode [ ]

                Set Field [ Titles::ID_TitleRecord; $$TitleIDforNewIssues ]

                Perform Find [ ]

                Go to Layout [ “Titles_IssuesViewInPortal” (Titles) ]