4 Replies Latest reply on May 2, 2013 12:09 AM by BZ

    Perform find using related global field

    BZ

      Title

      Perform find using related global field

      Post

           I'm trying to figure out a way to perform a find of multiple values in different field.
           I made it working with one field, but I'm not sure if this is the best way of approach as I discovered it sort of by trying.
           Let's explain what I exactly want and accomplished so far.

           Have a main table and some category table related to eachother by id and parent_id. The id of the last category is related to the id in de main table where the rest of the data will be added. The category tables contain names of childs, parents, grand-parents and so on. I want to be able to perform a find on a few childrens names but with the criteria of one or a few parents names. For example. I want to find: John, Joe, Alice and Mike.
           There are more children in the database with those names, for example 8 which have the name Mike, I only want to find the Mike's which have a parent called Bob and Nate. So, toghether with the four names I want to add the find criteria Bob and Nate for the name field in the parents table.

           I hope it's clear what I want. What I accomplished (but again, not sure if it's the best way to do it) is:

           I created a table called tblSearch with a global field named gName. I made a relationship between this field and the name field in the childrens table. I made a layout based on tblSearch with the fName field and a button. With the button a script is started which does the following:

           Go to Related Record [Show only related records; From table: "main"; Using layout "main_layout

           So in my Search layout I fill the four names (using enter to seperate them) and press the button. Then the main_layout is displayed and I get all the records containing those names.
           I tried to get my second criteria (the parents names) to work, but can't get this to work.

           And what I would like, is to know if a name can't be found, with my current approach I don't get an error message in this case.

           I would really appreciate any help with this.
            

        • 1. Re: Perform find using related global field
          philmodjunk

               If I understand this correctly, you are trying to find a group of child records. In the example given do you want the listed children with Nate OR Bob as a parent or do all these child records have both Nate AND Bob listed as a parent?

               A scripted enter find mode and generate a series of find rerquests, one for each child in the list, entering any "and" criteria exactly the same way for each "child" criteria. It can then use a single perform find to find the records.

               See the looping script in this thread for a possible example. It refers to a check box field, but data in such a field is a list separated by returns just like your list so it's a fairly close match to what may work for you here.

          Scripted Find Examples

          • 2. Re: Perform find using related global field
            BZ

                 Thanks for your reply Phil !

                 I used the second looping script and this works, I get the list of children. Now adding the parents name criteria is still a problem because I would like to use AND because I would like to know if a parent name doesn't excists. Just to try I added an OR find of the parent names to the script which gives me indeed the result I would like, only the searched childrens names with these two parent names.
                 But in case a search is performed with a parent name which doesn't excist in the database, I want to know after the search which name that is.

                 I made this error message for none excisting child names, but for the parent names this doesn't work using an OR find.

            • 3. Re: Perform find using related global field
              philmodjunk

                   As I said in my last post, any And criteria would need to be entered in identical fashion into each request generated by the looping script.

                   If all records have to be children of Nate AND Bob, specify both nate and bob in each request, but with a different child.

                   If no records match the criteria specified, you will get an empty found set. You can use get ( FoundCount ) in an if step to detect this and then you can use show custom dialog to display your error message.

              • 4. Re: Perform find using related global field
                BZ

                     Thanks again Phil, I got it working.

                     While working with this I got another idea what I want to do with this find, which would be even better to work with. As it's a bit off topic I'll open a new topic for this question, because I think the way I want to do it is not the best way.

                     Anyway thanks for your time to help me solving this problem.