Both a looping script and the calculation option of Replace Field Contents could do this. In Replace Field Contents, you'd set up a case function to look at each alternate field in succession, returning the value of that field if it is not empty.
philmodjunk has the answer for you. I prefer to use the looping script as I can run it through the debugger for the first few records to confirm it works as expected before it runs on the entire table.
Thanks for the reply. This morning came back to sort my fields by primary, secondary, tertiary, etc. To elaborate I added Leading counts to each of the duplicated fields. So now I can see my three fields of Gender (G1, G2, G3) and know that G1 has 15000 values, G2 has 3000 values, and G3 has 300 values.
Then I use the logic: I want to copy from secondary field to primary field. If Primary is empty, and there is a a value in Secondary, copy that over. In the end I wan to progressively fill in empty values in Primary from all others if there are any.
Of course this is my thought and it doesn't help I am not great at coding. So off I went to the forums to piecemeal things I thought went together. Found some other forum subjects that were similar in some respects and of course you helped in many!!
Here is what I have so far in steps:
1. Enter Browse
2. Go to my layout with records I am working with
3. Show all records
4. Go to beginning
5. Begin a LOOP
6. Set Variable to secondary field (of the two I am reviewing the one with fewer values)
7. Run IF [IsEmpty] on my Primary Field
8. If it is, then Set Field to contents of Variable above
9. Go to next record
10. End if
11. End Loop
Okay I definitely know things are out of whack just wondering if anyone wanted to help before I got back to thinking about it. One of those things where it took me a couple of hours to find more info in forums and build the logic behind it. Now I have to figure out the correct syntax to have to of through each record finding empties and copying values from smaller set.
And... will I get errors? I am thinking what if both fields are blank? At this point I want to keep going in the script and ignore that as data entry being at fault and not much I can do now.
Why not use replace field contents?
Show All records
Replace Field Contents
Might be all that you need.
You can set up a calculation option with a case function that looks like this:
Case ( Not IsEmpty ( MainField ) ; MainField ;
Not IsEmpty ( Field2 ) ; Field2 ;
Not IsEmpty ( Field3 ) ; Field 3 ;
and so forth for however many fields that you have
This will leave MainField unchanged if it has data. If not, Field2 will be copied over, if Field 2 is empty, then Field 3 will be copied over and so forth....
Just put your field names in place of mine in this function.
philmodjunk.....Thanks for that last explanation. Just got in and made copy of the database and ran the script per your instructions. Eureka!! Tried it on field with just two columns (City_01 & City_02). Before leaving work Monday and posting my example above this was these were the fields I was trying to work with and confirmed that City_02 only had two values missing from City_01 so anything I ran would be easy to check at end.
Sure enough this morning used your case calculation and got the +2 count of values on City_01 at end of script run.
Thanks you for help!!