Auto-Sorting By Date on Performing a Search

[lostatsea]

When performing a search using a range of dates (where the 'date' field is actually just text) I notice that the records are not sorted by date. Is there a way to ensure that my results are automatically chronoligical by date and not have to sort them manually?

 

Thanks.

[Ender]

Use a script to perform the search followed by the sort. Maybe something like this:

 

Go to Layout[Find]

Enter Find Mode[]

Pause Script[]

Set Error Capture[On]

Perform Find[]

If[get(lasterror)=401]

Show Custom Dialog["No records found";"Ok"]

Perform Script[Find]

Exit Script

End If

Go to Layout

Sort Records[No dialog; by Date]

 

BTW: Your "Date" field should really be a date field, not text, if you want it to sort correctly.

[lostatsea]

At the moment, we are performing the search by navigating to:

1. Find Mode

2. Entering the selected search critiera in the 'Next Due Date Field'

3. Hitting 'enter'.

 

Would I have to create a button in order for the above script to work, or is there a way to initiate the script on hitting 'enter' while in the field?

 

Thanks!

 

PS. It's true, what you say about changing it to a date field, although I am worried about all of the data that exists in this field currently. It exists as: yyyy/mm/dd and I am a bit leary about changing the type and risk screwing something up.

[Ender]

Use a button on the layout and have it Perform the Find script.

 

PS. It's true, what you say about changing it to a date field, although I am worried about all of the data that exists in this field currently. It exists as: yyyy/mm/dd and I am a bit leary about changing the type and risk screwing something up.

You can convert your date text to Date format by using the text functions. Maybe something like:

 

let(

text = substitute(field;"/";" ");

date(middlewords(text;3;1); middlewords(text;2; 1); middlewords(text;1;1))

)

 

Put the dates into a new Date field first using a Replace Field Contents, then change the original field to be a Date type, and use another Replace Field Contents to move the new dates into the old field (be sure to make a backup prior to messing with this).

 

With your dates in an actual date field, it will be easier to perform calculations on them in the future. You can always display them however you wish using the Date Format options.