What is it supposed to accomplish (other than the obvious sorted found set)?
What is it actually accomplishing?
In other words, what is the problem?
It is supposed to find all the publications that have a delinquency on them (i.e. they were late on the deadline). The script is supposed to pull all of these in and put them into a separate layout so I can see all of the delinquencies at once.
So, I have a schedule with multiple dates on it that the editors need to meet (Schedule). The editors input a date in which the task was completed (that is the Prelim ECS Due_Determine Theme Topic Actual date) into a separte field on another database (Routing). These two databases are related. I need the script to pull in on what deadline the editors were late getting their material to the next person. So I have all deadlines on the script but I only put one on the example for you otherwise it would take forever to type it out (but if I need to do that I can...copy and paste wasn't working).
These delinquencies are supposed to be brought in for only the current date...so only if someone was late today then it would come up.
Could it not be working because there were no delinquencies??? Just wondering...and I just thought of that. How could I test that?
Try doing the same find manually and see what happens.
Keep in mind that these three lines:
Set Field [ROUTING by Product Code:: Prelim ECS Actual; "="]
Set Field [Publications by Product Code Calc:: Product Type; "Ad; Bind-in; Cat; Cat Kit; Cat Insert; Cat Leaflet; Mag-S; S-S; S-P; eLetter; ePattern; H-C; H-W"
Set Field [Publications by Product Code Calc:: OnSale Date; "≥"& Get(CurrentDate)]
are searching related tables via two different table occurrences. (Routing by Product Code and Publications by Product Code Calc)
You are telling filemaker, "find all the records in 6-Schedule that have related records in these tables matching this criteria"
In my experience the first line of the above set fields, using an "=" to search for an empty field in a related table does not work reliably if it's possible that no such related record exists. I have to search for the opposite and specify the omit option.
Leave this line out and then add this code at the end of specifying your find requests:
Set Field [ROUTING by Product Code:: Prelim ECS Actual; "*"]
Omit Record is the equivalent of specifying an "omit" request when performing finds by hand if you are in find mode.
All right, I've attempted to fix the script with Phil's solutin. I'm getting a "No matches" pop-up so I don't know if that means that it didn't work or if there are no delinquencies. Is there a way to test it to make sure it is working? If not, then I will wait until there is a day in which I know for sure there is a delinquency. Thanks again.
"Is there a way to test it to make sure it is working?"
Yes, visually examine your records and see if they any should be found that are not. You could even save a clone of your file and enter some test data so that you have just a small set of records where you know you have at least one record that should be found.
You can also suppress the "no matches" dialog by including Set Error capture [on] just before your Perform Find script step if you want. (It's often less confusing to your user if you use this step and then use If [ Get ( FoundCount ) = 0 ] and Show Custom dialog to give the user a more informative message than the standard "no Matches" message.)
Ok, I finally got it to work. I had to switch some things around but your suggestion with doing a find with "*" and then omitting the record definitely helped.