Jump to content

Quick find for date ranges


 Share

This topic is 5665 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
spelled quote wrong!
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5665 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.