• 0
mdav20

Sum of hrs using a virtual field?

Question

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

 

 

Share this post


Link to post
Share on other sites

1 answer to this question

  • 0

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?

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now