5 Replies Latest reply on Jan 31, 2012 9:35 AM by philmodjunk

    SCRIPTING - Omitting Records - if it's NOT EMPTY of anything text or number

    blewvelvet

      Title

      SCRIPTING - Omitting Records - if it's NOT EMPTY of anything text or number

      Post

      Ok..so logically I would think that if a field is NOT empty you would use a (TABLE:FIELD ≠ 0) NOT ZERO..to express that.

      FYI the first line item to OMIT in the attached image of the scriplt... is if the record has a year that is not 2011...it should not be found either.

      What's weird (see attached image) is that OMITTING a record that has SOMETHING in it.... it removes SOME records as a result.  About half of them that have data in that field remain. It's a text field I'm trying to determine if it's empty or not. If it's NOT EMPTY..I was hoping to OMIT it from the Loop. This definitely doesn't feel like a solid statement based on the result.

      Where am I losing my logic? I guess I think too much in English terms in words of logic..but it just doesn't do what I expect sometimes.

      Believe me..I search Google and try about 10 other variations of the script before I come here. It's always a last resort after I've spent an more than a day trying to find a solution. I started all this yesterday morning..and realized after futzing with it all morning..I'm just not getting it.

      Screen_shot_2012-01-31_at_10.59.28_AM.jpg

        • 1. Re: SCRIPTING - Omitting Records - if it's NOT EMPTY of anything text or number
          blewvelvet

          Ok..I think I found the answer..which is to NOT use the LOOP / NEXT RECORD technique...in trying to apply the logic of NOT EMPTY or ( ≠ 0 ) but to use....

          ...CONSTRAIN FOUND SET before the LOOP which removes a lot of records BEFORE applying a LOOP of an "IF" something contains something..then OMIT...

          The CONSTRAIN FOUND SET allows me to OMIT the records using a Match Phrase...because this BUS ID field start with the letter "B" as a unique ID (for business) which is in there if the CREW member is employed by a business...thus not having to provide a tax 1099 Misc Income report.

          It can be very confusing. Some scripting you would expect to be logical to OMIT records because the OPERATORS are there such as ≠ NOT EQUAL. It's misleading on FMpro's part..I think

          Using Constrain FOUND SET has OTHER Logical sets to use which is technically more efficient than trying to LOOP and OMIT. I need to remember to see if I can use the CONSTRAIN FOUND SET before I use a loop.

          I hope this helps other users who are having Looping issues. I guess a good rule is to Use the LOOP/OMIT records for only one item at a time..and use it at the end of your script after you've exhausted all your CONSTRAINS first.

          • 2. Re: SCRIPTING - Omitting Records - if it's NOT EMPTY of anything text or number
            blewvelvet

            It's funny sometimes how I find answers to my own questions..after a good night's sleep.

            It's so true how your brain is clearer the next day when you let it go at night. Problems usually do look better the next day.

            • 3. Re: SCRIPTING - Omitting Records - if it's NOT EMPTY of anything text or number
              philmodjunk

              Also putting the problem in words can help your brain produce the needed solution...

              The main trick with scripts that use omit is that your current record changes each time you omit a record and this can throw off your exit loop controls to correctly exit the loop.

              • 4. Re: SCRIPTING - Omitting Records - if it's NOT EMPTY of anything text or number
                blewvelvet

                That's why sometimes when I type these issues out here on the forum..the answer sometimes come quickly to me shortly after I hit Submit....Whenever my brain slows down and gets sidetracked on writing the question or doing dishes.....somehow the answer pops into my head. That's another thing I think that is NOT just coincidence..it happens too often to be...but when you try so hard to think..the answer NEVER comes..it's when you go get a cup of coffe and start thinking about something else...the answer friggin pops into your head..like EUREKA!...so weird how that works.

                • 5. Re: SCRIPTING - Omitting Records - if it's NOT EMPTY of anything text or number
                  philmodjunk

                  Ever read the story of Archimedes and the word "Eureka"?

                  I may not take baths or run through town naked yelling Eureka (as he did supposedly), but I've lost count of the times I've relaxed in a shower while random thoughts free associate in my brain and the solution to a Database problem pops up...