And how is the data stored in the table? One record per machine per day? With the hours entered as a number?
Or are there multiple records each day that log machine time for a given machine?
And you are doing this for more than one machine aren't you?
Each day one record is made for the machine, with the hours entered as a number.
It is for more machines, but that is no issue. In my main page, I have to select the machine, before I can get acces to the logbook page. For one machine there are different components (lay-out 2).
A summary field that computes the "total of" your Hours field will give you your total, but in your example, if you have just one record per day, the Hours and the subtotal would appear to show exactly the same value.
You can perform a find or use other methods such as Go To Related Records (if you have the correct relationship set up and start from a table of machines) to get a found set of just the records you want for computing totals, averages and other aggregate values that you might want to produce using summary fields.
Each record is imported by this lay-out. In this case the operational time of an aircraft. FH = Flight hours (operational hours of the aircraft).
After that, the data will appear in the list, where subtotals are created and the total operational time are calculated by a sub summary report.
12/09/2016 shuould be 12/09/2010. Subtotals appear when i click on sort by.
In the next lay-out, the hours in operation should be calculated.
In this example the machine is in operation for 40028 hours, since 12/09/2010.
The compressor blade is installed at 16/09/2016 (installation date, lay-out 2).
At that moment the total FH are 40010 hours . So the hours in operation are 18 for the compressor blade. (total - subtotal)
To make this calculation happen, there have to be an interaction between the installation date, the total FH, and the subtotal FH at the moment of installation. But how could i create this?
How could you request automatically the subtotal for a given date?
You may want a button or a script trigger that will run on layout enter to calculate the value. You could use an unstored calculation as well. Done properly it should still be fast enough. I am not sure how the tables are setup, but here is a rough example.
msn = yourtable::MSN;
install = yourtable::InstallDate;
pn = yourtable::PN;
fhinstall = (whatever field is holding the FH data for the install date if it is the same or related record or use the next line);
fhinstall = ExecuteSQL ( "SELECT FH FROM yourtable WHERE InstallDate = ? AND PN =? AND MSN =?"; ""; ""; install, pn, msn); (if you don use this you can also remove the pn variable)
th = ExecuteSQL ( "SELECT SUM( FH ) FROM yourtable WHERE MSN = ?"; ""; ""; msn);
result = (th - fhinstall)
I think an unstored calc would be fine in this case. Sometimes they work out better in a case like this where you need live fresh data.
You can use this to control conditional formatting to highlight low hours left and manage service.
I think you gave me the correct answer, but it's not working ...
I don't know what I am doing wrong.
These are my tables and fields:
- SubTotal FH
- Current FH (= SUM (ENTRY FH)
Let([msn = MSN;
install = Date.Install;
sn = SN;
FHinstall = ExecuteSQL ( "SELECT T_AC.LOG FROM SubTotal FH WHERE Date.Install=? AND SN=? AND MSN=?"; ""; ""; install; PN; msn );
TH = ExecuteSQL ( "SELECT T_AC FROM Current.FH WHERE MSN=?"; ""; ""; msn);
result = (TH - FHinstall )];result )
A screenshot of the graph would help.
You syntax is not good. You swapped up the table and field in the query. select field from table.
I would also escape all you names in the format of /"Date.Install/" for all the table and field names. You have period in the names and that might be an issue.
You removed the PN variable declaration and left it in the query as a parameter for SN.
If you set all the variables to globals for testing you can test it in the data viewer. To see what's going on.
What table is the layout based on? Is Entry.FH the hours at install? If so you likely don't need the query and just the field name.