5 Replies Latest reply on Jun 11, 2012 12:18 PM by philmodjunk

    Help with a relationship, and PatternCount Field or something like.....

    MarcMcCall

      Title

      Help with a relationship, and PatternCount Field or something like.....

      Post

      Hey guys,

       

      I have 2 tables i'm working with in this question.  T15_Products, and EVO_vs_United.  At the moment they have no relationship in the graph.  What I would like to achieve is a field that if the value of T15_Products::manufactures_part_number is somewhere in EVO_vs_United::Evo_description or EVO_vs_United::United_description it displays T15_Products::manufactures_part_number. 

       

      EVO_vs_United::Evo_description and EVO_vs_United::United_description only contain a maximum of about 125 chars and are on 1 line of text.

        • 1. Re: Help with a relationship, and PatternCount Field or something like.....
          philmodjunk

          You really need a part number field in EVO_vs_United instead of trying to match to a substring "somewhere in one of two text fields". That's just plain inefficient even if you could define a relationship that works.

          Here is one option I can think of, but their usefulness in a database are limited:

          Use:

          T15_Products X EVO_vs_United

          Then put a portal to EVO_vs_United on your T15_Products based layout with this script trigger:

          PatternCount ( EVO_vs_United::Evo_description ; T15_Products::manufactures_part_number ) or PatternCount ( EVO_vs_United::United_description ; T15_Products::manufactures_part_number)

          That works for displaying matching records, but may, depending on your data and the format of a part number, match to records that it shouldn't.

          I suggest producing a calculation field that parses a part number out of these two description fields and using it in your relationship to match by part numbers.

          • 2. Re: Help with a relationship, and PatternCount Field or something like.....
            MarcMcCall

            I figured this was going to be somewhet limited.  I have 47,029 record in the EVO_vs_United table, and 18,201 record in the T15_Products table.  The 2 tables are from different product cataloges, and what I am trying to achieve is locating the records in the EVO_vs_United table that the T15_products::manufactures_part_number appears in the descriptions of the EVO_vs_United::EVO_description and EVO_vs_United::United_description so that I can make and XLS export of them and import it into our website with a field that disables them, so I do not have parts from the United catalog matching parts that we manufacture in house.

             

            I have the portal set up, and it appears to be working, actually fairly well.  No what I need to do, is figure out how to write a script that will go through all of the records, go to the portal, and set a checkbox field, that way I can go back to the EVO_vs_United based layout, and perform a find on that checkbox, and use that to make my export file for the website.

             

            • 3. Re: Help with a relationship, and PatternCount Field or something like.....
              philmodjunk

              As long as this is a single user situations (two users doing this at the same time would interfere with each others "check box" selections and you'd have to use a more sophisticated method to select records.

              I wouldn't use any relationship nor a filtered portal for this--except maybe to make it easier to montor the results produced.

              I'd use a script that performs a find on the description field to find all records that contain the part number in a specified description field. Then Replace Field Contents can be used to set a value in a field to "mark" all the records thus found.

              • 4. Re: Help with a relationship, and PatternCount Field or something like.....
                MarcMcCall

                It's only a single user file.  I wont have to worry about anyone else.  

                Why do you say not to use a filtered portal for this?

                That is what I set up, it is a little slow, but the results so far are right on the $$ as being accurate.  The nice thing about the United catalog, is it is very specific to having the OEM # in the description so when searches are made on the web store, the first thing used to return results is the text within the description, next to the actual sku # being directly searched for.

                • 5. Re: Help with a relationship, and PatternCount Field or something like.....
                  philmodjunk

                  It's generally better not to have a script interact with a portal in order to modify data instead, pull the data up in a found set on a layout based on the portal's table. What I describe also requires using a much simpler script.