iTunes importing Time to FM

[cricket]

This may be really simple, but I am having a problem getting it to work right as I am still new to FM. I am trying to import an iTunes exported text file into a FM database I made. iTunes apparently exports the track Time as seconds. When I import it into FM I cannot get it to convert to minutes:seconds.

I have tried changing the Time format of the FM Time field, but that just changes the seconds into really weird numbers. Is there a calculation function I have to use?

Ex: I want 136 seconds to read as 2:16.

Thanks in advance.

[comment]

Make the imported field of type Number.

Define a calculation field (result is Time) = imported field.

If this is a one-time conversion, you can now change the calculation field into type Time, and delete the original imported field.

[Wim Decorte]

Try this calculation: (seconds is the field from iTunes)

Int ( seconds / 60) & ":" & Right ( "00" & Mod(seconds;60); 2)

[LaRetta]

Yes. The time field converted it perfectly when imported into a number. Because time is simply seconds and FM knows it. It's all part of FM's date engine (logic), right? Time is similar to DATE (now don't you laugh) because FM reads them as straight numbers (seconds are the finite level of Time and days are the finite level of Date). FM tracks this linear progression forward/back, adding and subtracting the pieces (month, hour, whatever) as straight numbers broken down into seconds or days). So FM CROSSES date/time boundaries correctly. Manipulating time fields will be same logic as:

dateField - Day(dateField) would produce the last day of the prior month so

timeField - (Minutes(timeField)+ Seconds(timeField)) would produce, ummmm, midnight? FM thinks military style, right? And it must work with timestamps as well!

Anyway my point is ... it's straight math. Time fields used to (almost) intimidate me. They don't any longer!!

L

[comment]

seconds are the finite level of Time and days are the finite level of Date

Almost - Filemaker does recognize fractional seconds.

timeField - (Minutes(timeField)+ Seconds(timeField)) would produce, ummmm, midnight?

No. Ordinarily, subtracting the actual minutes and seconds from time would produce a round hour. However, Minute ( timeField ) is a NUMBER. Subtracting any number from time is subtracting that many seconds. So if, for example, timeField = 10:01:05, then:

Minute ( timeField ) + Seconds ( timeField ) = 1 + 5 = 6 ;

timeField - ( Minute ( timeField ) + Seconds ( timeField ) ) = 10:01:05 - 0:00:06 = 10:00:59

[LaRetta]

Of course, my error! Minutes would need to be * 60 then those seconds added to the seconds first when adding directly to a time field. But Minutes could be used (just as we use Months when reconstructing and jumping dates) with function of Time ( hours ; minutes ; seconds ). Theory is the same it seems?

I surely don't want to hijack this thread but I'm dying to know how one would track the math on those fractions of a second. It's not an option in field format under time to even display them and I've never seen it produce a fraction before. And there is no way to Set Precision() is there? Do we just pretend we don't know they're there? Can their existance or rounding throw off a calculation or does FM truncate? IIW, adding several seconds fields might end up with an extra second because of that rounding?

Now I'm done. Thanks for letting me in here on your thread, Cricket. This was just such a perfect example of conversion that I couldn't help myself. :wink2:

[comment]

Any time format that displays seconds, displays fractional seconds, too.

Rounding (or not) time has the same effect as it has on a number. Better put: on any OTHER number - since time is but the number of seconds since midnight.

[cricket]

Thanks for your responses. I got it to convert right, but it displays it like this 0:03:34. Is there a way to have it not show those leading zeros? I tried changing the Time Format for the field, but that does weird things to the number. The only way it seems to show up correctly is if I have it "Leave data formatted as entered."

Also, Wim Decorte, I tried your calculation and it didn't work for me. Also gave me strange numbers.

[comment]

The best you can do with a time field is format it not to show hours (select Format as: mmss). Unfortunately, leading zeros are turned on/off for minutes and seconds alike, so 3 minutes and 5 seconds can show either as 03:05 or 3:5, but not 3:05. For that, you can use Wim's formula - which will work fine if you define the result as Text. However, text is text, so you will not be able to total your list, for example.

[cricket]

Hmm, bummer.

I also tried using the mmss format, but it changed the result. The ONLY thing that works is hhmmss. I find this very strange.

Thanks for all your help. I'll just leave it as is. It's not super important that the zeros don't show up.

[comment]

I find this very strange.

So do I - maybe you can post a sample?