AppGini Community Forums

Posted: **2020-12-19 15:18**

Hi I am new to this forum and am having a problem with producing SQL code for two calculated fields that are similar. The code works perfectly well in phpadmin but when I place it in the calculated fields the output is blank (null) when the respective table view screens are displayed. I would be really grateful if someone could point me in the right direction.

A little bit of background I am using AppGini V5.92 rev 1126. I am writing a system for recording timesheets. I have three distinct tables for a time sheet; a header (table:TimeSheetHDR) , detailed appointments (table:TimeSheetDet) and missed appointments (table:TimeSheetMissed). I have three tables as there are different information requirements for each table. When detailed information and missed appoinments have been entered at the dv level I want to be able to calculate how many hours are left on that particular contract once the time sheet has been entered. The key field ContractID appears on both the TimeSheetDET and TimeSheetMissed tables.

My SQL code for the calculated field TimeSheetDET.ContractHrsLeft is as follows:-

Code: Select all

set @contract = (select ContractID from TimeSheetDET WHERE TSDETID = '%ID%');
SELECT BudgetHours - (
SELECT SUM(t.TimeBooked) AS total_booked
	FROM (SELECT TimeBooked FROM TimeSheetDET WHERE ContractID = @contract
			UNION ALL
		  SELECT TimeBooked FROM TimeSheetMissed WHERE ContractID = @contract) as t)
FROM Contract WHERE ContractID = @contract /code]

My SQL code for the calculated field TimeSheetMissed.ContractHrsLeft is as follows:-

[code]
set @contract = (select ContractID from TimeSheetMissed WHERE TSMSID = '%ID%');
SELECT BudgetHours - (
                SELECT SUM(t.TimeBooked) AS total_booked
	FROM (SELECT TimeBooked FROM TimeSheetDET WHERE ContractID = @contract
			UNION ALL
                 SELECT TimeBooked FROM TimeSheetMissed WHERE ContractID = @contract) as t)
FROM Contract WHERE ContractID = @contract

I suspect that the set command is causing the problem as in essence I am providing two SQL commands rather than one.

I have attempted to place the ContractHrsLeft calculated field code at the Contract table level and then merely select it from the Contract table when entering a detailed line. Unfortunately, I have discovered that calculated fields only get updated when the table is displayed so that the data can be out of date in between times.

I hope this makes sense and I can provide more information if required. Many thanks for looking.

Posted: **2020-12-19 15:24**

Hi,

you can only use one sql statement as far as I know. So you need to combine your statement into one.

Posted: **2020-12-19 15:31**

Hi

Thanks for the quick reply. I am new to SQL. Can you possibly show how that can be achieved? It took me ages to come up with the code that I did.

Posted: **2020-12-19 16:05**

Hi,

can you post your table definitions and the relations between the tables (lookup fields). Otherwise it is not possible to understand how that might look like.

Posted: **2020-12-19 17:00**

Hi

Yes, many thanks for looking at this. The table definitions are as follows:-

TimeSheetsHDR - Header table

