9 Replies Latest reply on Apr 10, 2009 4:13 PM by philmodjunk

    Trouble with JOIN

    KingsPawn

      Title

      Trouble with JOIN & FIND Does not give me ALL of the records I think it should

      Post

      Newbie

      FM9 Pro Advanced

      Microsoft XP

        

      I have two tables:

       

      Table1:

      FIELD-A (RangeName)  

      There may be many of these each with a unique value.

      FIELD-B (ShootName)  

      This field will appear many times for each occurrence of FIELD-A.

      FIELD-C (Number)        

      This field will appear many times for each occurrence of FIELD-B.

      FIELD-X(TeamName)

      This field will appear many times for each occurrence of FIELD-C.

       
       

      Table2:

      FIELD-A (RangeName)  

      There may be many of these each with a unique value.

      FIELD-B (ShootName)  

      This field will appear many times for each occurrence of FIELD-A.

      FIELD-D (Gun)  

      There will be four occurrences of this field for each occurrence of FIELD-B.

      FIELD-E (Yardage)            Containing either “100” or “200”

      There will be two occurrences of this field for each occurrence of FIELD-D.

      FIELD-F (Number)        

      This field will appear many times for each occurrence of FIELD-E.

      FIELD-G (Score)

      There will be 1 occurrences of this field for each occurrence of FIELD-F.

       
       

      There are other fields in each of these tables but they do not effect/affect this exercise.

       

      I have JOINED table1 and table2 together by fields A,B,C of table1 with fields A,B,F of table2.

       

      I do the following after loading Global variables with data.

       

      "Go to Layout[“LayOutName”(Table1)]

      Show All Records

      Enter FindMode[]

      Set Field[Table1::Field-A;$$RangeName]

      Set Field[Table1::Field-B;$$ShootName]

      Set Field[Table1::Field-C,$$TeamName]

       

      Set Field[Table2::Field-A;$$Gun

      Perform Find []]"

       

      If I now look at the FOUND SET I will see a record for each Table1 record that contains matching $$RangeName, $$ShootName and $$TeamName.

       

      I only see records that match the Table2 records containing Table2 FIELD-E with a value of “100”.

      I know for a fact that there are Both 100 & 200 FIELD-E records for all FIELD-D records.

       

      If I remove:

      Set Field[Table2::Field-A;$$Gun

      I get the same results.

       

      Is my JOIN incorrect or does the problem have to do with my FIND?

       

      Lost as usual, I remain

       

      KingsPawn:smileyvery-happy:

        

        • 2. Re: Trouble with JOIN & FIND Does not give me ALL of the records I think it should
          KingsPawn
            

          It does have a valid value: 

          There are four value at this time and there are :

          “10 1/2 Lb”

          “13 1/2 Lb”

          “Heavy Varmit”

          “Light Varmit”

          The value when I was doing my testing was “10 1/2 Lb”

           I get only the 100 yard records and would expect both 100 and 200 yard records.:smileysad:

           

          KingsPawn:smileyvery-happy:

          • 3. Re: Trouble with JOIN & FIND Does not give me ALL of the records I think it should
            philmodjunk
              

            Shouldn't Set Field[Table2::Field-A;$$Gun] be changed to set field [Table2::Field-D] ?

             

            Field D appears to be the field where you record the type of gun.

            • 4. Re: Trouble with JOIN & FIND Does not give me ALL of the records I think it should
              KingsPawn
                

              PhilModJunk,

               

              I made a typo in my example it should have been: 

               

              Set Field[Table2::Field-D;$$Gun not Set Field[Table2::Field-A;$$Gun the code in my program is typed correctly and so I think I should be getting both the 100 and 200 yardage records for $$Gun which is (10 1/2 Lb). But since I am not then I must be doing something wrong.

               

              I have tried to use the insert command to give you the exact code but cannot get it to work. I cannot Cut&Paste anything from FM to a note pad or and where outside of FM.

              • 5. Re: Trouble with JOIN & FIND Does not give me ALL of the records I think it should
                philmodjunk
                  

                Ok, we've ruled out the obvious. Now we can check for things that aren't obvious.

                 

                You may have tried the following, but your responses will help direct our investigation here.

                 

                Try performing the find without using a script. Enter find mode, type in the values and click Find. Do you get the same results? That will tell us if there's any problem with your script.

                 

                Try doing your find just on table 2. Put your range name, shoot name and number criteria into the matching fields of table 2. Does that work?

                 

                Pull up a table view of table 2 and check that fields A, B and F have values that correctly match A, B and C of Table 1.

                 

                If all else fails, recover the file and test the recovered file. If the recovered file works and the original file does not, you have a corrupted index.

                 

                • 6. Re: Trouble with JOIN & FIND Does not give me ALL of the records I think it should
                  KingsPawn
                    

                  PhilModJunk,

                   

                   

                  OK I have done what you requested and I understand why we have to start with the obvious.

                  Is it turned on?

                  Is it plugged in?

                  I have been there done that.

                  And I will do all of the item that you suggest because sometimes that is all it takes.

                  I really appreciate all that you do for me.

                  I created two new Layouts and pointed them to those two tables.

                  I did a find and typed in the data and found just what I would expect in both cases.

                  So it looks like the File is NOT corrupt.

                   

                   

                  • 7. Re: Trouble with JOIN & FIND Does not give me ALL of the records I think it should
                    philmodjunk
                      

                    KingsPawn wrote:

                     

                    I created two new Layouts and pointed them to those two tables.

                    I did a find and typed in the data and found just what I would expect in both cases.

                    So it looks like the File is NOT corrupt.

                     


                    It also appears to indicate that you have the right data in the right fields. I know, that should be obvious, but I've seen cases where a layout's design misled users into expecting "find result A" when they got "find result B" instead.

                     

                    Next questions:

                    I interpret the relationship description from your first post as follows:

                    You have a single link between table 1 and table 2. If you double click the line linking the two tables, you see three pairs of fields, each of which use "=" as the match symbol. Correct?

                     

                    There's more than one way to place fields from two related tables on the same layout. Describe your layout design. If you have a portal and are entering some of your search criteria into a portal's fields, describe the portal and the data you are entering.

                     

                    Can you use your original layout to enter find mode, type in criteria and click find to pull up the expected records "by hand"? If you can do it by hand when you can't do it with a script, you've got a script problem.


                    • 8. Re: Trouble with JOIN & FIND Does not give me ALL of the records I think it should
                      KingsPawn
                        

                      PhilModJunk,

                       

                      When I create a Layout and point to Table1, which is JOINED to Table2, and do a manual find I get the same results that I do from my original script.

                       

                      If I create a Layout and Point to Table1 while the JOIN is broken (Meaning not JOINED) I can find all of the data that should be there.

                       

                      If I create a Layout and Point to Table2 while the JOIN is broken I can find all of the data that should be there.

                       

                      It is only giving me a problem when the two tables are JOINED.

                       

                      Now what I am looking for is for every record found in Table1 I am looking for both a 100-yard record and a 200-yard record.

                       

                      OK there it is.

                       

                      Thanks.

                       

                      I need to point my layout to Table2 with it JOINED to Table1. Then for every record found in Table2 (100-yard & 200-Yard) I will find one record in Table1.

                       

                      It is a very small thing but that is the way we learn. We just need someone that knows just a little so when we tell them about a problem we have to think just a little.

                       

                      It is sure nice to have a wall to bounce things off of.

                       

                      Thanks and go have a Pizza on me.

                       

                      KingsPawn

                      • 9. Re: Trouble with JOIN & FIND Does not give me ALL of the records I think it should
                        philmodjunk
                          

                        "Thanks and go have a Pizza on me."

                         

                        Only if you're paying :smileywink:

                         

                        Have a great easter.