Sort by start date and end date

[fksstechguy]

I am trying to create a script that will sort data for a specified period. I want to specfify a start date and end date and have the data sorted and ready for printing.

Thanks

[mz123]

I'm not sure if there's an easier way but here's my advice (This requires that the global fields be on the actual layout you are on, which means you can hide them or display them if you want to show what the time period is)

Create two global fields: "GlobStartDate" and "GlobEndDate"

In the script, show a custom dialog that asks the user for a start date and end date (if you want, before the dialog, you can set these two fields to a "default" date so that when the dialog comes up, it has dates already set, but changeable).

Script Steps:

Freeze

Copy GlobStartDate

Enter Find Mode [No Pause]

set field [ Date; ">="]

paste [do not select all] Date

Perform Find

Copy GlobEndDate

Enter Find Mode [No Pause]

Set field [Date; "<="]

paste [do not select all] Date

Constrain Found Set

Sort [No dialog] by Date

Let me know what you think.

[fksstechguy]

I inputed the data as you typed, and I can really see where this will work, it is just beyond me. Here is what I scripted It is not item for item the same as yours. I hope you can show me where I went wrong. Here is a image of what the layout I am trying to sort. The data starts in 2004 and ends toi current. I just want the data displayed for the date range I specify.

I have the hidden GlobStartDate and GlobAEndDate field as you suggested.

Thanks for the ongoing help!

FKSSTECHGUY

[mz123]

After taking a second look at it, I think this is better:

Freeze

Show Custom Dialog (just like you have it, asking for the start date and end date, which are glob fields)

Enter Find Mode [No Pause]

set field [ Date; ">=" & globStartDate]

Perform Find

Enter Find Mode [No Pause]

Set field [Date; "<=" & globEndDate]

Constrain Found Set

Sort [No dialog] by Date

This way you don't have to hide the global fields.

[fksstechguy]

I inputed the data as you typed, and I can really see where this will work, it is just beyond me. Here is what I scripted It is not item for item the same as yours. I hope you can show me where I went wrong. Here is a image of what the layout I am trying to sort. The data starts in 2004 and ends toi current. I just want the data displayed for the date range I specify.

I have the hidden GlobStartDate and GlobAEndDate field as you suggested.

Thanks for the ongoing help!

FKSSTECHGUY

[mz123]

The changes I made were with the "Paste" and "set fields" script steps. I took out the "Copy" step and made it all work through the Set Field script step.

Did you make those changes? If you did, I don't see them reflected in your last post.

Let me know! I hope it works!

Martha

[fksstechguy]

Sorry, I kept getting a page error everytime time I tried to load images, I see now that The posts have been going through. I will try the changes now!

Thanks

Edited April 6, 2006 by Guest

[mz123]

Have you attempted my second suggestion?

Remove the Copy Script Steps.

Remove the Paste Script Steps.

Change the Set Field [] steps as follows:

[color:blue]

Set Field [ Date; ">=" & globStartDate]

Set Field [ Date; "<=" & globEndDate]

[mz123]

Okay great

let me know how it goes!

[Søren Dyhr]

I took out the "Copy" step and made it all work through the Set Field script step.

I was just about to make noises, but you cought it yourself!

--sd

[fksstechguy]

We are sooooo close...

It do the sort, but only displays information from the GlobEndDate field.

Here is my script:

Freeze Window

Show Custom Dialog (just like I have it)

Enter Find Mode[]

Set Field["Date","">="&GlobStartDate"]

Enter Find Mode[]

Set Field["Date",""<="&GlobEndDate"]

Perform Find[Constarin Found Set]

Sort[No Dialog]

I removed the Preform Find in the middle of the script that you sent as it was giving me grief and The script went through we just have this other wrinkle to get through!

Thanks

Also I am working with FMP 6. I do work with FMP 7 for most, but this PC that I am working on only has 6.

Edited April 6, 2006 by Guest

[mz123]

My knowledge on 6 is limited

Maybe someone else can help?

As far as I know (and please someone correct me if i'm wrong) that middle Perform Find step is necessary to create a found set of dates greater than your global start date.

I would say put the "Perform Find" back in the script and put a pause after it. What happens?

[fksstechguy]

Eureka! I ported the db into FMP7, tweaked the script a bit and it works marvelously! Here it is:

Freeze Window

Show Custom Dialog ([color:blue]just like I have it)

Enter Find Mode[]

Set Field[Transactions::Date;">="&Transactions::GlobStartDate]

Preform Find[]

Enter Find Mode[]

Set Field[Transactions::Date;"<="&Transactions::GlobEndDate]

Constarin Found Set

Sort Records[Restore; No Dialog] [color:blue](by date)

Thanks you!

[mz123]

Glad to hear it worked!

Martha

[Ender]

Enter Find Mode[]

Set Field[Transactions::Date;">="&Transactions::GlobStartDate]

Preform Find[]

Enter Find Mode[]

Set Field[Transactions::Date;"<="&Transactions::GlobEndDate]

Constarin Found Set

This might be simpler:

Enter Find Mode []

Set Field [ Transactions::Date ; Transactions::GlobStartDate & "..."& Transactions::GlobEndDate ]

Perform Find []