Go to the invoices layout, then perform the find. Performing your find on the Clients layout simply finds all clients with at least one related record in invoices with the specified criteria.
Thanks for the great suggestion. Inserting a "Go to Layout" above the Perform Find DID allow me to further isolate the find issue. I modified the criteria for the Perform Find to find just the invoices related to this client. I then added a Constrain Found Set step with just the date criteria. Disabling the Constrain Found Set step allows the full Monthly Report script to complete successfully, albeit with an incorrect selection of invoices. But enabling the Constrain Found Set step produces error #500, which I've finally learned means...
I can now categorically state will 100% confidence that the issue is related to the dates. But how? Here are the date criteria I use for the Constrain Found Set step: Invoices::Nohin date: [$STartDate…$EndDate]
And here is a screenshot of the Data Viewer. Everything SEEMS ok, although the date order for all the date fields and variables is Japanese style. Could that be why the data criteria don't work?
Any ideas? Thanks!
Why do you find all the clients, then constrain the set to limit it to the dat range? Why do you not just include the date range in the find criteria?
To isolate if the date format is an issue you could do some testing with hard-coded date criteria in the search.
Actually, That's what I did initially. In fact, the original criteria searched only for the date, as the proper set of invoices should already be selected by the relation between the client table and the invoice table. (I have a portal on the client details layout that shows the related invoices for that client.) When that didn't work, I added criteria for the client name. But that didn't work, either. PhilModJunk suggested adding a Go To Layout step before the Perform Find, and, initially, that didn't work. So I separated the find criteria into Perform Find step for the client invoices and a Constrain Found Set step for the dates. The former seemed to work fine (at least the "found" set of invoices was properly related to the client), but the Constrain Found Set step for the dates resulted in the aforementioned error code 500 and no records in the found set.
Therefore, I am isolating the date search criteria to find out why they don't work. But you make a good point about using some hard-coded date criteria to test the date format. I'll try that. Thanks!
Stored find requests cannot evaluate expressions. For that reason as well as to produce a script where I can see what criteria was used without having to open an additional dialog box, I script it in this fashion:
#Use Set variable steps here to load the two variables with dates
Enter Find Mode  ---> clear the pause check box
Set Field [YourTable::Date Field ; $STartDate & "…" & $EndDate]
#use additional set field steps to specify any additional criteria for the find here
Set Error Capture [on] ---> keeps "records not found" dialog from interrupting script if no records are found
Note that if the user is entering dates into two fields for $Startdate and $EndDate and then your script is copying the data to the variables, I'd make the two date fields global date fields and then use them in place of the variables in theset field step. (field must have global storage specified in order for this to work.)
Yep, that worked. Thanks! I can now put Billings Pro away for good and go full-time with this FileMaker Pro database. Just one niggly minor issue to go...but that's another posting.
Still puzzed by why my method wouldn't work. I did try hard-coding some dates as suggested by Sorbsbuster, and THAT worke (thanks, Sorbsbuster!) so the problem is probably a conflict between system date formats and FileMaker date formats. Might want to peruse the Japanese FileMaker forums to see what they have to say about this.
And I hear you on dialog boxes in FileMaker Pro -- I seem to spend much of my coding/debugging time just clicking through modal dialogs or searching the list of script steps for the right step to insert. Often wish I could just key-click an object and start typing code, a là 4D.
As I stated earlier, a stored find request cannot evaluate expressions. It can only contain literal values or just the name of a variable.
Run your script with the stored find request, then select Modify Last Find to see what the criteria actually looks like. I predict that you'll see the names of the two variables and the ... instead of the two dates separated by the range operator.