7 Replies Latest reply on Oct 7, 2012 8:09 AM by philmodjunk

    Perform Find doesn't. (Find, that is).

    pauhana658

      Title

      Perform Find doesn't. (Find, that is).

      Post

           I'm at wit's end (and beginning to suspect that it was a pretty tiny wit to begin with). Hope someone can share a bit of their's.

           Got a couple of tables -- Clients and Invoices -- related via Client Name. Once a month, I need to send out a monthly invoice for all of the delivery notices I issued for a client. My assumption is that I could use a script attached to a button on the Cilent Details form. The script would perform a find: all records in Invoices with ClientName identical to the Clients::ClientName and with delivery notice dates within the monthly period. 

           It ain't working. Or I should say, everything is working except the Perform Find script step. And I can't figure out why. i've looked through the FileMaker Pro 12 documentation and The Missing Manual, but nothing I've read (to the extent of my limited understanding) indicates what I'm doing wrong.  Here are a couple of screenshots of the procedure and Find criteria.

           Thanks!

      Screen_Shot_2012-10-04_at_21.24.21.jpg

        • 1. Re: Perform Find doesn't. (Find, that is).
          philmodjunk

               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.

          • 2. Re: Perform Find doesn't. (Find, that is).
            pauhana658

                 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!

            • 3. Re: Perform Find doesn't. (Find, that is).
              Sorbsbuster

                   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.

              • 4. Re: Perform Find doesn't. (Find, that is).
                pauhana658

                     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!

                • 5. Re: Perform Find doesn't. (Find, that is).
                  philmodjunk

                       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
                       Perform Find[]

                       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.)

                  • 6. Re: Perform Find doesn't. (Find, that is).
                    pauhana658

                         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. 

                    • 7. Re: Perform Find doesn't. (Find, that is).
                      philmodjunk

                           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.