4 Replies Latest reply on May 14, 2012 8:52 AM by AllisonKerivan

    Copying data to one field from multiple portal records



      Copying data to one field from multiple portal records


      I duplicated the script steps found on the Support page post with the above title, but all that happens is that the first portal line only gets copied. Is there a problem in the posted page as it currently exists? I'm pasting it here for your easy reference. Thanks in advance for your help!


      To copy data to one field from multiple portal records do the following:

      In the master database add fields:
      Combine (Text)
      GobalCounter (Global) - Number
      KeyCounter (Calculation) - Number - Count(Relation::KeyID) - KeyID used as the relationship link field

      Use these script steps:

      Set Field[GlobalCounter, "1"]
      Set Field [Combine, ""]
      Go to Portal Row [ select, First ]
      Copy [ select, "Relation::Fieldname"]
      Paste [ "Combine"]
      Set Field [Combine, Combine & "¦"] 
      Set Field[GlobalCounter, "GlobalCounter + 1"]
      Go to Portal Row [ select, "GlobalCounter" ] - specify by field value
      Exit Loop If [ "KeyCounter < GlobalCounter"]

      What this script does:
      First, the script "initializes" the GlobalCounter by setting it to 1, and then clears out the Combine field (so that it starts out empty). Then, starting at the first portal row, it loops through all the portal rows, copying the related Fieldname and pasting its value into the Combine field. Then, after each related value is copied into Combine, it appends a Carriage Return to the Combine field, so that the next related value appears on the next line. (This is optional). It finally exits when it has cycled through all the relatedrecords.

        • 1. Re: Copying data to one field from multiple portal records

          WHere did you find that "support page"? Is this in the knowledge base, in this forum, FileMaker Help, or somewhere else?

          If this is a knowledgebase article, it's severely out of date. Please post the article ID number or a link to the article so that I can forward this info to the FileMaker Inc. folks to correct it.

          There are a number of potential problems with it. Copy/paste is not ideal and Go To Portal Row can easily fail if you have more than one portal on your layout. Might this be the case?

          This code:

          Copy [ select, "Relation::Fieldname"]
          Paste [ "Combine"]
          Set Field [Combine, Combine & "¦"]

          Would put the contents of the last portal row copied into the "combine" field and then appends a "|" character to the end of that value as your paste step overwrites the previous value with each loop through the portal.

          Keycounter does not get a value assigned to it. Unless it is a calculation field that counts the number of related records in your portal, this script will execute the loop exactly once as an empty field will be less than the 1 stored in GlboalCounter at this point.

          Assuming you want to see Row1value | Row2value | Row3value and so forth in the combine field, use this calculation and you won't need to use any script at all:

          Substitute ( list ( Relation::FieldName ) ; ¶ ; " | " )

          and you can use any character as the delimitter that you want, such as commas instead of a vertical line character.

          • 2. Re: Copying data to one field from multiple portal records

            Thanks for your quick reply. Yes, the calculation I had pasted in my email was from the Support knowledge base (answer ID 2403). I had also seen the option of Substitute ( list ( Relation::FieldName ) ; ¶ ; " | " ), but we are using a slightly older version of Filemaker Pro (8.5), which does not have the Substiute List calculation function. (And yes, I have only one portal on my layout.)

            So if you were the one writing a script, how would you create a simple one that would work? Also, what other functions might I use to produce a list-making field (and to eliminate the script step altogether)? 

            • 3. Re: Copying data to one field from multiple portal records

              In the future, please let us know what version you have so we do not waste time posting a solution that won't work for you.

              Set Field [Globals::gCombine ; "" ]
              If [not IsEmpty ( RelatedTable::Field ) ]
                 Freeze Window
                 Go To Related Record [Show only related records; From table: RelatedTable; Using layout: "RelatedTable" (RelatedTable)]
                 Go to record/Request/Page [first]
                    Set Field [Globals::gCombine ; Globals::gCombine & "|" & RelatedTable::Field ]
                    Go to record/request/page [next ; exit after last]
                 End Loop
                 Set Field [Globasl::gCombine ; Right ( Globals::gCombine ; Length ( Globals::gCombine ) - 1 ) ] //strips off the leading | character
                 Go to layout [original layout]
              End If

              Notes: For the combine field, you can use a field with global storage like I have done here, use a variable (if supported by version 8.5), or you may make it a text field in the parent table (the layout specified for the layout where you have your portal.) By switching layouts like this, you avoid interacting directly with your portal and this is a slightly more robus approach--one less likely to be messed up by a future change to your layout design.

              • 4. Re: Copying data to one field from multiple portal records

                If you have multiple portals on a page how can you use the substitute & list function but set parameters so it only displays certain data?