Converting text to date format in repeating fields

[Kirsten]

Well, I'm stumped. I am trying to convert a text month to it's numeric equivalent, then concatenate that number with the day number and year, and convert the whole text string to a date, in date format. The script works fine for the first iteration of the repeating field (there are 10 repetitions), but I cannot get it to work for any other! Suggestions anyone?

Here is the first iteration of the script. The other iterations are identical, except the Loop Count is set to 2, then 3, etc. And the the Set Field is matched to the appropriate repetition.

Go to Record/Request/Page [First]

Set Field ["Loop Count",1]

Loop

Set Field ["Month Numeric r",

"Case(

GetRepetition(Entry Month Text r, Loop Count)="Jan",1,

GetRepetition(Entry Month Text r, Loop Count)="Feb",2,

GetRepetition(Entry Month Text r, Loop Count)="Mar",3,

GetRepetition(Entry Month Text r, Loop Count)="Apr",4,

GetRepetition(Entry Month Text r, Loop Count)="May",5,

GetRepetition(Entry Month Text r, Loop Count)="Jun",6,

GetRepetition(Entry Month Text r, Loop Count)="Jul",7,

GetRepetition(Entry Month Text r, Loop Count)="Aug",8,

GetRepetition(Entry Month Text r, Loop Count)="Sep",9,

GetRepetition(Entry Month Text r, Loop Count)="Oct",10,

GetRepetition(Entry Month Text r, Loop Count)="Nov",11,

GetRepetition(Entry Month Text r, Loop Count)="Dec",12)

Set Field ["Date Calculation r",

"TextToDate (

NumToText(GetRepetition(Month Numeric r,Loop Count)) & "/" &

NumToText(GetRepeition(Entry Day r,Loop Count)) & "/" &

If(GetRepetition( Month Numeric r, Loop Count) > 8, "2005", "2006"))"

Go to Record/Request/Page {Exit after last, Next[

End Loop

Thanks in advance,

Kirsten Masse

[comment]

I am not sure what you mean by "iteration". When you use Set Field[] with a repeating field as the target, you have to specify explicitly which repetition you want to set. You cannot use a calculation to determine this.

Why don't you use a repeating calculation field to get the result, something like:

Date(

(Position("janfebmaraprmayjunjulaugsepoctnovdec", EntryMonthR, 1, 1) + 2 ) / 3 ,

EntryDayR,

Year(Status(CurrentDate) ) +

( (Position("janfebmaraprmayjunjulaugsepoctnovdec", EntryMonthR, 1, 1) + 2 ) / 3 > 8 )

)

On a more general level: Is there a special reason to use repeating fields for data entry? It seems like you are spending a considerable effort to solve a problem which shouldn't exist in the first place.

[Kirsten]

Unfortunately, I'm dealing with a database already in existance in the repeating field format.

Thanks for the Position suggestion. I'll give that a try. As for specifying the specific iteration for SetField, yes, I did that. The posting I provided is only the first of 10 almost identical segments. The only difference is that the the Loop Count is incremented by one, and the SetField is set to the appropriate instance of the repeating field. (Should have used "instance" instead of "iteration" above.)

Kirsten Masse