A scripted approach to this, which you could run after the perform find step, would be to sort your found set, loop through each record and compare to the record before it, and omit any that match after you have gone past 3 records. Using Variables to count and compare the records.
This is what I have come up with, a duplicate filter script with a bit of a twist:
Set Error Capture [ ON ]
Perform Find [ ... ]
If [ Get ( FoundCount ) = 0 ]
# No records found so no need to run next bit.
Sort Records [ Speciﬁed Sort Order: TABLE::ItemNumber ( Ascending ) TABLE::DateOfCertificate ( Descending) Perform without dialog ]
Go to Record/Request/Page [ First ]
Set Variable [ $DupeCount ; 0 ]
Set Variable [ $ItemNumber ; TABLE::ItemNumber ]
If [ TABLE::Field1 = $ItemNumber ]
Set Variable [ $DupeCount ; $DupeCount + 1 ]
Set Variable [ $ItemNumber ; TABLE::ItemNumber ]
Set Variable [ $DupeCount ; 1 ]
If [ $DupeCount > 3 ]
Exit Loop If [ Get ( RecordNumber ) = Get ( FoundCount ) ]
Go to Record/Request/Page [ Next ; Exit after last ]
I have not tested it over IWP but all the steps used are Web Publishing compatible so should work fine for you. Let me know if there are any problems or if you have any questions about anything.
Thank you very much for your help. How and where exactly do I enter in all of the information you have supplied? Also, do the # stand for anything and must they be included? Thanks again for your help!
These are all script steps, so can be placed after your Perform Find step.
The # are just comments, at the bottom of the list of Steps and I just put included them in the post to break up the different sections of the script. You don't have to include them but they are hand for making notes about your scripts in general for future reference.
I entered the script, but now if I do a find for "27", only one record is displayed. It should display 16 records (2 records should be omitted because five have the same item number and two should be left out because they have older "Date of Certificate" dates.
Here is what I have for the script:
What I want to be able to do with this script is this:
When performing a find, if records have the same exact item number, I want to only show the records with the three most recent "Date of Certificate".
Also, the full name of the field "Item Number" is "Item Number Auto" in Filemaker.
Again, thank you for your time and help!
Just a quick one to test while I look further into the script. Add a Go To Record/Request/Page [First] at the very end of the script and let me know if this works?
PROGRESS!!! But still one quirky thing is happening.
First here is the script:
Now, when a search is done for "27" it returns the correct number. You will see that records 7, 8 and 9 have the same item number of "27MIB". The script has omitted the two records of 27MIB. This is great
Here are the two quirky things:
It is not omitting the correct ones. Currently, in the entire database we have, there are five records with the item number of "27MIB". The "Date of Certificate" for each of these are: 12/22/2008, 12/28/2008, 1/16/2009, 1/17/2009 and 2/9/2009. When conducting the search, we only want to show the records of 27MIB with the three most recent "Date of Certificate". So we want to omit the records that have 12/22/2008 and 12/28/2008.
The other quirky thing is that when you now do a find for "27MIB", four records are shown. It is odd that when you do a find for "27" only three records are shown, but when you do a find for "27MIB" four records are shown. What is even more odd is that it omitted the second most recent "Date of Certificate".
Any thoughts? Thanks again for all of the help!
Cool, glad we are getting there.
Your first issue, check the sort order and also check if the Date of Certificate field is set as a Date field. If you can work out which ones are omitted, best thing would be to Hlat the script after the sort and view the records and if they are in the correct order, or if you are using Advanced step through with the script debugger.
Regarding the second issue, I will look into this and get back to you.
OK your second issue is with the omit and Exit steps towards to bottom of the script, replace your line 17 - 22, starting If [ $DupeCheck > 3 ] through to the next End If, and replace with teh following
If [ $DupeCount > 3 and Get ( RecordNumber ) = Get ( FoundCount ) ]
Exit Loop If [ 1 ]
Else If [ $DupeCount > 3 ]
Go to Record/Request/Page [ Next ]
This should fix the bug.
Perfect solution, now it works great! Thank you, thank you, thank you!
Quick question, when you perform a find and you enter the text in the "Item Number" field or "Item Description Field" if you hit return, it starts a new line in the field, instead of performing the find which is what I would like to have happen. Under "Field Behavior" for both fields in the "Go to next object using:" I have checked "Tab Key", "Return Key" and "Enter Key". Any ideas on this one?
Glad I could help AAgraphics
With the hitting return in IWP, I don't thing the "Field Behavior" setting for "Go to next object using:" work the same way, if at all. But have not tested this properly myself for a while. So I dont know any way around this unfortunately, sorry.
No problem, just curious. Thanks again for the help!
We have been working with the new script and we have noticed some glitches. The script is omitting records that it should be showing. If you perform a find in the item number area for "27MIB", you should see the following:
As you can see, the script does successfully filter out the records it should. There are five records with "27MIB" as the item number and it is only showing the records of 27MIB with the three most recent "Date of Certificate" and omitting the others. However, the script is also omitting another record for no reason. It is omitting a record with "27SPB" as the item number.
Below is the list from FileMaker that shows all of the records that appear when doing a find for "27" in the item number field:
Finally, below is the script we are using:
Any ideas as to why this script is randomly omitting records? If I remove the part of the script that starts with "Set Variable..." to the end, it shows every single record. Also, if you do a find on the website for "CTN" in the item number field, you should see a record with "CTN11B" as the item number, but it shows no records at all. Any thoughts as to why it is omitting some item numbers randomly? Thank you.
On line 27 of your script Go to Record/Request/Page [Next] needs to include the 'Exit After Last' option otherwise on the last record each time it is stuck in a loop until the counter goes above three, which then omits the record.
Let me know if this solves the problem.
It works!!! Thank you very much!