5 Replies Latest reply on Jan 13, 2013 3:14 AM by TimDietrich

    case statement based on text string in field

    pfroelicher

      I have a table with purchase orders called PV and another with line items called pv_itens.

       

      These pv_itens get counted into "stock"o "on hand" when the status of the purchase order is "5"= received or "6"= received/payed or "4" = received in quarantine

      To implement this business logic i created a field called "quantity_into_stock" with the following formula:

       

      Case ( PV::pv_status_id=4

      or PV::pv_status_id=5

      or PV::pv_status_id=6 ;

      quantidade;0)

       

      Which works.

      Now I need to implement the following. In line itens there is a field "pv_itens_cfop" with values like "5902"or "6902"or "5124/6902"which are brazilian tax codes. Whenever the string *902, like in 6902 or 5902 shows up in this field the quantity_into_stock field should also be "0".

       

      I tried to add

       

      "and pv_itens_cfop ≠ 902" in the Case statement, but it would not let me.

      Also

      "and pvi_itens_cfop ≠ "*902"

       

      and Patterncount( pv_itens_cfop; 902)

       

      Did not bring results.

      What I am looking for is a simple calculation on the pv_itens_cfop filed which is FALSE when the string 902 shows up in the field.

      This calc with an OR in the Case statement would switch the quantities calculated into stocks to zero.

      Thanks for your help and hints.

        • 1. Re: case statement based on text string in field
          TimDietrich

          You might be able to use this modified case statement:

           

          Case ( 

            Right (pv_itens_cfop; 3) = "903"; 0;

            PV::pv_status_id = 4; quantidade;

            PV::pv_status_id = 5; quantidade

            PV::pv_status_id = 6; quantidade;

            0)

           

          Hope this helps!

           

          -- Tim

          • 2. Re: case statement based on text string in field
            pfroelicher

            Tim.

            thanks for the reply

            It is the string "902" which I have to identify. In the field there are entries like 7902, 5902 but also double entries like 5124/5902 that should trigger a FALSE too.

             

            Also, but this might be only a detail. My Case statment has "or" operators. Because it is TRUE for each of the conditions.

            The condition for the check of "902"in the pv_items_cfop field I would probalby put in with an AND statment, because even if received the items would not could into stock.

            Yours

            Pierre

            • 3. Re: case statement based on text string in field
              TimDietrich

              Pierre --

               

              Ok - i didn't realize that your "PatternCountpv_itens_cfop" might includes multiple values.

               

              In that case, this should work:

               

              Case ( 

              PatternCount (pv_itens_cfop; "902"); 0;

              PV::pv_status_id = 4; quantidade;

              PV::pv_status_id = 5; quantidade

              PV::pv_status_id = 6; quantidade;

              0)

               

              The Case will return the first true statement expression that it comes across. By placing the PatternCount expression first, it will return "0" if "902" appears in the pv_itens_cfop field. Then it will check the "pv_status_id" values, returning "quantidade" for values 4, 5, or 6. And if none of those values are found, it will return 0.

               

              Note that you could also use this:

               

              Case ( 

              PatternCount (pv_itens_cfop; "902"); 0;

              (PV::pv_status_id = 4 or PV::pv_status_id = 5 or PV::pv_status_id = 6); quantidade;

              0)

               

              -- Tim

              • 4. Re: case statement based on text string in field
                pfroelicher

                Yes Tim,

                thanks a lot. This was what I was looking for. I was near but somenhow fouled it up with the  "or, or, or, and" structure.

                Yours is better.

                Thanks a lot

                Pierre

                • 5. Re: case statement based on text string in field
                  TimDietrich

                  You're welcome! Glad I could be of help.

                   

                  Tim