12 Replies Latest reply on Nov 5, 2009 6:18 PM by CountryBoy1

    Programmed Find on Related Field

    CountryBoy1

      Title

      Programmed Find on Related Field

      Post

      Can a program set a field in Find Mode if the field is a related field ?  Have a table

      in which the field is a related field to a field in a master table.  I can manually type in a value

      and the Find will search OK.  However, am unable to get a program to enter anything

      into the related field.  Have tried several functions, all to no avail.  (However, I CAN get
      a program to set a field in the master table and do a Find search.)

        Thanks for your help. CountryBoy1

       

        • 1. Re: Programmed Find on Related Field
          philmodjunk
            

          Yes, you can use a script to enter search criteria in a field from a related table just like any other field. If you use set field, the related field does not even have to be present on the current layout.

           

          Keep in mind that the results of the find when criteria is entered in related fields is different than entering data into fields from the layout's table occurrence. This is true of manual and scripted finds. If you enter "apple" in the related field, while the current layout refers to a TO named "farms", you are telling Filemaker "find all the farms records that have a related record with "apple" in the related field." This difference, sometimes confuses people.

          • 2. Re: Programmed Find on Related Field
            CountryBoy1
              

            Dear PhilModJunk:

                   Thanks for your reply.  I have tried everything.  I even created a numeric

             field that has no relationship and still can't get the Find mode to accept a

             value that is inserted with a program.  It always says "no valid criteria".

             I have tried changing the field to a text field; have tried using the "Set Field",

             the "Insert Calculated Result" functions, even Paste, and nothing will work.

             If you have any ideas, it would be appreciated.  Sincerely, CountryBoy1

             

             

            • 3. Re: Programmed Find on Related Field
              philmodjunk
                

              Can you perform the search manually?

               

              The Insert and Paste script steps only work if the field is physically present on the current layout. Set Field is not limited to just the fields on the layout.

               

              Create a layout that shows all the fields you need for your criteria.

              Set up your script but leave out the perform find step so the script enteres find mode, enters the criteria and quits. Then run the script and inspect the fields to see if you are getting the criteria entered into the fields you expect to have entered. Then perform the find by hand.

               

              • 4. Re: Programmed Find on Related Field
                CountryBoy1
                  

                Dear PhilModJunk:

                    I can always get the Find to work if I Pause and then

                 type the value in manually.  I can Pause and look

                 at the value that the program puts in, and the value

                 looks correct, but when I tell it to Continue, then I

                 get the error.

                    There is another table, which is the master table,

                 that I can get a program to place values into and the

                 Find works OK.

                     Sincerely, CountryBoy1

                 

                • 5. Re: Programmed Find on Related Field
                  mrvodka
                     Post your script.
                  • 6. Re: Programmed Find on Related Field
                    CountryBoy1
                      

                    Dear Mr_Vodka:

                          Here is some code.  As you can see, I tried several things.  The YearxB is a global field

                          in the header that can be set with a drop-down menu.  The YearxB1 is the field on which

                          I want the Find to work on.  Both YearxB and YearxB1 are numeric fields although I have

                          even tried changing YearxB1 to a text field but it didn't make it work.  All of these methods

                          place a numeric value into the YearxB1 field in Find mode, but when Find executes, it always

                          gives the "no valid criteria.." error message.  Thanks for your help.  CountryBoy1

                       

                           Enter Find Mode[]

                            # Method 1

                           // Go to Field[Graves1::YearxB]

                           // Copy [Select]

                           // Go to Field [Graves1::YearxB1]

                           // Clear [Select]

                           // Paste [Select]

                           #  Method 2

                           // Set Variable [$var1; Value: Graves1::YearxB]

                           // Set Field [Graves1::YearxB1; $var1]

                           # Method 3

                           // Insert Calculated Result [Select; Graves1:YearxB1; GetAsText(Graves1::YearxB)]

                            # Method 4

                           //  Insert Calculated Result [Select; Graves1::YearxB1; Graves1::YearxB]

                           #

                              Pause/Resume Script [Indefinitely]

                              Perform Find[Restore]

                     

                    • 7. Re: Programmed Find on Related Field
                      philmodjunk
                        

                      I don't see how any of those will enter valid criteria. When you enter find mode, it's like you're now working with a blank record. No data in fields other than global fields are available to you. You have to capture the criteria in global fields or variables before you enter find mode.

                       

                      Set Variable [$var1; Value: Graves1::YearxB]

                      Enter Find mode[]

                      Set Field [Graves1::YearxB1; $var1]

                      Perform Find[]

                       

                      Or

                      Enter Find Mode[]

                      Set Field [Graves1::YearxB1; YourTable::YourGlobalField]

                      Perform Find[]

                       

                       

                      • 8. Re: Programmed Find on Related Field
                        CountryBoy1
                          

                        Dear PhilModJunk:

                             Thanks for your reply.  I do beg to differ about

                         reading global values from header in Find mode.

                         I have another program that does just that for a

                         similar table and it works.  Even so, I did move

                         my read statement of the global before the Enter

                         Find mode, and it made no difference.

                         Still frustrated.  CountryBoy1

                         

                         

                         

                         

                        • 9. Re: Programmed Find on Related Field
                          philmodjunk
                            

                          It will work. I use this method all the time with absolutely no trouble. You'll need to work out exactly why it's not. The most obvious thing to check is whether the field is truly set to have global storage in field options.

                           

                          PS. I said nothing about placing the global field in the header. It can be located anywhere on your layout. :smileywink:

                          • 10. Re: Programmed Find on Related Field
                            CountryBoy1
                              

                            Dear PhilModJunk:

                                     Nothing works with my table.  I made a little stand-alone file

                             with just four fields: Index, Counter, MonthNo, and Year.  Also has

                             a global named Yearx in the header.  Have a little program for finding

                             years that reads the Yearx global prior to entering Find mode.  It sticks

                             the value into the Year field and does a Find.  Everything works fine.

                             

                                     My actual program is quite complicated.  It has three windows

                             with layouts, all related, and each window has about three different layout versions.

                             The table that is giving problems has 73 different fields.  Of course

                             not all of the fields are shown in the same layout, but this reveals

                             the complexity.  I had some nice little drop-down inputs in the header

                             that the user could pick a month and year for a Find, but it's never going

                             to work. It always gives the "no valid criteria" error.  The user will just have
                             to go into Find mode and manually enter the year and month.

                             

                                     Thanks for your help.  CountryBoy1

                             

                            • 11. Re: Programmed Find on Related Field
                              philmodjunk
                                 If it works for the simple test DB, it should also work for your more complex DB. If you continue to investigate the differences between your two files, you should be able to get this to work.
                              • 12. Re: Programmed Find on Related Field
                                CountryBoy1
                                  

                                Dear PhilModJunk:

                                              Strange Things: If I use "Constrain Found Set " instead

                                          of "Perform Find", I can actually do a Find on  the data IF the field has no

                                          relationship to a field in another table. I can assign a variable $var1= "> 2008"

                                          and have it inserted into the field and it works.  However this assignment will

                                          NOT work if the field is related to another field in another table.

                                             If I code just "2008" into the related field, it will do a Find for a

                                          "Constrain Found Set" but it does NOT show the "2008" value in the field

                                          prior to the Find execution. Very strange.

                                             I can work around my problem by copying the field that is related

                                          into a field that has no relationship.  Then perform a "Constrain Found Set"

                                          on the field with no relationship.  Not a good solution but may have to use it.

                                             CountryBoy1