Jump to content
  • 0

Sum of hrs using a virtual field?


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

Recommended Posts

  • 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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...