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 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.
