4 Replies Latest reply on Feb 19, 2011 10:35 AM by johnhorner

    Looping Through Related Records Without Using/Showing 2nd Window



      Looping Through Related Records Without Using/Showing 2nd Window


      There are several instances in my database where i need to update a complete set of related records.

      For example, on an invoicing layout, i have a global search field for products (INVOICES::g_ProductLookup) to allow users to search for any products in the database whose name, product code, or description "match" the contents of this field.  An "on object save" script trigger opens a new window, goes to a blank layout based on the products table, and performs a find on the name, product code, and description fields.  A looping routine marks any found records as "found" (by setting PRODUCTS_Found field to "1").  The script closes the window and you are back on the invoicing layout where you started and a portal displays all the found product records.  This all happens fairly quickly (depending on the number of found records), but even so you can still see the products window (whose window size is set to zero) flash in the upper left corner of the already open invoices window.

      I have 2 related questions:

      Is there a better way to do this that does not require opening a second window?

      If not, is there a way to hide the second window while this type of script runs?

        • 1. Re: Looping Through Related Records Without Using/Showing 2nd Window

          All sounds too complicated.

          Why not just have a relationship from the global field to the products table, and show the portal directly there?  Is it only because you want it to match across 3 fields (name, code, description)?

          If that is the case you can create as the foreign key a field made up of the contents of those 3 fields, and match the global value to that.  If you want to match every word in the decription field, you could create it as:

          FieldToMatchTo = ProductCode & "¶" & ProductName & "¶" & Substitute ( ProductDescription ; " " ; "¶" )


          = List ( ProductCode ; ProductName; Substitute ( ProductDescription ; " "  ; "¶" ) )

          • 2. Re: Looping Through Related Records Without Using/Showing 2nd Window

            Thanks for your post!  Unless I am misunderstanding your solution, won't a relationship only show items that are an exact match for the search criteria?  meaning, that if someone types "WID" into the global search field, a relationship such as you propose, will not find a product with a code, or name, or description that is "WIDGET".  Or if someone enters "10", it will not find records containing "110", or "1010" for a product code.  Partial words will not work nor will whole words that are part of a sentence.  For example "FILM" will not relate to records whose description is "COLOR FILM".  So a find is the only way to return all the records of potential interest.  Does that make sense... or am I missing something?

            But apart from this particular example, I have other instances where I need to modify a larger set of related records so I am still interested in finding a solution that does not require a 2nd window or can hide it from the user while the script runs.  Any thoughts?

            • 3. Re: Looping Through Related Records Without Using/Showing 2nd Window

              You are right - the match will require 'widget'.  It's hard to jump to the definitive answer when you can't see what the range of options are in the data (and whether, for example, the user wants to find exact matches only).

              A variation on the theme is where we have a global field on top of a portal, where the user starts to type a person's name and the portal gradually reduces its list as they type more letters.  That is achieved by matching the global search field to a calculation of the name field, so the matching field looks like:

              NameToMatch =

              Left (NameFirst ; 1) & "¶" & Left (NameFirst ; 2) & "¶" & Left (NameFirst ; 3) & "¶" & Left (NameFirst ; 4) & "¶" & Left (NameSecond ; 1) &  "¶" & Left  (NameSecond ; 1) & "¶" & Left  (NameSecond ; 2) & "¶" & Left  (NameSecond ; 3) & "¶" & Left  (NameSecond ; 4)

              This would let them type up to the first four letters of either name and it will show in the list.  It all depends how many variations of words you could have, because if it is limited you could have a pop up generated from every word in the fields - but obviously this could get unmanageable quickly.

              The match I described will match 'Colour Film' if the user enters 'film'.

              The search as you describe won't find '110' for '10' either.  (But could if you further manage the search criterion in the script.)

              If all you are concerned about is the flashing window, why not start the script with a freeze window, simply go to another layout (rather than a new window), perform the search, check for no found set, 'Replace Field Contents... on your '1' field, return to your original layout, and display the portal the way you are now?  My experience of the 'Freeze window' is that it does what it says.

              One of the characteristics of your method that you must accommodate is not only the finding of the current matching set, but removing the tag from the previous found set.  When you run the script do you always go and find all the records already tagged and remove that tag, then do your find and mark the new found set?

              Or you could go to the Product Table, enter find mode, set the FieldToMatchTo value to be the the global search term, perform the search, and simply display the results in list view.  No portal or relationship needed.

              • 4. Re: Looping Through Related Records Without Using/Showing 2nd Window

                i appreciate your suggestions about other ways to make the relationship work.  that's a clever solution, although, as you mention, in this particular application it might get out of hand because the description field is usually a sentence of about 10 words or so (plus the other fields).  however, that technique will help me solve another issue and be a useful new tool for me in the future... thanks!

                you are right about not finding "110"... i wasn't thinking clearly, but as you say i could manage that issue with a ittle more scripting.

                finally, i had no idea about "freeze window" or how it worked.  that's awesome!  that does the trick as far as the pop up window thing and making the modification of related records a more seemless operation (it also seems to run a little faster without opening up the other windows... althoug it could just be my imagination?).  Anyway, there are about a dozen places in my database where this will help.  THANK YOU!