6 Replies Latest reply on Mar 3, 2014 2:44 PM by DevlinDean

    Find query



      Find query


           I have a parent table named Clients and a child table named Invoices.

           Some clients have multiple invoices, some have only one, and others have no invoices yet.

           How can I generate a found set in the table Clients that includes only those who have at least one invoice ?



        • 1. Re: Find query

               So if your match fields were to look like this:

               Clients::__pkClientID = Invoices::_fkClientID

               then you can enter find mode on the clients layout and put an asterisk (*) in the Invoices::_fkClientID field and perform your find. This will find all clients records with at least one related Invoices record. (The asterisk could be placed in any field in Invoices that is never empty and you'd get the same results.)

          • 2. Re: Find query

                 Thank you for the answer.

                 This works, but if I were to include all of this in a script, how would it translate?



            • 3. Re: Find query

                   Pretty much exactly as I described it.

                   Go to Layout ["Clients" (Clients) ] ---> not be needed if you are always on the Clients layout when you perform this script
                   Enter Find Mode [] ---> clear the pause check box
                   Set Field [ Invoices::_fkClientID ; "*" ]
                   Set Error Capture [on]
                   Perform Find []

                   For more examples of scripted finds: Scripted Find Examples

              • 4. Re: Find query

                     I'm having problems having the script to actually do something: 

                     In the 'Set field' part

                      Do I specify a target field or only the calculated result option ?

                     In the calculated result,  I have to replace  ;''*''   with   =''*''       or I get a warning '' An operator is needed''

                Is this OK ?

                     Then, the Perform Find step:     Do I specify a Find request or leave this empty? 

                     For the novice, many possible options !!


                • 5. Re: Find query

                       When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                       Thus you need to click both buttons and you enter just "*" as the calculated result.

                       You do not specify find criteria in Enter Find Mode. You do not specify find criteria in Perform Find. The brackets should show as Empty just as I put them in the example script.

                       Note:  specifying criteria in Perform Find can override criteria specified in the preceding set field steps. While that won't be the case for Enter Find Mode, in both cases, the criteria you might specify as part of these two steps is hidden from view. If you later review your script, you can't see the criteria specified until you open yet another window to see the criteria. By using set field steps instead, all of the criteria used in the find is immediately visible when you review the script n the script editor.

                  • 6. Re: Find query

                         Thank you so much.

                         Now it works fine.  This simple technique will be very useful.