10 Replies Latest reply on Dec 20, 2011 11:18 AM by JasonGreenberg

    Copy Pasting an entire column of records into another empty column

    JasonGreenberg

      Title

      Copy Pasting an entire column of records into another empty column

      Post

      I don't really understand why I'm having so much difficulty with this but I can't seem to figure out how to accomplish this.

      I have figured out how to copy multiple records but not how to paste them.  I have a feeling this has something to do with calculation and I'm quite a beginner with that stuff so please explain simply if possible.

      Thanks very much 

        • 1. Re: Copy Pasting an entire column of records into another empty column
          philmodjunk

          In some systems, like MS Access, you can copy from a query or a form and when you paste, the application attempts to align the delimitted data from the clipboard with the fields in the form, subform or table in order to paste them. FileMaker does not.

          It could be scripted, however, but it will take some doing to set up.

          What data, exactly, do you want to copy and paste?

          Why do you want to do this?

          Is this column of data from a single record or a set of records?

          From a layout or a portal?

          Would it always be the same block of data?

          • 2. Re: Copy Pasting an entire column of records into another empty column
            JasonGreenberg

            Thank you for your help!  Here are the answers to your questions:

            <<<What data, exactly, do you want to copy and paste?>>>

            I pretty much need to duplicate one of the fields within every record so that there will be a new field with exactly the same data as the orignal field. I then want to alter the new duplicated fields leaving the original fields untouched.  So I have created a new field and need to now copy/paste all the data from the original field to the new field for every record.

            <<<Why do you want to do this?>>>

            I need to leave the original field for all records untouched while creating a new duplicate so I can alter the duplicates without affecting the originals.

            <<<Is this column of data from a single record or a set of records?>>>

            This is a large set of around 8,000 records.

            <<<From a layout or a portal?>>>

            I'm not sure what a portal is, but yes these are from a layout.

            <<<Would it always be the same block of data?>>>

            I'm not really sure what you mean.  I would like to copy paste a single field for multiple records and every record has unique data in this particular field. 

            • 3. Re: Copy Pasting an entire column of records into another empty column
              philmodjunk

              I suspect that you are pretty familiar with spreadsheets. Filemaker is not a spreadsheet, so different approaches are needed to get the same results.

              I pretty much need to duplicate one of the fields within every record so that there will be a new field with exactly the same data as the orignal field. I then want to alter the new duplicated fields leaving the original fields untouched.  So I have created a new field and need to now copy/paste all the data from the original field to the new field for every record.

              You can't create new fields with data in Filemaker on the fly the way you can drop data into an empty column on a spreadsheet. What you can do is duplicate existing records to create a new set of the data. You'll need to restructure this approach to support duplicating records rather than duplicating fields.

              What does this column of data represent in your database? How is your data structured?

              <<<Would it always be the same block of data?>>>

              I'll take Your answer to my last qustion in the previous post as "no". In some situations, developers have a set of x records--maybe one for each month of the year--for example and need to create a new set of 12. That makes the set to be duplicated predictable and this can be taken advantage of in a script. This does not appear to be the case here. Instead, you appear to want a completely new copy of all 8000 records in your table so that you can then edit them while leaving the original data untouched.

              Please tell me more about yoru database and what you want it to do for you. I suspect that you need to restructure your system using relationships and likely additional related tables in order to get the functionality that you need.

              I'm not sure what a portal is

              I suggest that you look up "Portal" in the help file. It may not be part of the answer to you current question, but portals are extremely frequent design elements in FileMaker databases. They solve all kinds of issues and I can't remember the last database that I created for anything other than simple testing that did not employ multiple portals in order for me to design the user interface that I needed.

              • 4. Re: Copy Pasting an entire column of records into another empty column
                JasonGreenberg

                <<<What does this column of data represent in your database? How is your data structured?>>>

                This data represents a catalogue number, each being unique. This catalogue number is used for different purposes but for technical reason it needed to end with the extension .aif so it can link to audio files and meta data can be imported from the database into the audio files.  So the current catalogue numbers are as follows 1.aif, 2.aif, 3.aif etc.  Each record has a field with this catalogue number.  Now for other technical reasons I need to have a new field in all of my records with ONLY the catalogue number without .aif.  So it needs to look like this:  1, 2, 3, 4, 5, etc.  So what I need to do is keep the original field with 1.aif, 2.aif, 3.aif etc. fully intact and create a new field in every record with 1, 2, 3 etc. format in every field.  Both of these fields need to be in all 8000 records and the numbers need to line up.  So we may have a field called cat_num where the data = 3675  and in the same record we would have a field called cat_num2 where the data = 3675.aif  This would apply to all 8,000 records.

                <<< Instead, you appear to want a completely new copy of all 8000 records in your table so that you can then edit them while leaving the original data untouched.>>>

                Hmmmm... I don't think I need new records, I need to add a new field to my current records and transfer a copy of the data from the source field to the destination field so can then alter the destination field.  I would then have the source field and the destination field in each and every of the 8,000 records.  

                I sincerely appreciate your assistance.

                • 5. Re: Copy Pasting an entire column of records into another empty column
                  philmodjunk

                  OK, scratch the comment on duplicating records as it does not apply.

                  Define a calculation field in your table with this expression:

                  Left ( cat_num ; Length ( cat_num ) - 4 )

                  It will return the catalog number but without the right 4 characters, (.aif).

                  If you want the result to be editable and this is a one time thing. Follow the above steps, then return to Manage | Database | fields and change the field type from calculation to number.

                  If this is a case where additional records will be imported and you need the new field to be editable, set this field up as a number field, but use the above calculation as an auto-entered calculation so that each newly added record automatically computes the matching extension removed value in the second field.

                  • 6. Re: Copy Pasting an entire column of records into another empty column
                    JasonGreenberg

                    <<<

                    Define a calculation field in your table with this expression:

                    Left ( cat_num ; Length ( cat_num ) - 4 )>>>

                    This is great, this enabled me to remove the .aif from all the fields which is what I need to accomplish my second step, but not my first step.
                    I first needed to copy all of the fields from all of the records and paste them into the new field for all the records.  Then I would need to remove the .aif from the new field.  This way I would have the two fields in every record, the original with the catalogue number followed by .aif and the new field with the catalogue number without .aif.  Any ideas how to accomplish this first step?

                    • 7. Re: Copy Pasting an entire column of records into another empty column
                      philmodjunk

                      No copy and pasting needed. Adding the calculation field does exactly what you have requested. It adds a new field for every record in your table and each record will show the desired value.

                      • 8. Re: Copy Pasting an entire column of records into another empty column
                        JasonGreenberg

                        My apologies, you are correct.  Before I mistakenly used the replace with calculation command instead of adding the calculation to the actual new field like you told me.
                        This fixed my problem, Thank You, Thank You Thank You!!!!  Laughing

                        • 9. Re: Copy Pasting an entire column of records into another empty column
                          philmodjunk

                          Don't know why I didn't think of it before, but Replace Field Contents with the calculation option could be used here to copy the modified data from the original field into a new text or number field. As a one time fix, either approach works. If you will be adding additional records in the future, the calculation field method may be better here.