filter a calc element

[paulknox]

I'm trying... unsucessfully to filter a calc element in my table. I have a par level and a stock level. The par level is manually entered and the stock level is a calc element

$db =& JFactory::getDBO();
$query1 = "SELECT COUNT(*) FROM `iol` WHERE `lens_model` = '{stock_levels___lens_model}' AND `used` <> 'Yes' AND `used` <> 'Expired' AND `department` ='BCH' AND `iol_power` = '{stock_levels___iol_power}' ";
$db->setQuery($query1);
$query1 = $db->loadResult();

$result= $query1;
return number_format($result, 2);

the code above is working and returning the correct numbers... however... I would like a pre-filter on the menu that would allow me to only display entries where the stock level is lower than the par level.

I've tried with a menu pre-filter and I'm wondering if its because the calc element is working on the fly? I've even tried url filetering without luck..

index.php?option=com_fabrik&view=list&listid=2&clearfilters=1&stock_levels___current_stock[value][]=>5

displays below and it should only be displying results greater or equal to 5

Thoughts would be appreciated!

 

[troester]

I'm wondering if its because the calc element is working on the fly?

Yes. Filter is working with values from DB but as long as you didn't save a record with a calc element the calculated value is not in the DB.

 

[cheesegrits]

Well, even if all the records have been saved and the calcs run and stored, if that query is referencing another table to pull in stock levels, the value from the calc when the record was saved will be just that - the count of stock at the time the record was saved. Not when the list is being filtered some time later, when the stock count in the other table may have changed.

I think the only way you could filter on that would be to build the query that counts the stock into the pre-filter, rather than doing it in the calc. Or maybe as well as, so you have the stock count visible in your list (if set to "Calc on Save Only: No" it'll recalc when the filtered list is displayed). But unfortunately, helping figure that query out is beyond Commnity support's paygrade.

The only other option would be to build a MySQL view, which selects everything from the first table, and constructs the stock level field. Build a list on that, use that for your filtering, and add custom add/view/edit links to point to the "real" list. But you'd still need to figure out the query to do the counting.

-- hugh