Perform find using related global field
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.