'automatically' recalculate all calc elements in list?

[chris.paschen]

I have a large list with several calc elements. I've added a new calc element to the list/table and need to recalculate all the entries using this new calc element.

The calc elements are all set to update only on save.

Is there ANY way to do this other than manually opening and saving each element?

(Like with a scheduled action, etc.).

Doing the calc in phpMyAdmin is my last restore (I don't want to have to 're-code' this complex calc in sql syntax).

I'm open to any ideas about this.

 

[p38]

By default, the "Only Calc on save" is set to "No", which means it recalcs all records in a list when loaded.

So it should do what you want to do already.

 

[troester]

If you can't use "calc on save only"=no you
can add a scheduled task (php) which is doing your calculation and updating the DB with the result (instead of returning it)
Is the update only necessary once? Then you can run the scheduled task manually.

 

[chris.paschen]

I've got LOTS of calculations, and there's really no reason to be calculating all these things during the 'display' cycle, they can all best be calculated on save.
So I don't HAVE to have calc on save only = yes, but it is the 'best' way to go.

troester - is there any 'command' that I can issue that will just go through the entire table and open each one and then save, then move to the next?
I'd prefer not to move all the code into a list php action, esp. if I have a couple new elements added.

So ... is there any way to call the Fabrik open/save on each item within a loop?

 

[cheesegrits]

No, there isn't a built in feature to do this. It's one of those things we've considered, but never had time to do. I did have a brief go at it way back in 2.0, but the problem then was, it immediately ran in to nasty support issues, because people would run it on BIG tables ... which would then blow up because of hitting PHP script processing time limits on their server, leaving their tables in inconsistent states. And although we can issue ini_set() directives to raise the max processing limit, that only works on a small subset of servers - most shared and VM type setups specifically don't allow PHP scripts to change their own resource limits in the code. It also had all kinds of other issues, as s
simulating form submissions is *very* tough.

It's not a trivial issue. Even "faking" form submissions is distinctly non-trivial (to say the elast), and is not something we actually "officially" support, altough we (Fabrik Ninjas) kinda know how to half assed do it. And even that wouldn't necessarily provide the environment your calc code needs, if it assumes it is being run on a submission, unless we literally took care of every single little detail, like setting up PHP's request and post arrays in the exact way they would be during a form submission, which just in itself is a near impossibility. Remember we have no clue what you code does, or what "other stuff" it relies on in your specific environment / setup.

Which is one of the two reasons we added the "only calc on save" feature. Originally, the calc only ever calc'ed on save. For display purposes, it simply used the stored value from the table. So we initially modified it so it calc'ed on display as well, to handle cases of new calc's being added on forms with existing data, or existing calc logic being changed. And also to handle cases where the calc used data that was independent of the form itself - like looking up data from another source, which could have changed since "this" row was last saved.

But that introduced issues for people who, for instance, do table lookups in the calc code, but on source which didn't change and didn't need running on the fly ... who were then getting massacred, performance wise, when that added hundreds of database operations on large list displays, as each calc element being displayed ran it's code again.

So ... we had to make it an option.

Which is where things stand today. If you add / change a calc on existing data, you have three options:

a) work out a way of performing your logic in MySQL, and run it as an "update" query by hand, to get your data back in sync.

b) set "calc on save only" to No, and take the hit of the calculation re-running every time you display the data.

c) manually edit and save every row.

TL/DR - it's a distinctly non trivial issue, we are aware of it, but a "fix" (other than the existing "calc on save only = No" fix) isn't likely any time soon, unless someone funds the couple or three days it would take to implement properly.

-- hugh

 

[chris.paschen]

Hugh

THANKS for that thorough explanation (I hope it helps future newbies like me.
Reading through that it all makes sense.

And in my case, yes, I'm doing all sorts of nifty things (including lookups to other external data, etc.), and that's why I don't want it running when each record is displayed (because the values don't change THAT often, but should be set during a record update. So a saved value is best.

I've already used a MySQL 'version' of my updates. That took a bit of time. In one case it required a very complex process of multiple queries to 'build' the content. Not fun, but I did feel more confident in my SQL skills after that.

I guess those of us adding new calc fields to an existing, large database need to just weigh the time/costs between a) and c).

However, you did miss one option in your list ....

d) take the time to plan the implementation properly in the first place so you don't have these changes after you already have all your data entered

(But then again, obsolecense is what keeps us coding new things!

Thanks again for a GREAT explanation!

 

[troester]

I think you can do it "semi-manually" with a php cron job.
You know what you need and what you are calculating:
create a file with your cron script, copy your calc code.

Select your list in the php settings, then the list data can be found in $data[0] in the script (I don't know if prefiltered, I assume yes because you are selecting Fabrik lists, no DB tables)

So you can do something like

$data =$data[0];
 
foreach ($data as $row) {
 
//$calcresult = your calc code adapted
 
//set query: update your-table set calc = $calcresult where id = $row->your-table___id_raw
//echo query for debug
//don't run execute if debugging
 
}
exit;

You don't need to publish the cron job, you can run it manually: check box and "run"