I have two tables: Table A and Table B. Table A contains information about a television program, whereas Table B contains the shot schedule for the program. Table B is represented in a portal row within Table A.

 

I am having an issue calculating time. I have a field in Table A (total time scheduled) that uses the following calculation:

Sum(Table B::duration) *unstored*

Total Time Scheduled is formatted as time and Duration is formatted as text. The duration field in Table B needs a little flexibility so I have it as a text field (time wouldn't let me enter just seconds without zeros preceding the seconds). For example, if the shot is only thirty seconds, the user enters :30. If the shot is one minute, the user enters 1:00. If the shot is one hour (very unlikely, but I'd like them to have this option) then the user would enter 1:00:00. The calculation works properly if the duration entered is under 1 minute, but if 1 minute or greater is entered the Total Time Scheduled starts displaying bizarre results, usually adding an additional 40 seconds.

 

Additionally, in table A the user has to enter the anticipated start time of the show in a field called "Start Time" (time) and Total Time Scheduled is added to Start Time to give the user an estimated end time.

 

Can someone point me in the right direction here? Thanks

Please use actual names for your tables, fields, layouts, script, etc. or attach a copy of, or a mockup of, your file.

 

This sometimes help us to understand logic of your question. Using names such as table A table and B get in the way of this. Even better, would be to attach a copy or a mockup of your file.

 

Lee

 

BTW, if you’re not sure of how to attach a file just follow the steps you’ll find in this thread, Attached File

Edited January 23, 201510 yr by Lee Smith
change the wording to sound better

Duration is formatted as text.

 

You cannot sum text values expressing (in your mind) a duration in any meaningful way. Either define the Duration field as a Time field, and solve the data entry issue using auto-enter calculations and/or script triggers, or define a calculation field in the Shots ("B") table with a Time result type and sum that.