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

    Linking Tables



      Linking Tables


      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                                




      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

          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

            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
                 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

                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

                  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

                    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:



                    • 7. Re: Linking Tables

                      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



                        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