[hokah]

Hello.

How to create a conditional report page with user selecting dates and company(from a dropdown list) and then it calculates a summary from the main table?

table main
company_id
cost1
cost2
cost3

table company
company_id
company_name

it sums the main tables selecting records by date and sums costs.

I have no idea how to do that.

[Jane]

Hi,

you can create report on the Datasource tables tab, tehn join these tables on the Edit SQL query tab.
To filter records in the report use advanced search page.

[hokah]

Jane, on 02 February 2010 - 03:31 PM, said:

Hi,

you can create report on the Datasource tables tab, tehn join these tables on the Edit SQL query tab.
To filter records in the report use advanced search page.

Hi,

Thanks for the idea, but I want to create something more user-friendly. Something like a tables 'add page'. Where user selects start_date, end_date and company and program automatically summaryses costs without the need of saving a record.

[Jane]

Hi,

I'm not sure that I nderstand your question.
Where do you want to summarize values: on the add page or on the list page?

[hokah]

Jane, on 04 February 2010 - 03:32 PM, said:

Hi,

I'm not sure that I nderstand your question.
Where do you want to summarize values: on the add page or on the list page?

Where is not so important.
I guess another way would be to make a new table 'sums' with fields:
date_start
date_end
company
sum

then the user on the tables add page, fills the first 3 fields and saves a record
there should be a php event after record added that will calculate summary into the
sum field.?

[Jane]

Hi,

to calculate values on the add/edit pages use Before record added/Before record updated events on the Events tab. All entered values are in the $values array. Here is just a sample:

$values["FieldName1"] = $values["FieldName2"]*$values["FieldName3"];

[hokah]

Jane, on 05 February 2010 - 02:07 PM, said:

Hi,

to calculate values on the add/edit pages use Before record added/Before record updated events on the Events tab. All entered values are in the $values array. Here is just a sample:

$values["FieldName1"] = $values["FieldName2"]*$values["FieldName3"];

Hi,
need some help with php synatx. Table where the calculations are like in the image.

user selects conditions, FILIAAL, ALGUS_KP(starting date) and LOPP_KP(ending date) which select records from table 'sinine'.
date values are selected in the tbale 'sinine' from field TELL_KUUP. L_LAEKUNUD is calculated from table 'sinine' from fields SUMMA, SUMMA1, SUMMA2.
L_MAKSTUD is calculated from table 'sinine' fields HIND1, HIND2, HIND3.
L_KASUM is calculated from current table sum of L_LAEKUNUD - L_MAKSTUD.
and the L_PROTSENT is percent of that.



Kaarel

[hokah]

Maybe something like this works?

$result = mysql_query("SELECT * FROM sinine WHERE FILIAAL = $values[FILIAAL] AND LAEK_KUUP >= $values[ALGUS_KP] AND LAEK_KUUP <= $values[LÕPP_KP])
while($row = mysql_fetch_array($result))
{
$values["L_LAEKUNUD"] = $row['SUMMA'] + $row['SUMMA1'] + $row['SUMMA2'] 
}
return true;

[Jane]

I recommend you to publish your project on Demo Account and open a ticket at http://support.xlinesoft.com sending a URL to your pages along with detail descrition of what you want to achieve.

[hokah]

How to validate if fields value is set? For example to check with php syntax if user has set a value to field.