Quick find for date ranges

[Robert Collins]

I have a layout (viewed in list mode) that shows a date field and a customer name and details.

I would like to have a quick way to quickly find a group of customers based on the date field. For example at the moment the layout shows every record and I have set up a button to show all the records that date falls in the current month . I would like to set up a button that advances the month shown and another button that advances the year by one (I would also need a couple of buttons that decrease the month and date by one). I Have also setup a couple of drop down menus to select a month and year but I don'y know how to to link these with a find command so when I select the month and year in the dropdown menus, it finds the relative records.

I hope I've explained myself well enough , I have attached an example.

Any help would greatly be appreciated

[John Mark Osborne]

I think you might have posted the wrong file. There is no customer layout or table and one of the layouts describes a different issue.

[Robert Collins]

sorry about that , try this

[Søren Dyhr]

Well I didn't notice that it ought to be done via the customer layout, so I solved the matter in the stock view...

Here's som inspiration - at least!

--sd

exampleMOD.zip

[Robert Collins]

thanks Søren, those buttons did the trick , because will sometimes need to jump to a date quite far away i want to try a couple of pop-up boxes - one to select the month and one for the year and as soon as the selection is made, the records are shown for the month and year selected. Should I create two global fields to achieve this?

thanks again

[Søren Dyhr]

Should I create two global fields to achieve this?

Perhaps, or you should extend the array of buttons to +-6 month and +-one year.

If you should use the two popups you suggest, should you think about the lack of event triggers. A better solution would be to have arrow on both sides of the globals, to toggle up and down accordingly. We're here talking 'bout two globals holding year numbers and monthnames respectively, by these do you generate a value to set into g_Upper.

--sd

[Robert Collins]

I like the idea about having a < and > for year , also is there any way when it comes across a month that has no records , it just shows no entries instead of showing all , that way the records shown are always in sync with the month shown at the top of the screen?

[Søren Dyhr]

also is there any way when it comes across a month that has no records , it just shows no entries instead of showing all

I have a "Show All Records" in my no match clause that easily could be followed by a "Show Omitted Only".

--sd

[Robert Collins]

Thanks, I have put the "show ommited record" line into the script and it has worked great. I am really struggling to get my second set of < and > buttons to change just the year . Also when I transfered your example to my database , the date field starts at Jan 0000 and not may 2006 like your example did. I have double checked that everything is exactly the same. any ideas?

thanks for all your help on this, I do appreciate it.

[Søren Dyhr]

the date field starts at Jan 0000 and not may 2006 like your example did.

Since the autoenter options is preoccupied, is the best solution to make an initiation script, that fires each time the file is launched stuffing Get(CurentDate) in the upper range field.

Take a look at the filepreferences menu to see how to call an init'script.

Meanwhile have I made another template, with a different approach to almost the same issues you have:

http://www.fmforums.com/forum/showtopic.php?tid/176973/post/206770/#206770/

--sd

[Robert Collins]

thanks Søren,

I am going to create a script that runs when the file is opened , is it possible you can show the exact script as I just can't seem to get the grasp of this . Sorry to sound so stupid

thanks again

[Søren Dyhr]

Sure! Two lines only...

Set Field [ Stock::g_Upper; Get ( CurrentDate ) ]

Commit Records/Requests [ Skip data entry validation; No dialog ]

--sd

[Robert Collins]

that worked perfectly , thank you very very much!

[Robert Collins]

one more thing !

going back to the script you gave me that makes it possible to move up and down the months - where in this script can I put a find request so I can filter out any unwanted records? I have 4 criteria that 4 fields must meet in order to show on the list. I know how to create a script to enter find mode , insert text to field ect but wherever i put these lines they have no effect - also at the moment most of the records that I want to see are appearing , but nowhere in the script is the find criteria , so how does it know this without me telling it?

thanks again

[Søren Dyhr]

I know how to create a script to enter find mode , insert text to field ect but wherever i put these lines they have no effect

What the script I gave you makes is a found set via the GTRR, if you wish to filter this set do you need to use this:

http://www.filemaker.com/help/Script-Steps71.html

...simply because "Perform Find" ignores previous sets!

A script will go as this:

Go to Related Record [ From table: “Portal”; Using layout: “Portal” (Portal) ] [ Show only related records ]

