I have a table named “customer” with the fields “custid” and “custname” and a table named “customer_statistics” with the fields “custid”, “year”, “month” and “amount”.
On the left side of a form I show the customer list with the fields “custid” and “custname”.
On the ride side I want to show a “year statistics” for the selected customer with the fields “year” and the “sum(amount)”.
In Foxpro I use the following sql-query:
local m_custid
m_custid = customer.custid
SELECT custid, year, sum(amount) as amount from customer_statistics group by year where custid == m_custid
How can can I implement this in Servoy using a aggreate on the table “customer_statistics”.
create an aggregate ‘sum’ of ‘amount’ on table customer_statistics.
Then use the relation ‘customer_to_customer_statistics’ to show the aggregate…
I think he wants the values grouped by year though which is not a job for aggregates so much. Run something along the lines of this code on record selection of customer list:
var m_custid = forms.customer_list_form_name.custid
var dataset = databaseManager.getDataSetByQuery(
'my_server_name',
'SELECT year, sum(amount) from customer_statistics group by year where custid = ?',
[m_custid], -1)
// fast way: output dataset to html and put in an html area field
some_html_area_field_on_a_form = dataset.getAsHTML()
// slightly longer way: manually iterate over dataset and fill in either a solution model form or another table etc