Duplicating field contents for all records

[susan siegel]

Is there a way I can duplicate (copy & paste ??) the contents of a specific field for all the records in a table?

So far, I can't seem to use the 'select all" feature to highlight the field for all records; it just works for a single record.

There are no unique fields in the table that I can use for a lookup on a new field.

My objective: I have a table with about 1,600 records. One text field has one or more numbers (separated by commas; there's no need to sort the field). Due to a last minute update, all the numbers have to be changed -- usually by making them one digit higher.

Before I do a "find and replace" operation across the entire table, I want to duplicate the field and see the old and new fields side by side so that I can eyeball the new field for correctness and/or problems. (Being new to FM, I'm not totaly comfortable with a blind replace operation.)

Needless to say, I'm trying to avoid going through 1600 records manually.

Any suggestions would be most appreciated.

Susan

[Lee Smith]

Put that field on a layout buy itself, and then write a script to go to the layout and use the script Step Copy All

The script would look something like:

Go to Layout [ YourNewLayout ]

Copy All Records

Go to Layout [ original layout ]

Lee

Edited August 10, 2006 by Guest
sample script

[LaRetta]

Hi Susan,

"One text field has one or more numbers (separated by commas; there's no need to sort the field). Due to a last minute update, all the numbers have to be changed -- usually by making them one digit higher."

Split those numbers into a table now and save yourself much heartache in the future. You think this number increment will only happen once? Maybe and maybe not. But regardless, you will need to do other things with these numbers, such as summarize, count and so on. And you will NEVER be free of this headache until it is structured correctly.

LaRetta :wink2:

[Lee Smith]

Good catch LaRetta,

I got interrupted and didn't Finnish reading the post.

However, I don't think we have a full picture of what is needed. Maybe a sample file, or a sample of the data, would help us zero in on a solution

If it is really just changing a number by a digit, a couple of solutions come to mind.

Lee

[susan siegel]

Thanks, Lee, for the quick response.

I hadn't explored scripts yet.

I tried your suggested script but still ran into problems. Depending on how I did it, I either got nothing in the second duplicated field or just one entry in the list of 1,600 records.

Here's a recap of what I did. I'm obviously missing something.

I created a new layout (Layout #9) with just the field I want copied ("source code") and all the appropriate records are there when I view by table.

I entered the following for the script.

1) go to layout (layout #9)

2) select all (you didn't indicate this step but it seemed obvious to me as there was no other "copy all records" option.)

3) copy.

I had 2 choices here

a) select entire contents of field. I checked this.

select target field. I tried this with and without checking this box. As "layout #9" only had one field, it didn't seem necessary to check this box. But, when I did check it, I went back to the layout for the entire record and selected the approprite field "source code"

4)go to layout

here I tried it 2 ways.

a) go to layout (originial layout) as you suggested. But this doesn't seem to make sense if by "original" you mean layout #9 above -- which is the same "source code" field.

: so I went to "go to layout(layout with all the fields) and selected "revised code" as the targt field.

