Data formatting question

[T.J.]

I'm a newbie to FMP when it comes to using Scripts and Calculations.

I'm trying to do the following.

Lets say I have 3 records with the following data in the field

Record# FieldData

1 A

2 B

3 C

The final result is I would like to have a field that contains all the data records from the file with commas separating the data within them.

So the data within this field would look like this.

A,B,C

I've looked though the other posts and it appears I'm going to need to use a global field and a loop script. I've played with it some and have not been successful.

Any help would be greatly appeciated.

[Raybaudi]

"I've looked though the other posts and it appears I'm going to need to use a global field and a loop script."

Not with 8 .

The new release has the powerfull GetNthRecord function !

So, for your example, the calc is

GetNthRecord(field;1)&","&GetNthRecord(field;2)&","&GetNthRecord(field;3)

[Ender]

If the number of records is unknown and you don't have the Developer or Advanced version of FMP, a loop setting a global is still the best approach:

Go to Record/Request/Page [ First ]

Set Field [ gAllRecordData ; FieldData ]

Loop

Go to Record/Request/Page [ Exit after last ; Next ]

Set Field [ gAllRecordData ; gAllRecordData & ", " & FieldData ]

End Loop

[T.J.]

Thanks for your post Daniel. I don't quite understand as my data is in the same field in different records.

In your post it appears your solution would work if I were wanting to concatenate information from different fields but I want to concatenate the information from the same field in different records to a new field.

But once again I'm a real newbie when it comes to calculations and scripts so I may just need more info on how to set it up your way.

[sbg2]

I'm very curious to know why you need the data formatted this way. Does FieldData contain unique values or might a value repeat? If they might repeat how do you want the data formatted.... for example:

# Data

1 A

2 B

3 C

4 A

would you want this new field to show:

A,B,C,A or A,B,C?

For a straight loop you could do:

Set Field[MyGlobal; ""]

Show All Records

Go To Record[First]

Loop

Set Field[MyGlobal; MyGlobal & FieldData & ","]

Go To Record[Next; Exit after Last]

End Loop

Set Field[MyGlobal; Left(MyGlobal; Length(MyGlobal) -1)]

[Zero Tolerence]

Thanks for your post Daniel. I don't quite understand as my data is in the same field in different records.

In your post it appears your solution would work if I were wanting to concatenate information from different fields but I want to concatenate the information from the same field in different records to a new field.

But once again I'm a real newbie when it comes to calculations and scripts so I may just need more info on how to set it up your way.

His calc is getting the same field, just a different record of it every time, that is what the ;1 & ;2 & ;3's are for. 1st record, 2nd record, 3rd record.

[T.J.]

Thanks sbg2 & ender. You're solutions were what I was after.

In answer to sbg2's questions. In almost all instances that I can think of, the data in the FieldData records would all be unique.

I need the data formated this way to access information off of another database. The database uses a query language called ACCESS and to return the records I want from the fields in that database I have to type the query like this.

SELECT CUSTOMER WITH CUST.NUM = "A""B""C"

Often people will send me lists but in a vertical format.

A

B

C

Not A,B,C

I use a text editor to replace the commas with the double quotes.

Now with what you've shown me I can easily change the data to the way I need it.

Thank you!

[Raybaudi]

But why the same method as always ?

You own FM8 and there is this too:

script:

Set Variable [ $count ; Value: 0 ]

Loop

Set Variable [ $count ; Value: $count + 1 ]

Exit Loop If [ $count> Get ( TotalRecordCount) ]

Insert Calculated Result [AllRecordData;"""" & GetNthRecord ( fieldData ; $count ) &""""]

End Loop