AppGini Community Forums

I would like to know the suggested SQL Query to go in the "Query" window for a calculated field, where I have a "start time" field, "finish time" field and a "total hours" field. All fields are in the same table called "timesheet". I had a look on Google and found the following but it does not work.

TIMEDIFF(`timesheet`.`day_1_from`,`timesheet`.`day_1_to`)

As you will note I do not know anything about SQL Query Language. If anyone can help me that would be good. Thanks.

Hi,

can you post your complete query, and what are you getting (and expecting) as result?

This is the total query I have typed in.
I would like to see the total hours and minutes between the start time and finish time placed in the calculated field

I and not getting anything display at all at present

Hi,

you need to put in a sql query.

So try
assuming that day_1_from is your start time and day_1_to is you finish time.
Also assuming that your primary key of timesheet is called id

Thank you - It works.

However, I am getting a negative result (e.g. -04.00.00) in the calculated field in the table view.

The calculated result is not showing at data entry time in the detail data input view. It does show though on the detail view when looking at an existing record. i.e. the calculated field is not calculating at data entry time it is calculating on saving the data to the table. Is there a way to make the calc perform in the data entry view so the user knows the hours that are calculated? Thank you again, I much appreciate your help here.

Hi,

glad it works.

can you show a sample of that data you are using for the calculation. Also the default behaviour for the calculated fields is as you described. Calculation happens after saving the record. If you want to do some calculation at data entry you can do that via Javascript.
register on change for your date fields and if both are filled you calculate the diff and display it on the calculated field.

I can see at 66 Years old I'm gong to have to register for coding classes as the local college. I'm trying to help my daughter get some admin functionality on her business web site. I have no understanding of Java script. This is why I thought Appgini would be useful in helping me to do that. Even with Appgini though there is quite a lot of coding you have sort for yourself.

Anyway that aside, If I understand you correctly the data input to the data input fields looks like e.g. "12:30" start time and "15:30" end time - the calculated field now displays -03.00.00.0000. I'm only interested in the hours and minutes worked by the subcontractor teachers so we have a quick record to check the subcontractor teachers invoices. Have I given you the details you required?

Hi,

no worries, AppGini gives you a lot out of the box, and a lot of possbilities, but if you want to do more than what is provided out of the box, you need to get onto coding a little bit.

for your question.

Try
and switch the from and to (as in the written code now, assuming that these are your fields).

Thank you yet again.

I have tried the new code but there is no result in the Calculated field of the table now

Can you post your "old" code that gives results and the new one you that you put in.

Hi -
I'm feeling guilty now - you've my been on my query a while.

New Code you gave me:-

SELECT time_format(TIMEDIFF(timesheet`.`day_1_to`,`timesheet`.`day_1_from`),"%H:%i")
FROM `timesheet`
WHERE `timesheet`.`id`='%ID%'

First code you gave me:-

SELECT TIMEDIFF(`timesheet`.`day_1_from`,`timesheet`.`day_1_to`)
FROM `timesheet`
WHERE `timesheet`.`id`='%ID%'

Hi,
can you try:

What type of field is your calculated field? What codepage are you using?

The calculated field is a Date field
I don't understand "code page"

I have tried changing the field type to integer and text - the new code did not show a result in either and does not in Date type field
either.

Sorry,

can you try
there was a missing backtick.

Hi

That's cracked it. Thank you so much for your help. See Attached image of table.

Regards
Trevor