Sum of hrs using a virtual field?

[mdav20]

I currently have a calculated field on a details report that sums up the a total hrs field in a table.

This is the current formula and it works perfect...

select sum(hrs_total) from tire_hrs_table where tire_id=target.[@field:tire_id]

 

My problem is the hrs_total is the sum of [hrs_removed] - [hrs_installed] of only completed transactions and not currently open transactions.  Every record has a hrs_installed but open transactions do not have a hrs_removed yet. 

 

I would like to add a virtual field for [current_hrs] to my details report which would allow the user to input the current hours.  Then I need to take the virtual field [current_hrs] and subtract [hrs_installed] from records that do not have [hrs_removed] yet to figure out how many hours are on the open transaction currently and then add that to my formula i already have working. 

 

 

Not sure if I would use a CASE statement or how to use it.

 

Hopefully that kinda makes sense.

 

thanks for any help you guys can give

 

 

[MayMusic]

Can you give me the example of your table with sample data? Where do you have this cal field in details page or report page? Is this page based on tire_hrs_table  as well?

For the same tire_id is it possible to have some rows with blank hrs_removed and some with data?