Old v5 Database No Longer Works Properly

[stealth162]

I recently converted my database which is fairly simple to version 7. It has been working fine in version 5 and now I am having trouble with a time field. I use the time fields for calculations of timecode. The fields having trouble function like this. I enter a start time in the format hh:mm:ss and a end time in the same format. The database then calculates the difference between the two and displays it in another field (ie;length) in the same format hh:mm:ss.

Now that I have upgraded to version 7 that no longer works. I put in a start time and recieve a format error saying that the time is entered incorrectly. Same goes for the end time. Then when both numbers are in (start and end) the duration field reads some looks to be random number.

Here's an example of what version 5 does.

start 01:00:00

end 02:00:00

duration = 01:00:00

Here's an example of what version 7 does.

start 01:00:00

end 02:00:00

duration=10000:00

Any help is appreciated. I am willing to send the file to anyone that might be willing to take a look.

[transpower]

Are you sure that your fields are defined as time fields? Do you have duration defined as an auto-enter calculation? I tested your sample problem and it works fine.

[stealth162]

Yes my duration is an auto enter calculation. I also tried to switch all my fields and field formats to time and still had no luck. If you are willing to take a quick look I could send you the file. It is quite small and probably simple to an advanced fm guy.

[transpower]

Sure; add an attachment to your post.

[stealth162]

Both versions are attached (version 5 and version 7). The layouts are slightly different but the calculations are the same. I figure then you can go into version 5 to see how it is supposed to work. I really appreciate any instruction you can offer me. I am pretty new to filemaker so I will need some guidance if youre williing.

Spottingnewlayoutv7.fp7.zip

[comment]

I can't make heads or tails of your calcs, but couple of things seem to be clear:

1. In FMP7, "00:00" is ONE word (although "00:a" is TWO words). Your validation relies on parsing the input, using LeftWords() and MiddleWords() functions. Therefore the validation fails at what should be a legit input, e.g "01:00:00".

2. The duration calc is also based on the same fields, defined with LeftWords() and MiddleWords() functions. Therefore it also fails.

3. Contrary to what you said, none of your fields is a time field - and rightly so. Time fields are not suited for timecode calculations.

If I can make a suggestion or two:

Do not force your users to input the : character; instead, let them enter the numbers only. For example, for timecode 00:01:15:09 the user should be allowed to enter 11509. For feet-frames entry, you can demand the - character.

Validate timecode entry by parsing Right ( "00000000" & TCEntry ; 8 ).

For example:

 

Let ( [



TC = Right ( "00000000" & InPointEntry ; 8 ) ;

hh = Middle ( TC ; 1 ; 2 ) ;

mm = Middle ( TC ; 3 ; 2 ) ;

ss = Middle ( TC ; 5 ; 2 ) ;

ff = Middle ( TC ; 7 ; 2 ) ;



h = GetAsNumber ( hh ) ;

m = GetAsNumber ( mm ) ;

s = GetAsNumber ( ss ) ;

f = GetAsNumber ( ff ) ;



error = 

h > 23 or m > 59 or s > 59 

or 

f >= GetAsNumber ( TCformat ) 

or

Right ( TCformat ; 1 ) = "d" and  f < 2 and s = 0 and Mod ( m ; 10 )



] ;



Choose ( 

error ; 

hh & ":" & mm & ":" & ss & ":" & ff

"Invalid Timecode"

)

)

[stealth162]

I appreciate your reply and your post seems to make sense to me. I am really new to filemaker though and don't even know where to begin as to implement it. Do you understand what I am trying to do? Simply subtract two timecodes to give a duration but also convert that time code to feet and frames for display. As the old database was I could click and toggle that display between the two formats which was also how I'd choose how format my reports print in. I know this probably sounds stupid but, I honestly need some hand holding and some kind of simple working example I can studying or directions. I know that is a lot to ask for. I appreciate anything that is offered.

[comment]

It doesn't sound stupid at all. However, I am working in the dark here, since I don't have v.5, therefore I can't see how the original file functioned. So let me ask some non-Filemaker questions:

1. Which timecode formats (24/25/30dropframe/30) do you use ?

2. Which film format/s, and specially which film speeds (frames per seconds), do you use?

3. What are these "ticks" that your file uses to subdivide a frame? Do they have any significance in your work (like being your sequencer ticks), or are these just a calculation aid?

Other than dealing with 30dropframe timecode (aka 29.97), this is a very simple task. But loking at your location I'd guess that's the one you deal with most often?

[stealth162]

Thanks for your help.

We primarily work with the timecode format 29.97 non drop. In the conversion to feet and frames I'm not too sure if there is much signifigance between 29.97 and 30. Time code wise it shouldn't matter since it is just subtracting two numbers. I may be wrong.

As far as film format we work with 16 frames per foot which converts to 24 frames per second (or a foot and a half).

The ticks just aid in the calculation. No signifigance with any of it. Just trying to get the math done properly.

Thanks

[comment]

I have taken your v.5 file, converted it to v.7 and fixed it so that it functions as originally intended (hopefully).

FWIW, subtracting two numbers in 29.97 results in a slight error. For example, 00:02:00:02 - 00:01:59:29 produces the result of 3 frames. However, the correct result is 1 frame (frames 00:02:00:00 and 00:02:00:01 do not exist).

SpottingTemplatev5.fp7.zip

[stealth162]

