3 Replies Latest reply on Jul 12, 2013 9:55 AM by philmodjunk

    Value List Search in Different Table

    LeahRiley

      Title

      Value List Search in Different Table

      Post

      BACKGROUND: I have two tables.  T1 is linked to T2 (Tags) as a one to many relationship (T1 has unique values, T2 is a portal with multiple tags assigned to each T1).  

           I setup a script in order to do a checkbox search.  The checkbox part works fine - I setup a dummy field (Tags::tagFind) where the values checked get dumped into this field.  When I check some boxes, or uncheck, the value list successfully changes with the appropriate text list.  Good so far.

           Next I want to make a new find request for each value in the Tags:gTag dummy list.  I enter find mode, create a loop, and create a new find request for each entry.  This will be an OR search.  Still good so far.

      PROBLEM: The last thing I need to do is search - The catch is, my active layout is linked to T1 but the field I need to search is in T2.  I know that the default is for Find to only search T1.  I can click on Perform Find[ ] and Specify Find Requests.  From here I can chose table T2 and the appropriate field I want to be searching in.  Next I need an operator, so I click "=" to match the whole word.  However, when I click find I get the oddest found set.  It includes a few records with nothing in the searched field, and records that have no overlap with the values from the list in either table.

           I've included a screenshot of the script.  I'm very confused as to what could be happening.  Any help would be appreciated.

      Screen_shot_2013-07-11_at_9.20.17_AM.png

        • 1. Re: Value List Search in Different Table
          philmodjunk

               Why do you have find criteria in your Perform Find step that searches for all records whereTag is empty? That's the result you get when you specify a lone = as the find operator.

               If you clear the criteria in this step so that you have:

               Perform Find []

               You won't find records where Tag is empty.

               You also mention a second table (T1) but there is no reference to it anywhwere in this script, assuming Tags is the name of T2.

               The other issue that will affect your results is that if you are performing this script from a layout based on T1 (not Tags), You are finding all records in T1, that have at least one related record in T2 that match the specified find criteria. This has been known to create confusion on the part of the developer when, after the find is performed and a found set produced, portals and fields from the related table display data from all related tag records, not just those that satisfied the find criteria.

          • 2. Re: Value List Search in Different Table
            LeahRiley

                  

            Why do you have find criteria in your Perform Find step that searches for all records whereTag is empty? That's the result you get when you specify a lone = as the find operator.

            If you clear the criteria in this step so that you have:

            Perform Find []

            You won't find records where Tag is empty.

                 Okay, I can delete the criteria, but since my active table is T1 and NOT T2, it won't search in T2 - do I have that correct?  How do I make sure it searches T2?  I went ahead and removed the criteria, but the find doesn't filter any records.

                  

            The other issue that will affect your results is that if you are performing this script from a layout based on T1 (not Tags), You are finding all records in T1, that have at least one related record in T2 that match the specified find criteria

                 Perfect, that is exactly what I want.  I want to show all records in T1 which have a T2 value that satisfy the search.

                 I don't reference T1 anywhere in the script because I don't want to to anything with T1 except for show the results.  I didn't make T2 my active table layout because then my search will give repeat values of T1 in my output.

                 For example, let's say I have "BREAD" in T1 and "White, Wheat, Rye", as values in T2.  If T2 is the layout table and I search for WHITE and RYE, I will see BREAD twice!  I only want to see BREAD once.

                 Am I thinking about this correctly?  What information can I provide that would be more helpful?

                  

            • 3. Re: Value List Search in Different Table
              philmodjunk

                   Your script doesn't use the terms T1 and T2. Am I correct that "Tags" is a table occurrence for T2?

                   The script, once you have Perform Find[], should pull up all records in T1 that have at least one related record in Tags that satisfies the criteria specified in the Tags::Tag field. But since you are in find mode, the data in Tags::tagFInd will not be accessible to specify as criteria unless you have specified global storage for that field. Is it a global field?

                   When I mentioned that there was no reference to T1, I was making sure that the script is being run from a layout based on T1 as you will get the different results that you describe if you ran it on T2.

                   BTW, it occurs to me that two Go To Related records steps could be used to pull up the same found set in T1, if a relationship that matches records by the checkbox field were set up.

                   Also, I find myself questioning whether or not your relationship is set up correctly. I'd consider this relationship for managing such tags:

                   T1---<T1_T2>------T2

                   As I would expect that many records in T1 might all have the same Tag as well as a single record in T1 having many tags--which makes for a many to many relationship here.