New user to Phocas. In database design, I’m trying to exclude from my invoice revenue stream a certain item code (NON-INVENTORY ORDER ENTRIES) only where the item shows on a certain line item no. (999) from invoices. This is dealing with a data import problem. I can add each criteria separately but I cannot figure out how to join them “AND” so that it only filters when both are true: NON-INVENTORY ORDERS & LINE NO. 999. Does anyone have any suggestions?
One possible solution would be to create Transform columns which flags both instances, assuming that you have a column which identifies Non-Inventory Orders, and one which has the product_id 999 in it. Then you are able to use that transform column as a filter on the data sheet in Design.
The added transform column would look something similar to this:
case when [non_inventory_order] = 1 and [product_id] = 999 then 1 else 0 end
1 Like
Thank you so much! I’m not familiar with that function, but I will try to experiment and see what happens and let you know.
1 Like
It worked. Here was my expression:
case when [invoice_line_no] = 999 AND [item_cd] = ‘NON-INVENTORY ORDER ENTRIES’ then 1 else 0 end
Then, I filtered out all the results I was trying to exclude. Thanks again for unlocking some additional magic!
1 Like