• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

having trouble filtering list on calc element

skyrun

Active Member
my calc elements show correctly on the list, but filtering doesn't work on them.

looking at my tb table, the columns are not being updated.

i have calc on load = yes and only calc on save = no and store in db = yes. so not sure why the db isn't being updated when the list is displayed. (or a calc element on a list perhaps doesn't work that way?)
 
You need to set "only calc on save" to "yes" for the records to be added in database. Then your list filter will work as expected. For the existing records you need to re-open and save them. Or if you have a lot, then maybe update these with sql query.

Br,
Martin
 
seems counter-intuitive that setting only calc on save would make it calc all the time.... but ill give it a try.
don't mess with the monkey!

remember this is when it displays on a list only (ie, no form will be opened). trying to get it to update the db when the list is displayed (with all the calc elements shown).

perhaps i will have to do it in a list plugin prior to displaying the field (vs in each element as a calc).

*my ultimate goal is the make filters work on calc elements.*
 
i have calc on load = yes and only calc on save = no and store in db = yes. so not sure why the db isn't being updated when the list is displayed. (or a calc element on a list perhaps doesn't work that way?)

No, 'calc on save' doesn't work that way. The calc is only ever saved to the DB when submitting a form. The 'calc on save' option controls whether the calc is performed when rendering, or whether we just use the value from the DB. So with it set to 'yes', we don't perform any calc'ing in list view, we just use the value from the table. With it set to 'no', we re-run the calc as we render each row ... but that doesn't effect the list query that loaded the data.

The 'calc on load' only applies to AJAX calc, and controls whether the AJAX calc is kicked off during page load of a form view. Which is of very limited use, I added it essentially for my own use, for a couple of specific projects.

-- hugh
 
seems counter-intuitive that setting only calc on save would make it calc all the time.... but ill give it a try.

Well, no, that's not what it does. "Only calc on save" does what it says on the tin. Only runs the calc on save.

If you are trying to get calcs to be re-run AND STORED TO THE DATABASE prior to the list query, there is no built in way to do that. Which, if you think about it, isn't surprising. We'd have to run the list query, load and render every row, re-calc everything, save every row, then re-run the list query.

Sounds like you need a php_events list plugin, and run some onBeforeLoad code that updates your database prior to the list query running.

-- hugh
 
Which, if you think about it, isn't surprising. We'd have to run the list query, load and render every row, re-calc everything, save every row, then re-run the list query.

And actually, it'd get worse than that, if you are trying to filter on the calc. How would we know which rows to load and update? If your filter loads 'where mycalc = 1', but some of the row which will get set to 1 are currently set to 2? So the only "safe" way to do it would be to re-calc the entire table.

-- hugh
 
so bottom line, filters will not work on a calc element in the case when the calc element changes on external factors (ie changes on anything other than updating the record on the form). the only way to easily change that would be to update all of those columns each time the table is loaded (which could have performance implications).

sounds like filters use the db (ie queries) vs. using what's displayed/calced on the list (and how could they... the list data may have 100,000 rows and you're only viewing/calc-ing the first 20, and then never storing them in the db on a list (only on a form) no matter what the settings are.

in my case i have a column 'current status' that 'looks up' the current guest status in another table (to oversimplify). so if i need to filter on that column, i should figure out how to join that table and then filter on the joined element vs. doing a calc element with sql and then filtering on that.

thanks for helping me think through that.
 
so bottom line, filters will not work on a calc element in the case when the calc element changes on external factors (ie changes on anything other than updating the record on the form). the only way to easily change that would be to update all of those columns each time the table is loaded (which could have performance implications).

Correct. And as per my explanation above, it's not really reasonable to expect that this would work. It's a chicken and egg thing. How can we (or you in your own plugin code) selectively modify table data which is going to be used in a WHERE clause, with no way of knowing which rows we have to modify, because the rows haven't been modified yet?

It's not a "Fabrik thing", it's just a thing.

-- hugh
 
The only other option, if your calc elements are dependent on changes to data in other tables, is either to run form submission plugins on the forms for those lists, which update the dependent calc fields, or (if the changes only involve table data) use MySQL triggers to do the same thing.

-- hugh
 
Oh, the other option is to use pre-filtering. Either a subquery filter ...

Field: id (raw)
Condition: IN
Value: SELECT ... bah blah some query which gets a result set of ids
Type: query

... or a PHP pre-filter, which returns a list of ids.

So derive the "status" in the query itself, rather than trying to maintain a "status" element.

-- hugh
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top