Find between dates

[milky]

Hi all,

I have 2 date fields, StartDate and EndDate.

I would like to use these fields to use as a find for invoices by typing the start date and typing the end date and when the user tabs out of the end date a script would return invoices for only invoices between those dates.

I already have all invoices showing in a list view and would just like to shorten it.

Is there also a way to show only the current months invoices when the user first goes to this layout as the default and then let them chnge the dates as above?

Thanks in advance

Milton.

[LaRetta]

Hi Milton!

"...and when the user tabs out of the end date a script would return invoices for only invoices between those dates."

Not using a list layout. If you use a portal, it can happen automatically otherwise your Users will need to either click a button to run the script or use an event trigger. Here's what it might look like using a button (attached).

LaRetta

RangeCurrentInvoices.zip

[milky]

Thanks LaRetta,

I made the gStart and gEnd fields exactly as your example and I made the script the same as yours.

It still returns all invoices.

Do I have to do the self join thing?

I have no concept of how I can do this in a portal, I can send a copy of the db if required.

Thanks again

Milton.

[LaRetta]

Yep, Milton, you need to do the selfjoin thing because the script uses a step called Go To Related Record [ selfJoin ] instead of a find.

As to whether you can do this in a portal; I didn't explain how to do it in a portal because you indicated that you already had a list view you wanted to use. To make it work with your existing list layout, modify the GTRR ( Go To Related) script step as:

Go to Related Record [ 'the self join' showing records using 'your existing list layout' ]

If you instead want a portal, place a portal based on the selfjoin directly onto an Invoices layout. Fill in dates in the gFrom and gTo (placed above the portal) and the portal will automatically filter for you ... that is ... after you create the selfJoin.

Edited December 30, 2008 by Guest
Added portal sentence

[alex005]

Hi Milky & LaRetta,

Sorry to jump on your post but this is just what I need (with slight adaptation) the problem I have with this is the specify calculation for the gEnd my system says I have too many or not enough () in the code window.

[milky]

Alex,

This is what I did

[milky]

LaRetta,

I finally got it to work!!

What I was doing wrong was setting up 2 self join tables with one of each of your relationships instead of setting the relationships in the Edit Relationship window.

Now........ Is it possible to have in the gStart field the first day of the month DATE altomatically entered as well as TODAYS DATE automatically entered in the gEnd field?

This would automatically only show this months invoices by default.

This would be of great benefit to me.

On another matter, in another db program there used to be a selection to do an action when the user left the field or pressed enter.

Is there any thing like that in FMP?

Thanks for all your help.

Milton.

[LaRetta]

Now........ Is it possible to have in the gStart field the first day of the month DATE altomatically entered as well as TODAYS DATE automatically entered in the gEnd field?

Uhm, Milton? That's what the script called Invoices This Month does. I assume there aren't invoices for the future? If so, you would need to change the second Set Field[] (the one setting the gEnd date) to [color:blue]Get ( CurrentDate ) only.

If I'm still missing something you need, please clarify!

LaRetta :smile2:

[LaRetta]

On another matter, in another db program there used to be a selection to do an action when the user left the field or pressed enter.

Is there any thing like that in FMP?

Not yet.

And that's why I put that FILTER button there in case the User changed one of the dates.

[LaRetta]

Not Yet.

Okay now there is. See vs. 10 with event triggers just now announced here on FM Forums (although FMs website still doesn't show it - I checked before saying 'not yet.') :wink2:

Edited January 5, 2009 by Guest

[comment]

It's happening as we speak:

http://www.filemaker.com/products/fmp/script_triggers.html

[LaRetta]

ROFLMAO!

I checked before saying 'not yet.')

But I didn't check again before responding the next time! Thanks, Michael!

[milky]

Which version of 10 do you need to create stand alone or kiosk mode?

I want to upgrade but can't find any mention of which one?

Milton

[Lee Smith]

You need the Advance Edition.

Lee