8 Replies Latest reply on Nov 7, 2011 9:45 AM by philmodjunk

    Copy a record to same table OR How do 'Go to object' and 'Go to Portal Row' really work?

      Title

      Copy a record to same table OR How do 'Go to object' and 'Go to Portal Row' really work?

      Post

      I'm having trouble with getting "portal row select" and "Duplicate Record" to work.  My ultimate goal is to make a copy of a record in the same table (hence "Duplicate Record").  However, this is complicated by the fact that I want to also duplicate the line items associated with that one record.

      I have 3 tables that are mostly of interest:  Users, Searches, LineItems.  Users is currently associated with Searches via User_ID (PK in Users, FK in Searches); the relationship is a cartesian product (X, not =).  LineItems is related to Searches via "Search_ID" (just a straight '=').

      I am doing most of this work on a layout based on "Users".  I have a number of portals on this layout showing various bits of Searches.  I have one portal that shows the currently selected search (a global field in Searches records this selection).  Below that is another portal showing the line items for that selected search, so a portal showing "LineItems".

      I can think of two ways to do this:  switch to a 'Searches'-based layout, do a find for the currently selected search, then duplicate that record (should only be one - it is a PK I'm searching on).  (How does duplicate really work, anyway?  How do you know what record it is working on?  And when it is done, I am assuming that the original record stays as the active/found record.)  The other option is to use my portals, which are already filtered to the items I want.

      I have been trying to use my portals by using 'Go to Object' script steps and using the named objects.  (Yes, gave the objects unique names and using them correctly - did copy and paste of the text.)  So here are those steps:

                Go to Object [ Object Name: "SelectedSearchPortal"]
               Go to Portal Row [Select; First]
               Duplicate Record/Request

               #Update new row's info to reflect the current userID instead of the original's userID; and some other bookkeeping.
                Set Field...
                Set Field...
                Set Field...

      So how would I select this newly created record if it doesn't automatically become the active record in the portal (which it probably shouldn't as the portal is filtered by the "Slctr_Search" global field)?  I need the new SearchID from it to adjust the copies of the line items I will make.  I suppose a new find (switching layouts if I do it from the portals page, but wouldn't have to if I went with a switch&find process from the start) using a MAX() function on the SearchID could work.  But what if someone else is making a new Search at that exact time, too?  :)  (Matching on USERID could help that.)

      Back to the portal paradigm.  I switch to the line item portal to make copies of the line items, which should be showing the ones for the currently selected search (which is contradictory to my hope that the other portal might auto-update to the new one).

                Go to Object [ Object Name: "CriteriaLinePortal"]
                Go to Portal Row [Select; First]
                Loop
                    Duplicate Record/Request
                    Set Field...  (to match new SEARCHID)
                    Go to Portal Row [Next; exit after last]
                end loop

      In some ways it is probably a good thing that the portals don't update, because then I can use them to copy the existing stuff.  I just need to figure out how to best find and update the newly duplicated rows so I can properly update those records.

      Thanks,
      J

        • 1. Re: Copy a record to same table OR How do 'Go to object' and 'Go to Portal Row' really work?
          philmodjunk

          Users is currently associated with Searches via User_ID (PK in Users, FK in Searches); the relationship is a cartesian product (X, not =)

          With a cartesian join, the fields specified have no bearing on the function of the relationship. You could even delete the two key fields used to define the relationship from their respective tables and the relationship remains valid.

          (How does duplicate really work, anyway?  How do you know what record it is working on?  And when it is done, I am assuming that the original record stays as the active/found record.)

          Filemaker scripts work from the perspective of the current record and found set of the current layout. This, in turn, is controlled by the table occurrence name selected in Show Records From in Layout Setup... (A table occurrence is one of the "boxes" found in Manage | Database | Relationships.) Thus, if your script performs the duplicate record action, it duplicates the current record of the current layout. All fields in the duplicated record will match the original except for those with auto-enter settings such as the auto-entered serial number you should be using for the primary key. Thus, you get a copy of the original but with a new and still unique primary key.

          When you duplicate a record, the new duplicate copy of your record is added to the found set and it now becomes the current record.

          Instead of interacting with the portal, it works better to pull up the portal records shown in this portal on a layout based on the portal table and duplicate them there. The script that duplicates them must also update the foreign key field with the primary key of the new, duplicated parent record (The search record). Thus, such scripts usually duplicate the parent, save a copy of the new PK in a variable and then pull up and loop through the portal records, cuplicating them and also updating them with the value from the variable so that the duplicated parent record also displays the duplicated line item records in its portal.

          Here's a link to a thread where a script to do that is posted: Duplicating Bill Of Materials (duplicating portal line items)

          • 2. Re: Copy a record to same table OR How do 'Go to object' and 'Go to Portal Row' really work?

            OK, I will give it a shot.  Thanks for the link to the other thread.  I will give it a shot.

            • 3. Re: Copy a record to same table OR How do 'Go to object' and 'Go to Portal Row' really work?

              Well, that seems to have done the trick.  Thanks for the script, Phil!

               

              Now, the next twist in this quest will use a similar loop.  But the part I can't seem to figure out is how to build the search on the target layout?  To clarify, these line items are going to contain search fields (the names of the fields to be searched) and the data to search against.  I need to extract the data from each criteria line and build find records on a different layout.

               

              The criteria table contains a number of fields.  The important ones are the field to search on (a drop down list that exactly matches the actual field names in the target table).  The other field(s) are variable, including free text, date, or other type that I am not aware of yet (I am still learning this system  :) ).  Essentially one type of field for each of the possible types of data in the target table.  Then on the target table I need to pull these criteria out, select the right field to search against, and fill in the criteria.  The target table is the same each time at least.

               

              I can use the same looping technique you posted to go through and build search variables from the individual lines, but how do I add them to the find mode on the other layout?  If I just do a 'go to layout' on each iteration through the loop, won't I get a new/blank version of the layout (i.e. it won't remember what I might have set up on the previous iteration)?  And when I go back to the layout holding my search criteria line items, won't that found set also reset?

               

              So, should I just build a boat load of variables, cycle through the entire loop, and THEN switch to the layout where I want to find and build the find records all at once?  Is it possible to have variable variable names and use a loop counter, such as $criteria$num = $criteria1, $criteria2, etc. and $searchData$num = $searchData1, $searchData2, etc. on each pass through the loop?

               

              I found this thread (http://forums.filemaker.com/posts/fe1cf6096a) that seems to be using a field repitition to hold multiple values.  That isn't how I have currently set up my layout and tables to collect the user's input.

               

              Hmm...would Modify Last Find work?  Requires that you do a Perform Find though, not just add a new record/request.  Or so it seems.

               

              What about a new window, and then just navigate back and forth between the two with window names?

               

              Thanks,

              -- J

              • 4. Re: Copy a record to same table OR How do 'Go to object' and 'Go to Portal Row' really work?

                The new window trick seems to kind of be working.  I haven't ironed out all the kinks, but I was getting new records added and the criteria data filled in. 

                • 5. Re: Copy a record to same table OR How do 'Go to object' and 'Go to Portal Row' really work?
                  philmodjunk

                  You can put multiple values in the same variable, Either with repetitions, or as return separated values. I'd lean toward repetitions here as your criteria might include returns and that could mess up a list of such values.

                  That way, you can loop through your search criteria records loading one variable with field names in each repetition and loading criteria into corresponding repetitions of a second variable. Then you can return to your original layout, enter find mode and start transferring the criteria from the variables into find requests.

                  Hmmm, if you can guarantee that none of your criteria will ever contain a return character, you could even skip the loop by using the List function to load all the search field names and all the search criteria for one search record in a single go.

                  • 6. Re: Copy a record to same table OR How do 'Go to object' and 'Go to Portal Row' really work?

                    Phil,

                        In the script you posted in the other thread (http://forums.filemaker.com/posts/ac6afe2913) you are using an OMIT RECORD to pare down the found set.  Is this essentially the same functionality as a GO TO RECORD [NEXT; EXIT IF LAST]?

                     

                    And how is using this check:

                    If [ Not IsEmpty ( LineItems::kf_BOMID )

                    better/worse/no-different than using: 

                    Exit Loop if (get(foundcount) = 0)?     <-- Have to be sure you are in the right context when evaluating this, of course.

                     

                    Using variables with repititions is pretty much just an array, right?  I am (vaguelly) familiar with repitions in fields, but haven't seen (nor searched for it yet, I admit) variables with repititions. 

                     

                    -- J

                    • 7. Re: Copy a record to same table OR How do 'Go to object' and 'Go to Portal Row' really work?

                      I found one characteristic of Go To Related Record:  if there are no records it just leaves you where you started from, so you can't combine that function with a Get(foundcount) check.  GTRR doesn't return a found set of [empty] (and thus Get(foundcount) would return zero), it leaves you at your original found set which means Get(Foundcount) = whatever (1 in my case).

                      • 8. Re: Copy a record to same table OR How do 'Go to object' and 'Go to Portal Row' really work?
                        philmodjunk

                        Correct, you have to either check for the presence of related records, what I did with LineItems::kf_BOMID, or use Get ( LastError ) to check for an error code immediately after the Go To Related records step.

                        To understand why I am using omit record like I am. Try pulling up a group of records on a list or table view layout and then select a record near the center of the group and use duplicate record to duplicate it. Note how the current record changes on you. The method I used, not the only approach possible BTW, is a simple method for handling that change in current record status.