Method Community

I have a listing of all open Purchase Order Lines, listing Qty Ordered & Qty Received. I'm trying to set a filter to display only rows where the Qty Received value is less than the Quantity Ordered. It's simply not working at all. I've also tried filtering only listings where the Qty Ordered != Qty Received, and it's still doing absolutely nothing. They are both integer values (obviously), and are also both standard fields for the PurchaseOrderLine table.

As a workaround, I added a YesNo column for "Is Received" and added it as a new table field, then created a filter only to display rows where the checkbox is set to NO. The problem with this is that it requires manual input, where I'm thinking that it should easily be able to filter results based on Qty Received < Qty Ordered.

To further the problem, sometimes when I enter a filter for LESS THAN or GREATER THAN (tried both sides of this from both angles that make sense, obviously), the first page shows, and even shows around 192 results, but when I try to click to the next page, no results are coming up. Nothing.

What am I doing wrong?

Hi,

When going through a grid, using the filter views to compare two grid values may not work as you won't be able to specify which grid value to compare.  Instead, I have another approach for you.

What you could do is create a button that will calculate the difference between the QTY Ordered and the QTY Received and insert that value into a field in the PurchaseOrderLine table.  You simply loop through the grid, calculate the difference using the "Basic Math Calculation" action (QTY ordered - QTY Received), saving to an action result.  You can then use an "Update field in Table" action to insert the action result into the table.  You can then create a filter that will display only the rows with a positive value for that new field.  This way, not only can you see exactly the quantity you are short, but can also update the difference with the simple click of a button.

As for the paging issue, is this paging within the grid or a dropdown?  Only on the custom screen or elsewhere also?

- Adam

Hey Adam,

I was afraid it might be something like that... totally understand. So, what I'm getting is that we need to create an action sequence that runs on a set schedule, or on button press, that would insert these values into the appropriate table column/row, then create a simple filter based on the results.

Thanks for your help!

Charles