some thoughts about the best way to work

[FilMar]

I have a list where I have a lot of fields that are used only depending what kind of activity (choosen also via a dropdown). So I the result is in most cases that need a resulting field that holds the value from one field or another field (or fields) depending the value of another field. For expample: Activities can be 'in house' => choosen from a list of or own locations ( another list with the data via dbjoin-dropdown), or somewhere else where we enter the address in another field.

I see 2 possible workmethods:
- The use of many calc-fields to get the resulting data nice in the listviews with the other fields in the edit-form.
- the use of a SQL-view where I concatenate/calculate the right data for the list.

Wich way is the better way?
What is better in preformance (speed)?
Are the calculations also done if I use the list in a dropdown for another list?
When I use a SQL-view to show the list. Can I easily open the edit-form with the raw data when I click the edit button in that list?

I hope my questions are clear and understandable as my english is not that good,

Greetings,

Filip

 

[Sophist]

If you are going to use SQL views, be careful that the views do not end up as read-only and you find that you cannot save data using them.

The way I would deal with this would be to have all the fields needed, and to put the fields for each activity in a separate group. And then use onload / change javascript on the activity dropdown to show or hide the relevant groups.

 

[FilMar]

OK, I understand that.
And, for the project I'm on, I think I can live with the fact that I have one list to enter/edit the data and another list (based on the view) to show.
The data will be entered only in the back-end and are more static. But some calculations can vary more quickly (status changes depending the date, number of inscriptions via another list, ...)

I think that the calculations only are executed when you open the list or form (depending also the settings) but are the also calculated when you use a list to fill a dropdown? As far as I can see they use the data of the records in the database then.

Greetings,

Filip

 

[Sophist]

Calc fields are executed depending on their settings. The default is on load and save of a form, and on load of a details view or list. But for a form, you can make it calculate only on save with an option - or you can make it ajax in which case it calcs whenever you change any of the other fields in the form.

If you have a large list, then calculating each calc field on each row individually when you display the list can be very slow. So a few rules of thumb (and you need to listen carefully, because I vill say zis only vonce):

1. If the value of the calc field depends only on other elements in the list which can only be changed by editing the form (i.e. when you calc on save then the calc value will be always stay correct), then set Only Calc on Save to Yes. (Or alternatively, if the calcs are simple, then you might want to consider a different approach where instead of using ajax Calc elements you instead use read-only field elements and javascript change events to calculate the new values in the browser using javascript rather than using calc elements to calculate them on the server using php.)

2. If the value of the calc element depends on data which can change after the form has been saved - i.e. because it is in another list or something else changes it, and you are going to have the calc fields visible in the list, then you do need to calculate this when you view the data. If the number of rows you are going to display on your list is small, you can set Only Calc on Save to No. But if you are going to display lots of rows or need to have Only Calc on Save set to Yes then you need to do both of the following:

a. Set Ajax Calculation to Yes and set Calc on Load to Yes also; and

b. Use a List Plugin PHP events to run an SQL update on your table to update any rows which need recalculating before you load the data into the list - you should be able to do this as a single UPDATE statement which will run efficiently, and for every SET clause (e.g. SET calc_field1 = stuff) you should have a WHERE clause (e.g. WHERE calc_field1 <> stuff) so that you only update records where the value has actually changed. NOTE: I would classify this as an advanced use of Fabrik - you need to be proficient at SQL, though there are some examples in the forums you can crib from.

 

[FilMar]

Thanks Sophist for the ideas,

I will certainly look at the alternative way as read-only field and javascript for the 'simple' calcs as I have some calcs that are easy concats depending the values of some fields in the same list so they change only at edit of the record. I think that is 'lighter' for the application.

For the others I will look at your other proposal too, the list plugin php. I will take a look at the examples.
When I use a dbjoin dropdown in a form wich will show some of the calculated data I need to add the php plugin also on the form I suppose (to be sure to have the latest data).

After a quick look: I see a php plugin for a form where I can choose 'onBeforeLoaded' wich is the one you point to I assume.
But when I add the php plugin on a list I don't see where I can say when it has to be executed... as far as I can see it adds a button at each record that will be executed if clicked.

Greetings,

Filip

 

[Sophist]

"When I use a dbjoin dropdown in a form wich will show some of the calculated data I need to add the php plugin also on the form I suppose (to be sure to have the latest data)."

No idea what you mean by this but I doubt you need the php plugin on the form if you set Ajax Calc = Yes and Calc on Load = Yes.

 

[FilMar]

@Sophist: Sorry, I think I didn't read your answer very well the first time.
I already had installed the php list plugin, and now I discovered the list plugin php events. So far so good.

Further I'm confused. You say, for the more complex calculations to use a calc field (and ajax updates) and use the list plugin PHP events to do the update of the fields. I would think as, when you use the plugin to update, you don't need a calc-field any more.
I found this in the forum: http://fabrikar.com/forums/index.php?threads/count-entries-in-many-to-many-table.48238/#post-251093

And what I meant by my earlier post:

• I have a list A with the calculations. (when you open the list the calculations are done so you see the correct data, no problem, but are they also saved in de DB?)
• I have a list B and form B with a dbjoin field on list A where I show some of the calculated data.
• When I need the plugin in list A to update the calculations, I also need to do the same in form B to update the data of list A before it is shown in the dropdown.

Or am I wrong here?

Greetings,

Filip

 

[Sophist]

The list events plugin runs when you display the list. It does not run when you load or save the form without displaying the list.

1. The calc elements are saved in the database.
2. To update the calc fields in List A when used as a dropdown, you will need to run the same List A update SQL when you load form B (using the form php plugin).