13 Replies Latest reply on May 11, 2009 4:43 PM by philmodjunk

    append to a field instead of overwriting it

    webguy262

      Title

      append to a field instead of overwriting it

      Post

      can FM import records but instead of overwiting the data in a particular field, append it with the data in the imported field?

        • 1. Re: append to a field instead of overwriting it
          philmodjunk
            

          Not directly as part of an Import Records operation, but you can set up this process with a little work.

           

          Here's a rough outline of how you might do this:

           

          Import the data into Temp--a temporary table.

          Create a relationship linking Temp to MainTable--the table where you want to append your data.

          Either write a script to loop through the data and use the following script step to append data for each such field:

           

          Set Field [MainTable::FieldName, MainTable::FieldName & Temp::FieldName]

           

          Or

           

          Use Replace Field Contents (Found in the Records Menu) to replace with a specified calculation:

           

          Use:

           

          MainTable::FieldName & Temp::Field

           

          as your specified calculation.

          • 2. Re: append to a field instead of overwriting it
            LaRetta_1
              

            You can also use a global field but you must apply caution.  You would import (update and add to) from your new text to the global field.  Your standard text field (AppendField) would have auto-enter (replace existing value) with TrimAll ( AppendField & " " & global ; 0 ; 0 )

             

            Be sure you match on an ID so the proper records are updated.  And show all records first.

             

            After your import, it is important to clear your global field in case you later (during that User session) wish to change any data in the append field.  If you leave data in the global and you change ANY appendField data while the global contains a value, then the global data will append to that record.

            • 3. Re: append to a field instead of overwriting it
              LaRetta_1
                 This won't seem to work if you are adding new records.  I believe this is because of the order of the commit vs. auto-enter.  So it can be used to update (append) to fields but, unless others have a way to address the issue, adding records should use another import with straight 'add new records' and pointing the new text directly to the AppendField (skipping global use).
              • 4. Re: append to a field instead of overwriting it
                philmodjunk
                  

                LaRetta,

                 

                I really think your suggestions are confusing the issue.

                 

                The original post describes using Import Records. This suggests, but I could be wrong, that they want to append different data to different existing records. Using a global field doesn't make sense for that task.

                 

                That's why I've posted a general description and am waiting for a response from the original poster. If I've made correct assumptions, then maybe they have all the help they need or a follow up post from me can fill in more detail. If I'm wrong, we can move in a different direction.

                 

                Let's wait to hear from the person who made the original post.

                • 5. Re: append to a field instead of overwriting it
                  LaRetta_1
                    

                  Webguy262 said, "can FM import records but instead of overwiting the data in a particular field, append it with the data in the imported field?"

                   

                  My process does EXACTLY that. Did you even give it a try?

                  • 6. Re: append to a field instead of overwriting it
                    LaRetta_1
                      

                    Phil said, "Let's wait to hear from the person who made the original post."

                     

                    Hmmmm, now nobody can make a suggestion besides you?  I will suggest whatever I wish to suggest ... just as everyone else is capable of doing.   My process DOES use import if you've had bothered to try it.

                    • 7. Re: append to a field instead of overwriting it
                      philmodjunk
                        

                      "Hmmmm, now nobody can make a suggestion besides you?"

                      Nope, never said that. I suggested waiting since the original post is somewhat vague.

                       

                       My process DOES use import...

                      I can't really follow what you are suggesting. Feel free to provide more detail. Collaborative suggestions make this forum very valuable.

                       

                      I never said your process didn't use Import, I simply expressed my opinion that it didn't make sense to use a global field since there could well be a different value to append from every imported record to a matching existing record.

                       

                      I repeat my suggestion to wait, because all suggestions, including mine, could be way off base here without hearing more detail about what they really want to do.

                       

                      Peace LaRetta,

                       

                      I'm trying to help, not criticize.

                      • 8. Re: append to a field instead of overwriting it
                        LaRetta_1
                          

                        You crack me up!  You contradict yourself, saying "Feel free to provide more detail. Collaborative suggestions make this forum very valuable” and then again telling me to stop with my suggestion.  Truth is, I didn't want to publically correct you when you said it was not possible using a straight import and it IS POSSIBLE.  Your suggestion was to create another table, create another relationship and then use Set Field[] or Replace Field Contents[]!! 

                         

                        "I simply expressed my opinion that it didn't make sense to use a global field since there could well be a different value to append from every imported record to a matching existing record."

                         

                        Again, it can all be handled with a straight import, where differerent values in records coming in, appends to the same ID records in the existing table.   And all I did was suggest an alternate method.  So shoot me.

                         

                        And yet you still haven't even tried it.

                         

                         

                        • 9. Re: append to a field instead of overwriting it
                          webguy262
                             Thanks to both of you for the quick and detailed responses.  At this point, I am looking for help in the specific instance of importing records.  My client runs basketball camps.  They keep track of which camps kids attend every year.  Unfortunately, they have always kept the "camps-attended" data in a single field!  They just manually added the new camp code to the text field (PointGuard06, BallHandling07, etc.).  So now that we want to suck in online orders, they want to append the "camps-attended" data in a player's record with any new camp registration codes.  Does that help focus your recommendations?  Thanks again!
                          • 10. Re: append to a field instead of overwriting it
                            LaRetta_1
                              

                            Okay, here is the exact process:

                             

                            <!-- [if gte mso 9]><xml> <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif] --><!-- [if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif] --><!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:"";      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:"Times New Roman";      mso-fareast-font-family:"Times New Roman";} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;} --><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable      {mso-style-name:"Table Normal";      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:"";      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:"Times New Roman";      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} </style> <![endif] -->

                            Main table with:

                             

                            ID

                            textField (which needs to be appended with incoming imported field data)

                            global

                             

                            textField has auto-enter calculation (replace existing value) with:

                             

                            TrimAll ( textField  & " " & global ; 0 ; 0 )

                             

                            Import script would be:

                             

                            Go to Layout [ MainTable layout ; MainTable ]

                            Show All Records

                            Import [ importFile ; Update matching records in found set only  ]

                            Set Field [ global ; "" ]

                             

                            … and match in the import map:

                             

                            Import::newText to MainTable::global field

                            Import:: ID = MainTable::ID

                             

                             

                             

                            • 11. Re: append to a field instead of overwriting it
                              LaRetta_1
                                

                              Yes webguy, then my suggestion will work.  You map on the players ID and simply import into the global as suggested and it will append the text field.

                               

                              But you realize that this data should be split out into a normalized structure, make up of related tables, right?

                              • 12. Re: append to a field instead of overwriting it
                                philmodjunk
                                  

                                So for each imported record, you need to append new data from matching imported data. Using import records to pull data directly into your table overwrites existing data with the imported data which is the problem you wish to avoid. You need to import the data into a different location and then use a 2nd step in the process to combine the data into a single field. Let me know if I've got that wrong.

                                 

                                You can either Create an extra field in your existing table or create a new table, (It's easy to creat a new table with the import Records tool as you can specify a "new table" as the target for your imported data) and link that table with a relationship.

                                 

                                In either case, the next step is fairly simple:

                                 

                                Use Replace Field Contents with a specified calculation:

                                 

                                MainTable::NameofFieldWithCurrentData & "¶" & TableName::NameofFieldThatReceivedTheImportedData

                                 

                                I'm using place holders for the table and field names as I don't know the current field and table names, but hopefully, you get the idea.

                                 

                                Now with that said, I'd recommend taking this accumulated data and breaking it out into separate records in a related table. Then each import could simply add a new record to the related table. That would do an even better job of solving this problem.

                                 

                                Note to Laretta, I'm sending you a private message, check the upper right corner of your screen for the envelope Icon. we've opened up a separate issue that has no place here (in my opinion) in this thread.

                                • 13. Re: append to a field instead of overwriting it
                                  philmodjunk
                                    

                                  LaRetta,

                                   

                                  Very clever, once all the details were spelled out so I could follow it. That wasn't how I read your original post at all. :smileywink:

                                   

                                  I never realized you could import data into a global field and have it process through in this fashion.

                                   

                                  You do have to be sure that your auto-enter option is selected during import. That may not be possible in some cases if you have other auto-enter options set that you don't want the import to change. You can test with a copy of your file to make sure it works.

                                   

                                  I'd also separate the values with a carriage return (use the paragraph symbol) instead of a space.

                                   

                                  The only other issue which is so minor that I don't think it really matters is that using a Global field means you can't review the imported values after import to make sure you pulled them in correctly.