8 Replies Latest reply on Jul 15, 2010 9:09 AM by philmodjunk

# Is it possible to re order calculation field contents

### Title

Is it possible to re order calculation field contents

### Post

Hi

I'm using FM Pro 8 advanced and I would say an average user but I've hit a bit of a wall. One calculation returns the following content, at the moment it's ordered in alphbetical order like this:

Waterrafting 18km, Canoeing 1.5km, Dinghy sailing 1.5km, Fishing 1.5km, Football 1.5km, Golf 10km, Horse riding 5km, Kayaking 1.5km, Karting 30km, Mini golf 1.5km, Pedalos 1.5km, Quad biking 2km, Snorkeling 1.5km, Tennis 2km, Windsurfing 1.5km, Waterskiing 500m.

Is it possible to re order them to be in order of distance, like this:

Waterskiing 500m, Canoeing 1.5km, Dinghy sailing 1.5km, Fishing 1.5km, Football 1.5km, Kayaking 1.5km, Mini golf 1.5km, Pedalos 1.5km, Snorkeling 1.5km, Windsurfing 1.5km, Quad biking 2km, Tennis 2km, Horse riding 5km, Golf 10km,   Waterrafting 18km, Karting 30km.

Mark

• ###### 1. Re: Is it possible to re order calculation field contents

What calcuation expression are you using to generate this list?

• ###### 2. Re: Is it possible to re order calculation field contents

Hi Phil

This field is generated by answers to the following type of questions:

Case(not IsEmpty(Local Activities_Surfing _Distance from site _km_);"Surfing "&Local Activities_Surfing _Distance from site _km_&"km, ";"")&

Case(not IsEmpty(Local Activities_Waterrafting _Distance from site _km_);"Waterrafting "&Local Activities_Waterrafting _Distance from site _km_&"km, ";"")&

etc, etc

The red text relate to column headings in an imported excel spread sheet, hope this makes sense!

Thanks

Mark

• ###### 3. Re: Is it possible to re order calculation field contents

That still doesn't give me the whole picture here. Are the "Local_Activities..." fields data fields or calculation fields? If they are calculation fields (you indicate that they "relate" to an imported file), what are their calculations?

• ###### 4. Re: Is it possible to re order calculation field contents

Sorry Phil, data is imported into text fields from excel and then brought together into one calculation to form the list field "_Site_Activities_Canoeing_If_not_on_site_how_far_km_" will have a value of 1.5

Hope this is a little clearer.

Thanks

Mark

• ###### 5. Re: Is it possible to re order calculation field contents

Just didn't want to assume anything. It's theoretically possible to sort the calculation results they way you've currently structured your data, but it will be very messy. If you import this data into a related table with number fields for the distance, you can specify a sort order on the relationship and then build your sorted list from there.

Distance        Activity
1.5                 Canoeing
2                     Fishing

You'll need an ID field to link these records to the correct record in your current table and you'll need to define a calculation field in this table as cDistActivity: Activity & " " & Distance

Now in your main table, this calculation may work for you:

Substitute ( List ( ActivityTable::cDistActivity ; ¶ ; ", " )

• ###### 6. Re: Is it possible to re order calculation field contents

Hi Phil

Thanks for the post, I think I understand, it seems to make perfect sense, I'll give it a go and see what happens, I may need to come back to you if I get stuck.

Thanks again.

Mark

• ###### 7. Re: Is it possible to re order calculation field contents

Hi Phil, I'm running into problems.  I've created a new table with a relationship to the current table via a unique record ID, the problem is the each record could potentially have up to 19 activities and distances within one field and specifying the order on the relationship just re-orders the records rather than the contents of the field within those records.  I'm missing something but I don't know what, thanks for any further help.

Mark

• ###### 8. Re: Is it possible to re order calculation field contents

"...the relationship just re-orders the records rather than the contents of the field within those records."

That's the concept here, You re-order records to get the order you need and then use the list function to generate the sorted list of values with the substitute function replacing returns with a comma and space.