TSHDRID Int(12) PK
EmployeeID int(11) - look up to table Employee
AllocID int(11). - used for selecting only students that the employee is allowed to see
RoleID int(11) - lookup to table Role
ChargingMonth smallint(2)
CRN Varchar(40)
DateEntered timestamp (automatic)
Username (read only)
StudentApproved tinytext
StudentAuthorisation - uploaded document
TimeSheet - uploaded document
Approved Varchar(1) - read only
Invoiced varchar(1
StudentID int(11) - readonly

----------------------------------------------------

TimeSheetDET - Detail table

TSDETID int(11) PK
TSHDRID - foreign key to TimeSheetHDR
Location varchar(25)
ModeID int(11) - look up to Mode table which holds types meetings e.g. telephone, face to face etc
ApptDate date
StartTime time
EndTime time
BreaksTime decimal(10,2)
TimeBooked double(10,2) - calculated field as StartTime minus EndTime rounded to nearest 1/4 hour
ContractID int(11) - calculated field that finds the right contract on the Contract table (THIS WORKS FINE!)
ContractHrsLeft decimal(10,2) - THIS IS THE CALC FIELD THAT DOES NOT WORK

--------------------------------------------------

TimeSheetMissed. - Missed Appointments

TSMSID int(11) PK
TSHDRID - foreign key to TimeSheetHDR
Reason varchar(40)
ApptDate date
StartTime time
EndTime time
TimeBooked double(10,2) - calculated field as StartTime minus EndTime rounded to nearest 1/4 hour
DateTimeInformed datetime
ContractID int(11) - calculated field that finds the right contract on the Contract table (THIS WORKS FINE!)
ContractHrsLeft decimal(10,2) - THIS IS THE CALC FIELD THAT DOES NOT WORK

-------------------------------------------------

Contract - table of all contracts

ContractID default?
StudentID int(11) - look up to table Student
FundID int(11) - look up to table FundingBody
RoleID int(11) - look up to table Role
StartDate date
EndDate date
ContractRate decimal(10,2)
BudgetHours decimal(10,2)
TotHrsBooked - calculated field as mentioned in previous post - works but not refreshed constantly
BudgetHrsLeft - calculated field as mentioned in previous post - works but not refreshed constantly
VatID

------------------------------------------------

This is a bit long but I hope it is enough information for you.

Regards

Posted: **2020-12-19 20:04**

Hi,

can you try for Contrace.BudgetHrsLeft

Code: Select all

SELECT   sum(c.BudgetHours)- (select sum(tm.TimeBooked) from TimeSheetMissed tm where c.ContractID = tm.ContractID) - (select sum(td.TimeBooked) from  TimeSheetDET td where td.ContractID=c.ContractID) 
FROM  Contract c
    where c.ContractID=%ID%

Posted: **2020-12-19 20:24**

For the refresh you could try to add to the hooks/Contract.php Contract_footer function the following:

Code: Select all

			case 'detailview':
				$footer='<%%FOOTER%%><script>
					$j(function(){
						$j(document).on("hidden.bs.modal", function () {
							AppGini.calculatedFields.updateServerSide(AppGini.currentTableName(),$j(\'[name="SelectedID"]\').val());
						});
					});
				</script>';
				break;

Posted: **2020-12-19 20:43**

Thank you I will try this code and let you know.

Posted: **2020-12-20 10:43**

Hi

I have tried the above but unfortunately it does not refresh in between visits to the Contract table. So, when entering a timesheet detail record it just shows the same result for ContractHrsLeft until I go into the Contract table view which then refreshes the calculated fields.

I am restricting employees from viewing the Contract table so I cannot ask them to go and view the Contract table from time to time so that the calculated fields work. Is there a way of forcing the Contract table to refresh whilst entering a timesheet?

Many thanks

Posted: **2020-12-20 11:07**

hmm... Can you post the code you entered. On my system it worked fine.
Or are you talking about entering the timesheet details not via the Children View, but directly in the respective table?

Posted: **2020-12-20 11:30**

: demo.gif (205.56 KiB) Viewed 6428 times

Posted: **2020-12-20 11:46**

Thanks. i do mean in the children detail view and my hours left performs the same calculation correctly. However, if you then enter another detailed line, without refreshing the Contract table manually in between, it hasn't updated the Contract.BudgetHrsLeft field so the result on the new line is the same as the old one.

I can send you the code but what code would be helpful specifically?

Here is the code you sent me placed in the correct place (I hope). Thanks

Code: Select all

	function Contract_footer($contentType, $memberInfo, &$args) {
		$footer='';

		switch($contentType) {
			case 'tableview':
				$footer='';
				break;

			case 'detailview':
				$footer='<%%FOOTER%%><script>
					$j(function(){
						$j(document).on("hidden.bs.modal", function () {
							AppGini.calculatedFields.updateServerSide(AppGini.currentTableName(),$j(\'[name="SelectedID"]\').val());
						});
					});
				</script>';
				break;

Posted: **2020-12-20 12:19**

Hi,

sorry I do not really get your workflow. Can you put up some screenshots on how you handle that. Is it different from the demo I put up above?

Posted: **2020-12-20 14:04**

Hi

The first image is where I have just inserted a new detail line

https://1drv.ms/u/s!Ak_L4rjQJPIJgshvySX ... Q?e=FiJYr8

And after I have gone into the Contract table it shows a different balance which means that the Contract table is only being refreshed when it is displayed in table view.

https://1drv.ms/u/s!Ak_L4rjQJPIJgshwt1q ... Q?e=rhw87j

I'm not sure how to perform a post in the way that you did so I have shared links.

Posted: **2020-12-20 14:31**

Calculated fields will be updated when the dataset is displayed in detail view or listed on a page in table view. Furthermore, there is no guarantee that the value in the database is always up to date.

You can trigger the recalculation of calculated fields yourself, for example via a CRON job. There have been extra function calls for this for some time.

A few (hopefully useful) links:

Posted: **2020-12-20 15:04**

Thanks very much for your help, both. I will research these links and try and find a way forward

Posted: **2020-12-20 16:10**

Hi again,

sorry, but your table definitions in the upper part of the post do not match the images that you post. So I'm completely lost on which table you are working and what you try to achive and what is not working.
From the images it looks like you are working on the TimeSheetsHDR.
If that is the case, the code should go to that hook file of course.
Maybe you can try that and explain once more that workflow.

Posted: **2020-12-20 20:14**

Hi

Sorry to confuse you but I left certain fields out as I thought they were irrelevant with regard to the problem in hand. The differences really relate to me trying to solve this issue i.e. Contract No and Hours left. The work flow is that a) an employee enters the header information i.e. employee name/student/role and then b) enters the detail information which I will include in a link below, whilst he/she enters the info it would be superb if the contract hours left would be displayed live for informational purposes, b) if they have any missed appointments then they would enter details of those and again it would be good if the contract hours left would be displayed. I hope this makes it clearer.

https://1drv.ms/u/s!Ak_L4rjQJPIJgshxYq9 ... A?e=B82Bmh
https://1drv.ms/u/s!Ak_L4rjQJPIJgshyiOv ... w?e=Bl0hhY

Having read the links from jsetzer it appears that as I am picking up calculated fields from a different table then this is difficult and I may have to resort to refreshing them on a regular basis using a cron job running cli-update-calculated-firlds.php. I have tried to refresh the table programmitically but I'm afraid it didn't work. This is a bit of a nightmare using XAMPP whilst testing as I have to do this at the cmd level manually

Posted: **2020-12-22 12:33**

Just to complete the loop on this. I wasn't too keen on setting up a cron job to continually refresh the Contract table in order to provide updated info whilst inputting a time sheet so I persevered with sql code.

In the end I decided to add two extra fields to the TimeSheetDET and TimeSheetMissing fields i.e. SumDetLines and SumMissingLines as hidden fields on the records. The SQL required became a lot easier to handle. Once these fields were calculated, I could then calculate the ContractHrsLeft by picking up the budget from the Contract table and subtracting both SumDetLines and SumMissingLines. The SQL code I came up with is below. Perhaps it is clumsy but thank goodness it works.

I have just listed the code for TimeSheetDET but it is almost exactly the same for TimeSheetHDR

Code: Select all

TimeSheetDET.SumDETLines

SELECT SUM(TimeBooked)  FROM TimeSheetDET 
WHERE ContractID = (select ContractID FROM TimeSheetDET WHERE TSDETID = %ID%)

TimeSheetDET.SumMissedLines

SELECT SUM(TimeSheetMissed.TimeBooked) FROM TimeSheetDET
			INNER JOIN TimeSheetMissed USING (ContractID)
		    WHERE TimeSheetDET.TSDETID = %ID%
		    
TimeSheetDET.ContractHrsLeft

Select (Contract.BudgetHours - TimeSheetDET.SumDETLines - TimeSheetDET.SumMissedLines)  from Contract
INNER join TimeSheetDET Using (ContractID)
WHERE TSDETID = %ID%

If Appgini had allowed me to declare a variable in my original SQL code i.e. two sql commands as a calculated field, then I wouldn't have had the problem. However, I understand that there are constraints in every software.

Happy Christmas to all

AppGini Community Forums

Difficulty with a SQL calculated field

Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field

Re: Difficulty with a SQL calculated field