Field Type And Sorting Results

[GregSzklarz]

A newbie here trying to create a database view of track and field results by 1) Field Events and 2) Track Events. Data for both types are stored in a result field in excel that I have defined in the Caspio table as a text field.  Sort works great for the Track side of data but when I get to the Field side for the discus event I'm having a problem. 

 

Example of data being imported is  102' 11.5" for one record, and let's say 99' 03" for the next record.

Since I have the field defined as text the sort put's the 99' 03" first. Any solutions without putting a leading 0 in front of the 99'?

 

Thanks  -  Greg

[GregSzklarz]

Matilda,

 

got it! I handled it on the input side, converting feet and inches to a strictly numeric value and placing in a separate field.

On the database side I sorted on the numeric field and displayed the corresponding text field.

 

Thanks for your help, it got my mind in gear.

 

Greg

[svitlanataran]

Hi Greg,

 

I would recommend you to change datatype of the field "Track Events" from "Text255" to "Number".

It will let you sort data.

 

Matilda

[svitlanataran]

Hi Greg,

 

I have one more option for you. In this case you can save "Text255" datatype of the field.

Create a calculated field and paste the following code:

CAST ((SUBSTRING([@field:YOUR_FIELD], 0, 10)) AS Integer)

 

Please, paste a name of your field instead of YOUR_FIELD. 

Sub string function returns data as number, which is required for sorting.

 

And then sort the results by the calculated field.

 

 

Please note that you should hide the field with original values. Add the following codes to the header and the footer:

This goes in header:
<style>
#target table:nth-of-type(1) td:nth-of-type(7) {display: none;}
#target table:nth-of-type(1) th:nth-of-type(7) {display: none;}

</style>

<div id="target">

 

Add this to the footer:

</div>

 

The number in table:nth-of-type(1) depends on your result page design. If your result table is the first on the result page (case when you don't have download, or sort by dropdown or has a search form), this number should be 1. If you have download or sort by, or pre-defined criteria this number should be 2. 

Number in td:nth-of-type(7) shows which field you want to hide. 1 refers to the first field, 2 second field and so on..

According to the screenshot it's the field #7

 

I hope, this helps.  

Matilda

[GregSzklarz]

Matilda,

 

option 1: changing field to a number does not work on feet(') and inches(") designators. Sort results are all over the board.

 

option 2: Too complex for me, I'm a newbie.