12 Replies Latest reply on Oct 31, 2011 11:00 AM by philmodjunk

    Performing a Find

    AlexXander

      Title

      Performing a Find

      Post

      Hi I am wondering how or if it is possble to make a find based on related field. For example instead of omiting the number "3" form field 1 in table A. i am looking for a way to omit "field 1 form table B that equals the number 3" from field 1 in table A .

        • 1. Re: Performing a Find
          RonCates

           Tried rereading this over and over but still can't understand the question. Maybe try using real field and table names.  Also, keep in mind that you don't omit a field, or omit a number from a field. You omit RECORDS that contain certain criteria within a field.

          • 2. Re: Performing a Find
            philmodjunk

            Maybe this example will help.

            You are a farmer and have two tables: Fields and Crops.

            They are related like this:

            Fields::CropID = Crops::CropID

            You want to find all Fields records where the related Crop records is not "Apples".

            If this is the type of find you want to perform, then yes you can do this in a script or by hand in a manual find.

            1. enter find mode
            2. Enter apples into the Crops::CropName field
            3. Select the omit option
            4. Perform the find.
            • 3. Re: Performing a Find
              AlexXander

              Thanks for the reply but I understand that this can be done manually. What Im trying to do is get where it is automatic because i have a table with over 50 fields that I do criteria finds on that are always changing.

              PhiModjunk - You seem like you are understanding what I am trying to do however I am a beginner in using filemaker and have not ventured into knowing how to use scripts. If you could explain how this could be done that would be great.

              So using a combination example to make it easier to understand: I have two tables **Combinations** and **Used**

              **Combinations** lists all the possible combinations and has the fields ID, (F)irst, (S)econd, (T)hird, F+S, S+T, F+T, S-F, T-S,.....

              **Used** has the same fields but contains what combinations has been used.

              And what I would like to do is be able to omit or find the records that are or aren't related based on what has been used. So if **Used** (F+S) equaled 16 I would like to automatically omit all records from **Combinations** where (F+S) equals 16.

              Since new combinations at constantly being used it is a hassle to repeatedly change the find values for all the field criteria’s so I want to create a way that this process can be done automatically. If this can be done by using scripts I would appreciate it if someone can walk me through on how exactly can that be done. Thank You.

               

              • 4. Re: Performing a Find
                philmodjunk

                What relationships have you defined to link Combinations to Used?

                Is it this?

                Combinations::ID = Used::ID

                If you were to perform a find on Combinations that omits all Combination records where a related record in Used has "16" in the F+S field, (Is that really a field name?) will that produce the result that you want?

                Frankly, I'm not sure that your current table structure is a good approach here, but need to better understand what you are doing to be sure...

                I'm also not sure that performing a find is really what you should do here to get the results that you want...

                • 5. Re: Performing a Find
                  AlexXander

                  Right now there are no relationships between the two tables but (Combinations::ID = Used::ID) is what I had it originally.

                  the F+S field is actually named D1, and S+T is named D2, AND SO ON. I just wanted to illustrated that they were calculated values.

                  And yes if a find on Combinations that omits all combination records where a related record in Used is 16 in the F+S (D1) Field, it will produce the results that I want. However I have muiltiple criteria's when I do a find that are set up as an and/or type of find. Such as omit record that equals FIRST=7 and SECOND=9 and THIRD=11 or F+S(D1)=16 or S+T(D2)=20 or F+T(D3)=18...

                  So finding a way where this is done automatically instead of me manually changing the find values everytime is what I am trying to do.

                  And I'm realizing that doing a find wasn't really getting me to the point where i wanted to be so I'm looking for a way to make this possible. I do not know scrips so if that is the way i need to go to accomplish what I want to do it would cool if someone can help me on that process

                  • 6. Re: Performing a Find
                    philmodjunk

                    I think you will need one or more relationships but need a more detailed picture of what you are trying to accomplish. It may be a better approach to structure one or both of these tables differently. (Glad to know that the field names aren't F+S, etc as that would make for some really cryptic expressions in your database.)

                    • 7. Re: Performing a Find
                      AlexXander

                      if a find on Combinations that omits all combination records where a related record in Used is 16 in the F+S (D1) Field, it will produce the results that I want. However I have muiltiple criteria's when I do a find that are set up as an and/or type of find. Such as omit record that equals FIRST=7 and SECOND=9 and THIRD=11 or F+S(D1)=16 or S+T(D2)=20 or F+T(D3)=18...

                      So finding a way where this is done automatically instead of me manually changing the find values everytime is what I am trying to do.

                      • 8. Re: Performing a Find
                        philmodjunk

                        This is why I am asking for more information before I make any further suggestions. Start with the big picture:

                        What are you trying to do here?

                        What do these two tables really represent. (Combinations and "used" combination values for what exactly?)

                        What is the purpose for "excluding" these values here?

                        • 9. Re: Performing a Find
                          AlexXander

                          I am trying to create a list of combinations form all possible combinations that have no/few relations to the one(s) already used. 

                          The table Combinations represents all the possible combinations that the 3 number combinations can create.

                          The Used table represents all the used combinations that have been used.

                          By excluding the related calculated values I am able to see the combinations that have the fewest relationships to the one(s) already used. This is for my project in my statistics class which I am illustrating a theory of Randomality. 

                           

                          • 10. Re: Performing a Find
                            philmodjunk

                            By excluding the related calculated values I am able to see the combinations that have the fewest relationships to the one(s) already used.

                            Actually, you would be able to see the combinations that have NO relationship as you have excluded all others.

                            Let's see how much of this I really understand at this point...

                            Each record has 3 values, F, S, & T. There are 9 additional calcualtion fields that compute all possible 2 value sums and differences that can be produced for these three values.

                            I would assume that whatever source of these three values (a psuedo random number generator) create a series of records in Used to record each time a given three values occurs.

                            Is this correct?

                            What's fuzzy here is how a record in the Used table records the fact that a value was "used". Presumably the same three values could show up more than once? Would that produce multiple records with the same values in Used?

                            Is the specific combination significant? If F+S produces 16, does that mean you need exclude records where F = 26 and S = 10? (F-S = 16).

                            • 11. Re: Performing a Find
                              AlexXander

                              Not really there, hope this helps you see what I'm doing.

                              The Combination table contain all the possible combinations like this:

                              ID=1, (F)IRST=1(S)ECOND=2, and (T)HIRD=3THENF+S(D1)=3, S+T(D2)=5, F+T(D3)=4..

                              ID=2, (F)IRST=1(S)ECOND=2, and (T)HIRD=4THENF+S(D1)=3, S+T(D2)=6, F+T(D3)=5...

                              ID=3, (F)IRST=1(S)ECOND=2, and (T)HIRD=5THENF+S(D1)=3, S+T(D2)=7, F+T(D3)=6...

                              ID=4, (F)IRST=1(S)ECOND=2, and (T)HIRD=6THENF+S(D1)=3, S+T(D2)=8, F+T(D3)=7...

                              ID=5, (F)IRST=1(S)ECOND=2, and (T)HIRD=7THENF+S(D1)=3, S+T(D2)=9, F+T(D3)=8...

                              ID=6, (F)IRST=1(S)ECOND=2, and (T)HIRD=8THENF+S(D1)=3, S+T(D2)=10, F+T(D3)=9...

                              ID=7, (F)IRST=1(S)ECOND=2, and (T)HIRD=9THENF+S(D1)=3, S+T(D2)=11, F+T(D3)=10...

                              ID=8, (F)IRST=1(S)ECOND=2, and (T)HIRD=10THENF+S(D1)=3, S+T(D2)=12, F+T(D3)=11...

                              ID=9, (F)IRST=1(S)ECOND=3, and (T)HIRD=4 THENF+S(D1)=4, S+T(D2)=7, F+T(D3)=5...

                              ...

                              ID=120, (F)IRST=8(S)ECOND=9, and (T)HIRD=10THENF+S(D1)=17, S+T(D2)=19, F+T(D3)=18...

                               

                              So If In The Used Table The Last Used record were to be:

                              ID=67, (F)IRST=3(S)ECOND=4, and (T)HIRD=7THENF+S(D1)=7, S+T(D2)=11, F+T(D3)=10...

                              Then I would like to omit the records where:

                              ID=67 from the combination table and

                              (F)IRST=3

                              (S)ECOND=4 

                              (T)HIRD=7

                              F+S(D1)=7

                              S+T(D2)=11

                              F+T(D3)=10...


                               

                              I understand that i can do this manually in Find, however when I change the Used record so does all the values, and it is a hassle to manually alter each find every time. So i want to be able to have it automatically pull the values of the used related record.

                               


                              • 12. Re: Performing a Find
                                philmodjunk

                                Apologies in advance, but I have to be absolutely clear here.

                                You want to exclude records with any one of these values or only if all of these values?

                                In other words, exclude records where

                                (F)IRST=3 OR

                                (S)ECOND=4  OR

                                (T)HIRD=7 OR

                                F+S(D1)=7 OR

                                S+T(D2)=11 OR

                                F+T(D3)=10...

                                Or do you mean:

                                 

                                (F)IRST=3 and

                                 

                                (S)ECOND=4  and

                                 

                                (T)HIRD=7 and

                                 

                                F+S(D1)=7 and

                                 

                                S+T(D2)=11 and

                                F+T(D3)=10 and

                                Why would the ID numbers match between the two tables? What would this indicate? Does matching ID numbers mean the two records should be "related"?

                                To use values in fields in a scripted find, you can use code like this:

                                Set Variable [$Criteria1 ; Value: Table::Field1]
                                Set Variable [$Criteria2 ; Value: Table::Field2]
                                Enter Find Mode [] //clear the pause check box
                                Set Field [TableToSearch::Field1 ; $Criteria1]
                                Set Field [TableToSearch::field2; $Criteria2]
                                Set error Capture [on]
                                Perform Find[]

                                This example assumes "and" search logic. For "or" search logic, you'd put a New Record/Request step between each Set Field Step and the next Set Field step so that the criteria is entered into separate requests.