how to combine separate fields into one repeating field?

[saralee]

Hi everyone! I'm asking an opposite question than the one I asked yesterday - how can I combine several separate fields into one repeating field?

When I was importing a series of research records (lab visit date, study name, study lab etc) into a new file which displays one record at a time, I found out that I need repeating fields in the original files in order to be exported as separate records.

To be short, I would like to combine fields like Date-of-Visit01, Date-of-Visit02, Date-of-Visit03... Date-of-Visit18 into one big repeating field to be named as Date-of-Visit. Is there a way to do so? Should I use calculation?

Please advice. Thank you!

[comment]

You can use a repeating calculation field, e.g. =

Extend ( GetField ( SerialIncrement ( "Date-of-Visit00" ; Get ( CalculationRepetitionNumber ) ) ) )

Then import the calculation field instead of the individual fields.

[comment]

Come to think of it, that won't work if your fields are really named "Date-of-Visit01" etc. You cannot use a hyphen (minus sign) in a field name and get away with it so easily. Either rename your fields or change the formula to =

Extend ( GetField ( SerialIncrement ( "${Date-of-Visit00}" ; Get ( CalculationRepetitionNumber ) ) ) )

[saralee]

Thanks for the follow up, comment :

Did you mean that I should create a new field and make it a calculation field using the code you provided?

Thanks!

[saralee]

I just got your second reply. Yes, I found out that too I cannot use hypen. My fields' names are actually "Date of Visit01" ...

So I created a new field named Date_of_Visit00 and defined that as a calculated repeating field using your formula. Something must have gone wrong here.. please enlighten me:) Thanks.

[comment]

Ahm, I should have mentioned that the calculation field needs to be unstored, since it references no fields.

[saralee]

Hi comment. Thanks Did you mean that I do not need to create a new field to be the calculation field? Where should I store the code you mentioned then? Thank you!

[comment]

Yes, you should create a new field of type Calculation. Enter the code in the formula box (you'll see that as soon as you create the field). Below the box, set the result type to Date and specify 18 repetitions.

[saralee]

Thanks, comment! maybe I'm getting it wrong, but it is still not working for me. I changed the field name in the code to

Extend ( GetField ( SerialIncrement ( "{Date of Visit00}" ; Get (CalculationRepetitionNumber ) ) ) )

My new field is defined as Date of Visit00, and it needs to combine all repeating fields from Date of Visit01 to Date of Visit18.

Is there anything wrong here? My defined calculation field is not showing any date.

[comment]

If your date fields are named Date of Visit01 ... Date of Visit18, then the formula should be:

Extend ( GetField ( SerialIncrement ( "Date of Visit00" ; Get ( CalculationRepetitionNumber ) ) ) )

It doesn't matter what the calculation field is named.

[saralee]

I totally got that part. Thanks Now the issue is that I still don't get separate records when I tried to import this file with newly created repeating field ("date of visit") into a new file. For some other fields like "study name" and "lab" which were created as repeating fields to begin with, I was able to separate them into individual records though.

I think the main difference is that I still have the separate fields "Date of Visit01" ... "Date of Visit18" in the layout and the calculated field "Date of Visit" is not integrated into the layout, while the other fields have repeating fields in the layout.

Hope this makes sense. Thank you! :)

[comment]

Hope this makes sense.

Not really, I am afraid. When you import, you import directly from a table. It doesn't matter which fields are on the layout. If you map the repeating calc field to a non repeating date field in the target table, and select "Splitting..." in the ensuing dialog, you will get a separate record for each non-empty repetition.

[saralee]

It makes sense to me Yea, I finally got this date field work, but another issue came up - I couldn't import another calculation field into individual separate fields.

This field is basically calculating the age of the babies, and I made a repeating cal field in the original file, but didn't know what type should the field in the new importing table should be. Should it be cal again? I tried but didn't know what to put for the cal function. Should it be text? it didn't work..

Please enlighten me. Thank you so much! :)

[comment]

The calculation's result should be the same type as the fields being collected. If it's calculating age, then it probably should be a number?

Note that the suggested formula depends on the individual fields being named "Something 01", "Something 02", etc. The data is being collected by incrementing the string "Something 00" by 1 at each repetition and getting the data from a similarly named field.

[saralee]

I got it sorted out! After a long while, I finally got all my repeating fields separate into individual records.

The next step is to link the new tables/fiels together... crossing my fingers..

Thanks a lot! ???