Show calculated results in list/table possible with Fabrik?

[Joppo2]

I've been experimenting with Fabrik for a few weeks now, but despite also some googling I could not find an answer on my following question:

I have a Joomla (3.3) design and use the latest version Fabrik from Github. I want to show some calculated results in a table form (within a login page) which are based on various (different) db table fields. Like:

Example table shown on login webpage:
Label calculated result

Cost (euros) 145.23
Payback (days) 76.2
ROI (%) 15.2

So the results for 'Cost', 'Payback' and 'Roi' are calculated by means of some retrieved database table field values

Would calculating & showing this be possible with Fabrik? If so, how? ( I looked at some possible ways of using the Fabrik lists, but as far as I understand(?) Fabrik only shows the field values saved in the db).

 

[troester]

I don't know your exact setup but you can use the calculation element.
You can calculate inside one form/record using placeholders, e.g.
a calc element "total" could be
return (int) '{mytable___quantity}' * (float)'{mytable___costs}';
or you can use php code fetching values from different tables etc.

So you can add calc elements to existing lists or you may create an extra list with e.g. a dbjoin to your products (or whatever you want to show) + calc elements retrieving values related to this product.

 

[Joppo2]

thanks, that works! (I didnt know the calc element as it was not preinstalled).

One thing I'm still trying to figure out: If I'd like to have the labels in one (first) column and the related calculated results in the other (second) column - like in my earlier example- how to do that?

 

[troester]

Is this only one record?
You could use details view instead of list view.
Or you can use the "div" list template.

 

[Joppo2]

thnx. How to set the 'details view'? Is this in the list Details->Layout-templates options? I only saw and set the div option there but no effect (see screen shot in attachment)

 

[troester]

This should do.
Where/How do you call the list?
In content plugin add layout=div to the content string {fabrik...}

 

[Joppo2]

For test purposes I have linked the list to the main menu (so by clicking on a menu button)

To double check: your refer to the 'Fabrik content' plugin shown at the plugin manager? (I dont see how to add anything there)

 

[troester]

With the content plugin you can include (multiple) lists/forms/vizs in a Joomla article.

Template: you can select the list template in the menu item definition.
With "use default" it is taking the one of the list setting but you can override this setting by selecting an other one.

 

[Data Crafter]

If you've defined the calculation element in your list (i.e. table), then one approach might be to perform the calculation on the MySQL side with update and insert triggers. I know this is a bit of a run-around from the pure Fabrik approach - but this would easily allow you to perform the calculations each time a record on the list is created or updated. Creating these triggers is very easy using phpMyAdmin and requires very little SQL to perform.

If you want to take this approach (and use phpMyAdmin), simply get into phpMyAdmin and look at the specific table in question. Click the Triggers tab, and specify the name of the trigger, e.g. my_table_bu (indicating a Before Update trigger). phpMyAdmin gives you drop-downs to specify the BEFORE and UPDATE part. In the trigger definition, put the calculation you want to perform. If you can tell me the calculation you want to perform and the column_names, I can put together an example SQL definition for you. It sounds like the calculated element in on the same row as the elements needed for the calculation - so you'd only need a trigger for INSERT and one for UPDATE.

The MySQL triggers allow you to reference the NEW value for elements as well as what was the OLD value for elements. (This is true for Update triggers; Insert triggers only capture the NEW values - and Delete triggers only capture the OLD values).