Perform Find and sort/omit results based on dates

I'd love a little guidance on how to perform a basic Find via a script using one date field and omitting records that have a Rejected date field and then sorting the results in descending order.

When I built the script to do this, my Find resulted in 0 records found, so that indicates I've made a mistake somewhere along the script. Basically, I have a set of company records. All have Created Date fields, a few have Rejected Date fields. I need to sort and show only records that have a Created Date field and omit records that have a Rejected Date field and vice versa. Thanks so much for any guidance anyone can provide!
_Astrid_Asked:
Who is Participating?
 
Will LovingPresidentCommented:
This is a great simple script to start with to learn the basics of FileMaker scripting. The most important part to understand is that FileMaker has two different ways of dealing with "Matching Records" when performing a Find:  "Include" and "Omit". It's important to understand the difference and to do or script a Find in the correct order.

The default Matching Records action is "Include", this means "show me all records that match my criteria". the "Omit" action does just the opposite, it means "show me all the records that DON'T match my criteria". In order to produce the desired results, you generally want to setup all the "Include" Requests first and then follow those with any "Omit" Requests.

In the case of your Created and Rejected dates you would have two Requests: Include all records with a value in Created Date, and Omit all records with a Rejected Date. To do this, go into Find mode, enter and asterisk in the Created Date field, go to Requests > New Request (or use the New Request button), change Matching Records in the Status bar to "Omit", and enter and asterisk in the Rejected Date field.

The simplest way to create a scripted Find is to first do the actual Find manually so that are getting the correct results. Then, when you go and create a Script you can use the Perform Find[ Restore ] script step and FileMaker automatically remembers and saves into that script step the last Find that was performed.

Find & Sort Script
Find & Omit saved Requests in Perform Find scrip step
The script could actually be simpler if all of your records actually do have a Created Date entry. In that case you would only need to save the "Omit" portion of the Find. Alternately, you could include a dialog which could be used to do something like "Show all records with Created Date greater than or equal to:" and then have an entry field. (slightly longer script but I'm just giving you a sense of what can be done)
0
 
_Astrid_Author Commented:
Thank you once again, Will! I wish I would have found Experts Exchange sooner. In just a few hours I learned more than I have been able to learn in the last few weeks. You've saved me from much hair-pulling!
0
 
Will LovingPresidentCommented:
By the way, the principle of "perform the operation manually first and then script it" also works for Sort, Export and some other script steps. If you do an Export or Sort first, before creating the script or adding the script step, FileMaker will automatically assume you are wanting to use the most recent Export or Sort as the template for the script step and saves those settings.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
_Astrid_Author Commented:
Thanks, Will. That's very helpful. I've been able to use that as a basis to create several searches that now function correctly!

My last conundrum is having to place two separate search results on the Welcome page of the DB. I'd like to be able to see the last three entries based on date created and the last three entries based on date closed. I've been working with portals to try and set this up with not much luck. Would you have any suggestions on how I can achieve this?
0
 
Will LovingPresidentCommented:
Two separate portals, both presumably using the same relationship, both showing only three rows and having no vertical scroll bar, and each having a separate sort order as specified in the Portal Setup. For the first one sort by Date Created and Ascending, for the second one, sort by Date Closed and Descending.

The only problem you might run into is that if your relationship potentially shows all records and you have a lot of records the sort required to show the first three might take a while. If you use a relationship that limits the potential number of records, for instance, to records within the last week or X number of days that can help restrict it. There are other ways to do it too, but that's probably the simplest.  If the number of records is not large then you shouldn't have an issue.

One other things I forgot to mention is that you can use the "Saved Finds" menu to preserve searches that you might want to do again. If you need a Find followed by a Sort then you need a script but if it's just a Find then you can save it.
0
 
_Astrid_Author Commented:
When you say "Using the same relationship: does that mean I should I be using a self-join relationship for this to work? For some reason, I keep getting the same results in both portals, just displayed differently depending on the fields in the portal. :O In fact, both portals only show one record, the same record, and nothing else.

I've gone back to review my tables and relationships and have tried a few different variations with no changes.

I'm obviously making a silly mistake somewhere.....anything in particular I should be looking out for? Thank you as always!
0
 
_Astrid_Author Commented:
I've made progress and have the portals filtering as they should now. But my only remaining issues is that the Date Created portal will also show closed records if their Date Created date is new enough to be included in the top three rows. How can I omit records that have a Date Closed value from the Date Created portal? Thank you! :)
0
 
Will LovingPresidentCommented:
You'll need to add a Filter to the Portal Setup. The filter should be:

isempty( DateClosed )
0
 
_Astrid_Author Commented:
Thank you so very much for all your help, Will! Where can I send you a special treat for helping me save so many hours of hair pulling? :)
0
 
Will LovingPresidentCommented:
Click on my profile and send me a direct message.
0
 
_Astrid_Author Commented:
And last but not least.....my portals all work.....is there a way I can have the user click on a portal row and be taken directly to that record. I tried the Go to Related Request script but I read that if that portal's table is not the related table (which in my case, it isn't), the first related record in the found set is made current. Is there a way around that?

Thank you, and happy Saturday. ;)

Astrid
0
 
Will LovingPresidentCommented:
I'm not sure what you mean by "the portal's table is not the related table"...

GTRR (Go To Related Record) should work. Give it a try. Just make sure you turn off Browse Entry for all the fields in your portal.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.