7 Replies Latest reply on Feb 16, 2010 2:24 PM by philmodjunk

    Linking Tables

    gsbilsky

      Title

      Linking Tables

      Post

      This is my last attempt.

       

      I challenge any of you to the following:

       

      Build two table with two fields each.  This should take 3 minutes.

       

      Table One: FieldOne FieledTwo

       

      Table Two:  FieldOne  FieldTwo

       

      Table One has the following data:

       

      FieldOne                                                           FieldTwo is empty

       

      CHECKCARD 0124 APL*ITUNES 23458 2223       

      CHECKCARD 0222 APL*ITUNES 33233 3322       

      COOK OUT FREEDOM DRPS                                

      CHECKCARD 0111 PAT OBRIANS BAR NOLA

       

       

      Table Two has the following data:

       

      FieldOne                                                        FieldTwo

      APL*ITUNES                                                   Apple Itunes

      COOK OUT                                                     Cook Out

      PAT OBRIANS                                                 Pat O'Brians

       

      Ok, now link TableOne FieldOne with TableTwo FieldOne and replace all of TableOne FieldTwo with data from TableTwo FieldTwo.

       

      Simple with SQL in Access.  Can anyone write a script to do this?

       

      I want to thank all who have helped me get close, but not over the edge.

       

      If this can not be done I will be staying with Access.

        • 1. Re: Linking Tables
          comment_1
            

          gsbilsky wrote:

           

          now link TableOne FieldOne with TableTwo FieldOne


          That's not a good idea, since the data does not match. You should first define a calculation field in Table One to extract the matching part, and use this as the matchfield to Table Two. Unfortunately, your example doesn't make it clear which part/s need to be used. If "COOK OUT FREEDOM DRPS" should match "COOK OUT", should it also match "ROBIN COOK", or "FREE WILLY"?

           

           

          On a more general note: this appears to be a merely a symptom of the real issue - namely, keeping multiple facts in a single field.


          • 2. Re: Linking Tables
            philmodjunk
              

            In your sample data, each row represents a separate record correct?

             

            You don't give the logic behind how you extract a label from the downloaded data. It appears that text starting with CHECKCARD should return words 3 and 4. All other entries appear to return the first two words of the string.

             

            If those assumptions are correct, then define a calculation in table 1, cKey as: Case ( LeftWords (RawText ; 1 ) = "Checkcard" ; MiddleWords(RawText ; 3 ; 2 ) ; LeftWords (RawText ; 2 ) )

             

            Link your two tables in the relationship graph as:

            Table1::cKey = Table2::field1

             

            Now use your field tool to place Table2::field 2 on your table 1 layout (no need for a field 2 at all in table 1)

             

            done--but only if I guessed correctly here.

            • 3. Re: Linking Tables
              gsbilsky
                 I did not give a lot of data.  But I have a few hundred records and the extract does not always show in word two or three.  I could be the first, second, third or last word I want.  I look at TableOne FieldOne and type into TableTwo FieldOne the word/s to make the unique link and type in TableTwo FieldTwo what I want to see.
              • 4. Re: Linking Tables
                comment_1
                  

                gsbilsky wrote:
                I could be the first, second, third or last word I want. 

                According to your example, it could also be TWO words - and you haven't answered my question. Unless you make the rules clear, you're asking for clairvoyance.


                • 5. Re: Linking Tables
                  gsbilsky
                    

                  It is very simple.  TableOne has a FieldOne with a long string of data.  TableTwo has FieldOne with short string of Data I want to use to match with the first. After matching I want to use the data from TableTwo FieldTwo.

                   

                  Here is a SQL statement from Access that may help

                   

                  <!-- [if gte mso 9]><xml> <o:DocumentProperties>   <o:Template>Normal</o:Template>   <o:Revision>0</o:Revision>   <o:TotalTime>0</o:TotalTime>   <o:Pages>1</o:Pages>   <o:Words>36</o:Words>   <o:Characters>209</o:Characters>   <o:Lines>1</o:Lines>   <o:Paragraphs>1</o:Paragraphs>   <o:CharactersWithSpaces>256</o:CharactersWithSpaces>   <o:Version>11.1287</o:Version> </o:DocumentProperties> <o:OfficeDocumentSettings>   <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif] --><!-- [if gte mso 9]><xml> <w:WordDocument>   <w:Zoom>0</w:Zoom>   <w:DoNotShowRevisions/>   <w:DoNotPrintRevisions/>   <w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery>   <w:DisplayVerticalDrawingGridEvery>0</w:DisplayVerticalDrawingGridEvery>   <w:UseMarginsForDrawingGridOrigin/> </w:WordDocument> </xml><![endif] --><!-- /* Font Definitions */ @font-face      {font-family:"Times New Roman";      panose-1:0 2 2 6 3 5 4 5 2 3;      mso-font-charset:0;      mso-generic-font-family:auto;      mso-font-pitch:variable;      mso-font-signature:50331648 0 0 0 1 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:"";      margin:0in;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:"Times New Roman";} table.MsoNormalTable      {mso-style-parent:"";      font-size:10.0pt;      font-family:"Times New Roman";} @page Section1      {size:8.5in 11.0in;      margin:1.0in 1.25in 1.0in 1.25in;      mso-header-margin:.5in;      mso-footer-margin:.5in;      mso-paper-source:0;} div.Section1      {page:Section1;} --><!-- StartFragment -->

                  UPDATE tbl_BankData INNER JOIN tbl_ReportPayeesWCats ON [tbl_BankData].[bd_Payee] Like "*" & [tbl_ReportPayeesWCats].[P_Where] & "*" SET tbl_BankData.bd_ReportPayee = [tbl_ReportPayeesWCats].[p_Name]

                  WHERE ((([tbl_BankData].[bd_ReportPayee]) Is Null));

                  <!-- EndFragment --> 

                   

                  • 6. Re: Linking Tables
                    comment_1
                      

                    I'm afraid I am not familiar enough with SQL to understand what you mean. I am guessing you want to match a "short string" record to ANY "long string" record that contains the short string within the long one?

                     

                    If so, you will need to use a custom function to "explode" the long string; for example, the string "alphabet", constrained to a minimum length of 3 characters, would be exploded to:

                     

                    alp
                    lph
                    pha
                    hab
                    abe
                    bet
                    alph
                    lpha
                    phab
                    habe
                    abet
                    alpha
                    lphab
                    phabe
                    habet
                    alphab
                    lphabe
                    phabet
                    alphabe
                    lphabet
                    alphabet









                    • 7. Re: Linking Tables
                      philmodjunk
                        

                      The sticking point and whole focus of the issue in this thread is the expression: like * & FieldName & *. It means "match any record where the field contains the text stored in FieldName".

                       

                      There isn't a filemaker equivalent for this partial string matching that can be used within a relationship. I've been trying to figure out a work around that still enables you to build a such a list of partial string matches, but so far, the only way I can figure out is through a script of nested loops that loops through each imported record and tests it against each entry in table 2. That's not a terribly efficient way to do this--especially if you are importing large numbers of records.

                       

                      Hmmm, maybe this would work or it'll fire some one else's ideas toward a solution....

                       

                      You could loop through table 2 in a script that does this

                       

                      Loop

                        Set variable [$Pattern ; Table2::Field1]

                        Set variable [$label ; Table2::Field2]

                        Go To Layout [Table 1]

                        Enter find mode[]

                        Set error capture [on]

                        Set field [table1::Field1 ; "*" & $Pattern & "*"]

                        Perform Find[]

                        Replace Field Contents [no dialog; Table1::Field2; $Label]

                        Go To layout [original layout]

                        Go To Record [next; Exit after last]

                      End Loop