How to write queries for a custom calculation correctly?

[sasha199]

Hello everyone, I need to change the final calculations for the element, I tried to write queries to the user calculation, but nothing happened, maybe there is an example of how to do this? I didn't find a description in the wiki.

 

[troester]

Do you mean the calculations in element's [List view settings]?
http://fabrikar.com/forums/index.php?wiki/elements/#custom-calculation
Did you set the "Custom Access" you need?
In general you'll find hints in the tooltips (hover labels).

 

[sasha199]

Do you mean the calculations in element's [List view settings]?
http://fabrikar.com/forums/index.php?wiki/elements/#custom-calculation
Did you set the "Custom Access" you need?
In general you'll find hints in the tooltips (hover labels).

Yes, that's what I mean, the standard calculation of the amount counts data from the database that relate to this column, and I would like to count the data that comes in the $data array, because they are different from those in the database, there is an element of calc with a sample of data from the database, how would I count the data from the array, I read the description, but how to make a query I did not understand, please tell me?

 

[troester]

In "Custom calc" you can only calculate (MySQL functions) on the element (=column, addressed via %s) values in the database.

In each of the element calcs you can "Split on" some element (which will do a GROUP BY)

I can't see what "Split php" in "Custom calc" is doing (or if it's doing anything at all), "Split on" is working as in the other calc types.

 

[sasha199]

In "Custom calc" you can only calculate (MySQL functions) on the element (=column, addressed via %s) values in the database.

In each of the element calcs you can "Split on" some element (which will do a GROUP BY)

I can't see what "Split php" in "Custom calc" is doing (or if it's doing anything at all), "Split on" is working as in the other calc types.

That's just what I would like for the "user calculation" to work as a calc element, but I did not understand how to achieve this, thank you very much for telling me, I will continue to understand.

 

[jmoises]

@troester Hello, i cant find samples of use for this, i am trying to use this to made a total Custom Calc, but i just cant figure it how to use this, can you please elaborate a sample that i can use in the,

thank you

• Custom query - Query fragment to generate result using %s to represent the field name for this element (do NOT use the actual field name, you must use %s). So to generate the population standard deviation for this element, this would be
  STDDEV_POP(%s)

 

[jmoises]

what i am trying to do is this

SUM all records tipo C
SUM all records tipo A
and then
subtract

10,000-2,000
custom should be 8,000

 

[troester]

Something like this should do (doing it "manually", without %s)

((select sum(tt.your-column) from `your-table` tt where tt.your-column = "A")-(select sum(tt.your-column) from `your-table` tt where tt.your-column = "B"))

 

[jmoises]

thank you i try it right now!, i still dont understand what the %s means, i look in the wiki and no much explanation.

 

[jmoises]

Something like this should do (doing it "manually", without %s)

((select sum(tt.your-column) from `your-table` tt where tt.your-column = "A")-(select sum(tt.your-column) from `your-table` tt where tt.your-column = "B"))

@troester one more question, i need to especify the rowid in the query from the current form, i have try use {rowid}, {tablename__id} but i got a sql error, how can i include it ?

thanks

 

[juuser]

You are summing and substracting list element values in the bottom of the list. What do you mean you need to specify row id?
For what and where?

List calculation is not related to any specific row, so you can't use {rowid}.

 

[jmoises]

i am showing a list using the content pluging on a form, then i show in this list charges and payments, i like to show only for the customer is viewing the form,

this
((select sum(tt.your-column) from `your-table` tt where tt.your-column = "A")-(select sum(tt.your-column) from `your-table` tt where tt.your-column = "B"))

work great but show from all the table, i try to add

And customer_id='{rowid}'

but didtn work, i try multiple diferent samples using the {} and without it too.

 

[jmoises]

or maybe the question is how to use elements in the query for the custom calculation ?

 

[troester]

I don't think you can use any placeholders (already this solution is a sort of hack).

If you need complex calculations you may do it in a custom list template.
Or - because your example is already looking like a special list (it looks like Monto is already summing up data) - add a third record which does the substraction.

 

[jmoises]

ok thanks i was trying to use fabrik functionality at the most, before i made custom template or functions,

thanks so much for your time!

 

[troester]

I don't know if this helps in your case, but you can filter a list in the content plugin.
http://fabrikar.com/forums/index.php?wiki/content-plugin/#filtering-lists
So {fabrik view=list .... your-table___custom_id=[rowid]}

 

[jmoises]

I don't know if this helps in your case, but you can filter a list in the content plugin.
http://fabrikar.com/forums/index.php?wiki/content-plugin/#filtering-lists
So {fabrik view=list .... your-table___custom_id=[rowid]}

thanks i have use this yes, but this is not working custom calculation right?

 

[juuser]

Haven't tried, but assuming you have some relation with your form and embeded list, you might be able to create an extra field in the list something like "my_filter_field". And then in form PHP plugin onBeforeLoad or onLoad you update this field to "1" where "some element in list" equals form rowid and all other rows to "0". And then in filter query something like:
select sum(tt.your-column) from `your-table` tt where tt.your-column = "A" AND my_filter_field = "1")