Thank you so much. That seems to work really well. Were the changes very drastic to make it work in version 7? I'd like to make the changes in the version 7 layout that I already have so that I can keep my new layouts. Also just curious but do you know if it is possible to set it up so that when you enter a start and finish timecode you don't have to enter the seperator ":" but, as soon as you hit enter it will reformat it so that it is there. Would save a lot of time when entering info. Thanks again for your help. I'd love to learn about what it took to make it work if you have time to explain.

[comment]

Well, at first I planned to rewrite the whole timecode section, so that indeed you wouldn't have to input the ":" separator, and streamline the calc in the process.

But then I noticed that a whole bunch of other fields depend on the calc as is, so I would have to quiz you extensively about your work procedures, and we wouldn't get anywhere with this.

So I ended up by just adding 2 calculation fields (cStart# and cFinish#). All these fields do is add a space before and after the separators in the existing Start and Finish fields.

Then I changed the validation and the parsing calcs (i.e. the fields called "Start/Finish Term x") to refer to the new calc fields, instead of the actual input fields.

The problem with colon separator is also more extensive than I thought initially. The original developer made it so that at least two of these are required - otherwise it's not recognized as timecode. See the definitions for Start Format and Finish Format fields.

If you can decide on the rules (i.e. how should the user indicate to the system that the entry is timecode vs. feet&frames), it shouldn't be too hard to rewrite these definitions to conform to the rules.

[stealth162]

Thanks to your help this is starting to make some sense to me. I was thinking that the user could identify to the system via the "+" between the feet and frames. And if it was not there it would assume it was timecode. Not sure if that's possible. If it's not. What about switching it to a ".". On the numeric keypad this would at least allow for faster entry. Thoughts? I am going to try to pick apart the work you've done in the version 5 file so that I may use the new v7 one (quikview) as it has a new layout setup and that is the one I'll utimately use. I've attached that one if you want to mess with it. It doesn't have any of your changes in it yet.

Spottingnewlayoutv7quikview.fp7.zip

[stealth162]

Made the changes to version 7 file as you stated in your earlier post and got it working. Awesome! Now if I can just make it simpler to enter the timecode. haha. Is that possible in this world?

[comment]

was thinking that the user could identify to the system via the "+" between the feet and frames.

That should work, I think. If there's a plus sign in the entry, then it's film mode. If not, it's timecode. If that's the way you want to go, then attach your current file, and I'll try to make it so (no sense in doing everything twice).

[stealth162]

On second thought I think it would be less confusing to have the user enter a "." so that there is still a symbol and no question as to weather or not a symbol is needed. I've attached the most current database.

Spottingnewlayoutv7fixed.fp7.zip

[stealth162]

It would be cool if there was a way that once the data was entered it would change it to have ":" if not the "." will work fine.

[comment]

Just to make it clear:

- a "." (period) signifies timecode;

- a "+" (plus) signifies feet+frames;

- if none of the above is entered - invalid entry.

Is this right?

[stealth162]

that's correct

[comment]

It's testing time.

Spottingnewlayoutv7fixed.fp7.zip

[stealth162]

That seems to work great!! I really appreciate all your help. There is one more thing I was thinking it would be cool if I could implement. I'm going to define the fields and the layout so you can see what I want to achieve. I understand if you don't have time to mess with this and thank you again for what you've done so far. Either way I'll post the file once I have it ready.

[stealth162]

That works great!! I see that you changed/added the calcs for the start and finish fields. What else did it take to get that to work. Just for my own knowledge I like to see what you've done and learn from it. There is another really cool thing the database currently does. If you have a bunch of records/cues and they are 1m01, 1m02, 1m03 etc and 2m0,1 2m02, 2m03 etc. When you go to the layout called Spotting Notes It will derive the reel number from the cue number and then place it above that series of cues. ie; It will display Reel 1 1m02, 1m03 and then continue on Reel 2 1m02, 1m03. I'd like to do a similar thing but adding reel or picture versions to that heading. So I would only put the info in on that main menu layout and then when I choose the report "Spotting Notes" It would say "Reel 1 v. 0109". I'm sure it is possible. How difficult would that be to do? I understand if you don't have time to answer this and I do thank you for all your help so far.

[stealth162]

I encountered one small problem. If you try to enter a even minute ie (01:00:00) you get "invalid tc" message.

[comment]

I have made slight modifications to:

cStart#

cFinish#

Start Format

Finish Format

These should be pretty self-explanatory - in fact I see I have left the previous calcs in the Format fields, commented out.

The real change is in the Start and Finish fields. I have turned validation off, and added AutoEnter calc, replaces exisiting value. Go to the field options > Auto Enter and click Specify... next to Calculated value, to see it.

As for your new request - I'm afraid I don't understand what you're trying to achieve.

[comment]

If you try to enter a even minute ie (01:00:00) you get "invalid tc" message.

It's not a problem - it's a feature. There's no such timecode in 29.97.

If you want to disable the flagging of this type of error, go to the previously mentioned auto-enter calc, find the line

TCError =

and delete these two lines:

or

f < 2 and s = 0 and Mod ( m ; 10 )

(BUT LEAVE the ";" at the end of the second deleted line!)

[stealth162]

Cool. Any thoughts on that reel version question?

[comment]

I said I am not quite sure what the problem is. The Spotting Notes is a report with a sub-summary part. You'll we have to enter your version information somewhere, perhaps concatenate it with the reel number.

The tricky part will be sorting on the correct field when calling the script. The sort order is stored in your script, and you may have to change it. I suggest reading a bit about sub-summary parts in FMP help.

[stealth162]

Thank you so much for your help.