5) Paste to (: above. I think this step repeated what was in step 4 above.

What am I doing wrong?

Thanks for getting me started on scripts.

(My first major FM project is getting a database in shape to print out selected fields for a book.

Each day there seems to be a new challenge to overcome.)

susan

[Lee Smith]

Okay, as far as the script goes, you do not need to select all. i.e. you don't need your number 2.

You are using the wrong script step, although the step name changed in v8, it is called Copy All Records/Request, and it is below the other copy step in is listed under Records steps.

so, the modified script would like this

Go to Layout [ YourNewLayout ]

Copy All Records/Request

Go to Layout [ original layout ]

However, I don't think it is going to do what you need. If it doesn't change things, then post a copy of your file or a sample of the data. I prefer a file, because I can personalize it to your file.

HTH

Lee

Edited August 10, 2006 by Guest

[susan siegel]

Thanks again, Lee.

Unfortunately, still not working.

Attached is a copy of the file.

What I am able to do is copy and paste the "source code" field into the new "revised code" field -- BUT - it comes out in one record, as a list. You'll see the "15" in the attached file in the first record.

this would be okay -- except for the fact that the leading is different from the original field and after a few records the records (numbers) don't line up properly.

In the attached test tile I've moved the two critical fields over the the left so you don't have to bother with the remaining fields.

Most of the fields in the source file have just one number and I'm pretty confident that I won't have a problem with "find/replace" on these. My concern is when I have multiple numbers, e.g., 7, 37, 145.

The possible numbers range from 1-168 (assigned numbers based on an alpha list from another file. Because I have to add 3 entries to the alpha list, new numbers have to be assigned and transfered over to this file.

Susan

test810.zip

[Lee Smith]

I opened your file, and I see the field you are referring to. The script we were toying with is not going to work here.

You have probably said this already, but I want to make it clear in my mind.

What do you want happen with the multiple numbers in a field. Do you want to create a couple more fields for them? Make them go away?,

I know you mentioned something earlier about the numbers needing to be increased, but lets deal with this one step at a time.

Lee

[Lee Smith]

Okay, I developed a script to parse out the Source Code field into 4 fields where needed. After this process, I did a find for missing data in the Source Code Field and found that you had over a 1000 records without data in it.

test810_a.fp7.zip

[Mikhail Edoshin]

You cannot directly duplicate a field along with its contents. You should create a new empty field and copy contents from the old field into the new one. Try the Replace Contents... command in the Records menu. It replaces contents of any field across the whole found sent with either a constant, a serial number or a calculation. You need to select the calculation and in the calculation dialog enter the name of the original field. The calculation is evaluated on per record basis, so in every record it will copy the contents of the original field of this record.

The command is quite powerful, so please be careful with it. It's better to try it on a copy of your file first.

[susan siegel]

Hi Lee,

The numbers in the "source code" field, #1-164, direct the user to an alphabetical list of 164 specific sources which will be listed, by number, elsewhere in the publication. That's why the contents of this field vary from a single number to 2 or more numbers; some records have more than one source.

Unexpectedly, after I listed the source code/s for all 1,600 entries, I discovered that 3 sources had been omitted from the original list of 164 sources. So I have had to renumber the original source list based on a new alpha sort.

That means, that all the "source codes" have to be changed. In most, but not all, cases, the new number will be two digits higher than the original number.

I want to do this was a mass "find/replace" - but before I do that, I'd like to set up a duplicate "source code" field (call it fields 1a and 1b) so that when I do the mass chanage on "field 1a" I can compare it to the unchanged "field 1b" to see if there were any errors along the way. I don;t want to take the chance that there will be mistakes.

I did a test "find/replace" yesterday and think I worked out most of the kinks. I have to change the existing field definiition from its current "text" (which I need because of the commas) to "number" so that when I want to change "12" to "14" I don't also change "121" to "141".

And I know from past experience, that when I start with the number replacement, I need to start from the highest number and work backwards.

I appreciate the time you're taking to help me find an automatic solution to this issue.

In the previous outdated DB software I used, I couldn't duplicate the contents of a field across all records; I assumed that in more up-to-date software, this would be an easy function. Maybe I was wrong and this isn't a feature that many users need.

Susan

[susan siegel]

Thanks, Lee. Looks like you're an early riser.

I'm on my way out for a meeting and will look at the file when I return.

I fogot to mention (it didn't seem important) that only some of the records have source codes; there's another field I use to sort out only the relevant records. But that shouldn't affect what you did.

susan

[susan siegel]

Thanks (again), Lee.

The parsing lets me see all the "original" numbers so I can visually compare them with the revised ones I'll be substituing and hopefully pick up any errors when I do a mass "replace."

I can't seem to open up the script so that I can understand -- and learn -- what you did. So I'd very much appreciate one more posting with the actual script steps.

The script may also be helpful in my dealing with the awkward "author" I'm working with where I have multiple author names that have to be separated as well as the names of adapters. Parsing may be the solution to this field also.

I hope this solution can be of help to others.

Thanks

Susan

[susan siegel]

Thanks for your posting.

The only reason for my wanting the duplicate field was so that when I changed the original contents using the "replace" command, I could compare the two fields in a side by side comparison to spot any errors. While most of the replacements are a simple single number replacement, some are more complex and involve 2-4 numbers separated by a comma, with each number needing to be replaced.

See the last posting from Lee who parsed the field so that I can see ALL the old numbers and the new numbers side by side.

I'm not sure anyone else will ever want to duplicate the contents of a single field across all records in the file -- but if they do, hopefully this exchange may help them arrive at a solution that works for them.

Susan

[Lee Smith]

Hi Susan,

The script should be visible to you. Is the file a [color:blue]Read Only because of the movement between us, this sometimes happens.

Parsing scripts are not easy to understand when you are first starting out because they sometimes use several functions to get to the data. Basically I used the "," (comma) to find the data and then set the respective field with it.

Once I understand what needs to be done with the data now, it is quite possible that we will need a script to process it.

Review what the Script produced, and let me know else needs to be done with it.

Lee