Enter Find Mode [ ]

Set Field [ Portal::Value; "A" ]

New Record/Request

Set Field [ Portal::Value; "B" ]

Constrain Found Set [ ]

...but be carefull, the criterias imposed here is OR'ed, so records in the related record that either contain "A" or "B" in the same field. The easiest way to deal with 4 criterias which all should be fulfilled, is to make a series of Contrains. However is it a little cluncky and you ought to throw some DeMorgan after the matter when several criterias are AND found... only I have to think it through. So for the moment Contstain until you drop...

--sd

Edited May 23, 2006 by Guest
spelled quote wrong!

[Robert Collins]

does this go inside the +/- script you gave me?

I still do not understand how it is managing to find most of the criteria I require as I have not told it what to look for!

I have so much to learn!

[Søren Dyhr]

I still do not understand how it is managing to find most of the criteria I require as I have not told it what to look for!

Try to list the querries you need to impose on the found set! But yes it belongs to the +- thingy...

--sd

[Robert Collins]

Ok, after which line in my +/- script do I insert the following criteria

a field called "cancelled contract?" - this must be empty

a field called "product type" - this must be "contract"

a field called "commission" - this must be more than £35.00

these fields belong to a table called sales line items - I have one layout with the fields shown and a second layout that shows records from the sales line items table but shows different fields (the fields I want my on-screen report to see) this is triggered from a button which is in an other screen . So far my button sucessfully goes to my report layout , shows records from the current month (thanks to you!) and I have my buttons to scroll up and down though the various months . My only problem is some records are on my report due to not sucessfully being filtered.

[Søren Dyhr]

Alright your script should carry on this way:

If [ Get ( FoundCount ) ] 

    Enter Find Mode [  ] 

    Set Field [ Stock::Cacelled_Contract; "*" ] 

    Omit Record 

    Constrain Found Set [  ] 

    Enter Find Mode [  ] 

    Set Field [ Stock::Product_Type; "contract" ] 

    Set Field [ Stock::Commision; "> 35" ] 

    Constrain Found Set [  ] 

End If 

However are NULL values always bothersome for database systems, so I suggests that you redefine the field into type number and makes it autoenter 0 (zero and not NULL) and restyle your field into boolean. The toggling is then done via a oneline scripting attached as an invisible button containing:

Set Field[stock::Cacelled_Contract;Stock::Cacelled_Contract xor 1]

--sd

[Robert Collins]

thanks , I shall try this now

[Robert Collins]

I am getting really fustrated with this (my lack of knowledge , not your help) could you list the script in its entirety . By the way , to simplify the filtering process I have now created a field that has a "1" in it if it should be shown and a "0" if it should not. What I seem to be getting by inserting those lines you gave me is a Find dialog box keep popping up everytime I changed the month and no records found . I still don't know why if I leave the script as it is apart from a couple of rogue records , it works - Where is the criteria stored if it is not in the script.

Thanks again for your patience

[Søren Dyhr]

Sorry - this one is on me, you need to include "errror capture" before constraining to take care of "Nothing found"

If [ Get ( FoundCount ) ] 

    Set Error capture[ON]

    Enter Find Mode [  ] 

    Set Field [ Stock::Cacelled_Contract; "*" ]

    Omit Record 

    Constrain Found Set [  ] 

    Enter Find Mode [  ] 

    Set Field [ Stock::Product_Type; "contract" ] 

    Set Field [ Stock::Commision; "> 35" ] 

    Constrain Found Set [  ] 

    Set Error capture[OFF]

End If 

Unfortunately am I off on vacation to Barcelona tomorrow, ohterwise would I have suggested that you sended me your file, and I gave it the required tweaks. I'm back monday though.

Where is the criteria stored if it is not in the script.

What's confusing you is that first querry is done via a GTRR while the following are closer to ordinary searching by the use of Constrain. When it comes to it have I thrown you in the pools deep end, by showing some tricks instead of showing the straight forward approach.

Since you're on fm7 can't you benefit from the new and sharper ways to search in datefields. But it would probably better to make calc'fields splitting the date in year and month and make them part of the requests.

--sd

[Robert Collins]

thanks Søren, have a nice vacation