9 Replies Latest reply on Jun 3, 2014 9:45 AM by philmodjunk

    Script to omit records where text field contains

    ElenaBerzins

      Title

      Script to omit records where text field contains

      Post

           I have a script that produces invoices so it has multiple find/omit criteria that all work fine except for one. How do I omit records where a text field (B) contains a certain text that it gets from another text field (A)?

           The originating field (A) is created text via a calculation too so not static either.

           I've tried separating out this particular Omit from my others in the find statement so that its standalone in Perform Find, followed by the rest in Constrain Set, but still the first part doesn't work, so I need help.

           Omit records   MessageCenter2: [mb_invoice_month]

            

           Thanks!

        • 1. Re: Script to omit records where text field contains
          philmodjunk

               Do you mean that you need to omit all of the records shown in red in the following example?

               Field A     Field B
          Apple       AppleSauce
               Apple       Red Apple
               Box          Boxes

               Ready       Everyday

               In other words, the exact text contained by Field B can be different on each record depending on what value is entered into A?

               If so, I'd set up a calculation field:

               Patterncount ( Field B ; Field A )

               And specify ">0" in the calculation as my criteria to find or omit records

          • 2. Re: Script to omit records where text field contains
            ElenaBerzins
                                                                                                                                   
                                Field A                     Field B
                                June 2014                     

                                     April 2014

                                

                                     May 2014

                                

                                     June 2014

                           

                 Field A changes monthly. When it does, the contents get pasted into Field B, adding to what's already there. So, I only want to Omit records where Field B contains "June 2014" or whatever is in the current Field A

                  

            • 3. Re: Script to omit records where text field contains
              philmodjunk

                   That matches the assumptions I used in posting my previous answer. What I suggested there should work for you.

                   But:

                   

                        the contents get pasted into Field B, adding to what's already there

                   Does not sound like the optimum design to use for this in your database. Setting up a related table with one related record for each date can be much more flexible a way to deal with such a set of dates.(And if you are using copy and paste script steps to put these dates into this field, that is also not the best way to do this.)

              • 4. Re: Script to omit records where text field contains
                philmodjunk

                     Correction, That doesn't match my original assumptions.

                     In a scripted find,

                     Set Variable [$Date ; YourTable::Field A ]
                     Enter find mode []
                     Set field [ YourTable::Field B ; "*" & $Date & "*" ]
                     Omit Records
                     Perform Find

                     will omit all records where the date in Field A of the current record is found anywhere in the list of dates in Field A.

                     My comments about less than optimum design choices still apply.

                • 5. Re: Script to omit records where text field contains
                  ElenaBerzins

                       Oh, yes, its not great design (was "inherited" that way and I haven't gotten around to changing it since its only used in this one instance).

                       Thanks!

                  • 6. Re: Script to omit records where text field contains
                    ElenaBerzins

                         Ok, I got it to work! Left my working script as it was and just added the Set Variable part before my Constrain Found Set (which omits records where Field B: [$$variable]

                          

                         Thank you!

                    • 7. Re: Script to omit records where text field contains
                      philmodjunk

                           Note: a self join relationship between Field A of your current layout's table occurrence and Field B of a second occurrence of the same table and using the ≠ operator instead of the = operator would match to all records that do not have this month, year text listed in Field B.

                           YourTable::Field A ≠ YourTable 2::Field B

                      • 8. Re: Script to omit records where text field contains
                        ElenaBerzins

                             I don't know how to use a (not equal to) symbol in the Find Requests, since is not an operator (why not??). So, I'm not sure how to do that within the same script.

                        • 9. Re: Script to omit records where text field contains
                          philmodjunk

                               I haven't suggested that you use the ≠ in a find request. I've posted that you can use that operator in a relationship that you'd set up in Manage | Database | Relationships. "table occurrences" are the "boxes" found in that window that you use to define relationships in your database. You can create multiple Tutorial: What are Table Occurrences? that all refer to the same data source table.