Creating specific "Find" searches

[Orchid28]

I'm a little lost as to how I would go about creating scripts in the "Find Mode" layout. For an example, I have a field called "Start Date" and another field called "End Date". Instead of typing in dates I want to be able to type "oldest date" in "Find Mode" in the fields "Start Date" and "End Date" and when I press enter it will search for the oldest date to lets say year 2005 then as I click onto the next record it will increase, like an ascending order from year 2005-2011.

Where or how do I go about starting this script?

[Vaughan]

It sounds as though you need to sort the found set of results by start date.

[Orchid28]

Where do I go about doing that? Do I need to create a script for that? My company wants to specifically enter in "oldest date" and the oldest date will pop up and from there as we click through it, it will go from oldest to newest and vice vers with "newest date".

[bcooney]

You cannot literally enter "oldest" date into a date field in Find mode. However, as Vaughan suggests, you can sort records by date ascending, and the first record will be the "oldest" date.

[LaRetta]

Hi Orchid28,

If this is your first script, it might look something like this:

Freeze Window

Go To Layout [ list layout ]

Show All Records

Sort [ on date field ; ascending order ]

Go To Record/Request/Page [ first ]

:smile:

[Orchid28]

Okay yeah that makes more sense. My boss wanted to literally enter "oldest date" so I thought it was possible. But I try that script out and see how it goes, thank you all!

[LaRetta]

You can name the button (attached to this script) 'Oldest Date'. We can show Users whatever we want; that doesn't mean that's exactly what happens on the back-end to accomplish it.

If you want to go to the oldest record and then go down the list to newest, as you've indicated, then you need to display all records and not just the oldest. A find would produce a found set of only some records so you wouldn't be able to move up the list to newer records.

Understanding the purpose of this particular User request would help, i.e. what is the User attempting to accomplish during this task?

[Orchid28]

^User is trying to find a quicker and efficient way to find records and one of them is searching by date

[LaRetta]

My company wants to specifically enter in "oldest date" and the oldest date will pop up and from there as we click through it, it will go from oldest to newest and vice vers with "newest date".

Finding only the oldest date (which would give you a found set of only that date) and clicking through records from oldest to newest (and vice versa) are two different actions and require very different scripts, as we have explained.

Have you tried the script I suggested? You may want to remove the 'Show All Records' so it only sorts the existing record set. I still cannot envision the User entry needs here, sorry.

[Orchid28]

^Yeah I tried the script and it didnt work When I put in 2010, which is the oldest date, it only shows dates from 2010...Not from 2010-2013. And it doesn't go by order so when I'm clicking through the 2010 dates it will show (7/2010, 10/2010, 6/2010 etc.) so the months are random and does not go in ascending order.

So like you said I am looking for a script that can allow users to click through records starting from the oldest date to the newest date. Sorry if I wasn't clear enough!

[LaRetta]

It is defining the USABILITY that we were lacking. Let's walk through this:

A User will perform a find by typing 2010 into the StartDate and 2013 into the EndDate. With the resulting record set, sort the records ascending on StartDate and place the User at the first ('oldest') record.

If the User clicks 'Newest' then what should happen? It would be the same found set. Should the list be sorted in descending order and put the User on the first record so that, as they scroll down, the dates would get older?

Hang in there ... we'll get this nailed down. :twitch:

[LaRetta]

One more question ... are StartDate and EndDate global fields? What is the name of the date field being searched and sorted?

[Orchid28]

Okay so I have 2 fields, one is StartDate the second is EndDate. The user won't be inputting the dates in StartDate and EndDate at the same time. If the user wants to look at the start date they will input the "oldest" date in start date ONLY and leave the EndDate blank with no searches.

I'm thinking of including both ascending and descending order so if the user just wants to start with the "newest date" they can do that instead of always starting with the "oldest date". It would probably be better if there are more options to finding the dates.

A little more information about my database:

Each record I have has a different project that my company needs to address or accomplish. Each record has a Start Date of when my company started the project and an EndDate of when my company finished the project. So what my coworkers are looking for is an easier way to search for the StartDates and EndDates. And what they want is to look for the oldest or newest dates. So when they look at the oldest date it will start from 2010 and they can continue to click till they get to the newest date, 2013 and vice versa.

EDIT: Addressing your question above.

So far all I have is

Field Name: Start Date Type: Date

Field Name: End Date Type: Date

and no they are not in global fields...i did global fields and it deleted all my data and when I tried to input all the dates back in again it automatically inputs 6/2010 for every record which is totally wrong.

[LaRetta]

Let's see if this gets you there ... I might go into more detail than necessary because I see no Skill Level on your profile. Create the following pseudo-script and attach it to a button on the User layout (back up first):

Enter Find Mode [ pause ]

Modify Last Find

If [ not Isempty ( Table::StartDate ) ]

Set Field [ Table::StartDate ; Table::StartDate & "..." & Date ( 12 ; 31 ; 4000 ) ]

Set Field [ Table::EndDate ; "" ]

Else

Set Field [ Table::EndDate ; Table::EndDate & "..." & Date ( 12 ; 31 ; 4000 ) ]

Set Field [ Table::StartDate ; "" ]

End If

Set Error Capture [ On ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ "No records found" ]

Else

Sort [ Restore ; no dialog ] ... sort ascending on StartDate

End If ... ooops - I accidently left this line off!

Go To Record/Request/Page [ first ] ... I left this off too. I guess that's why I called it pseudo-script since I typed it from memory (and I guess my memory is at the beach today! LOL!

UPDATE: I corrected the portion in blue. And red.

[Orchid28]

Okay great! and yes I have NO skill level what so ever lol but I am learning and I thank this forum and you for helping me through my first database!

I will try this out! thanks.

[Orchid28]

^^lol ok got your corrections...I guess its time for a vacation!