case statement based on text string in field

Discussion created by pfroelicher on Jan 12, 2013
Latest reply on Jan 13, 2013 by TimDietrich

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 ;



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.


"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.