Formatting Time Value List

[Mark Ballard]

Hi All,

I have two databases, within one is a value list that provides preset timings for the start of a booking in the format 10:00 etc. This just fixes the start of the booking at half hour intervals. When i try and link to this value list from the second database, the link is good but the list has added and extra :00 ( i guess seconds indication) to the value list. ie i now get 10:00:00.

I have tried all the different formatting options which only seem to affect the data after you have chosen the relevant option from the value list. How do i change the formatting when displaying the value list? It's bugging me but i'm sure it's something simple!

Any ideas?

note: The value list in the original database display perfectly as 10:00 etc

[mz123]

In the database that contains the times (let's call the field "TimeField"), create a calculation field named "TimeField Calc" as follows:

Let (

[

thehour = Hour( Time ) ;

theminutes = Minute( Time ) ];

If( theminutes > 9 ; thehour & ":" & theminutes; thehour & ":0" & theminutes)

)

Then you could link your drop down list to the calculation field. Would that do it?

Hope that helps :)

[Mark Ballard]

Hi Martha,

Thanx for that infor and sorry for the delayed reply, it's been a very busy week and this is just a hobby for me rather than an occupation!

I'm slightly confused by your answer as i figured that it was simply a formatting issue and i'm surprised that i have to perform any calculations, simply to display the same value list.

I have enclosed two screen snapshots, just to illustrate the problem. Image 1 is the original value list in my first database, image 2 shows the same value list in the linked database. Just doesnt seem to make sense that the values display differently, when it's the same list.

The linked database is a ready made one that i have adapted but even the originator of that can't suss out what's going on!

Any ideas would be very greatfully received.

Many Thanx

Mark B

[aldipalo]

Are both fields formatted the same? Again, I assume you clicked on Format>Time>"Leave data formatted as entered" OR Format>Time>Format As>HH:MM>Symbol>.

Are both fields defined as Time fields?

HTH

Al

[Mark Ballard]

Hi Al,

Are both fields formatted the same? Again, I assume you clicked on Format>Time>"Leave data formatted as entered" OR Format>Time>Format As>HH:MM>Symbol> .

Yes both fields have been formatted the same but in any case this only affects the display of the field AFTER you have selected a value from the value list.

Are both fields defined as Time fields?

Yes both are formatted as time fields.

I have enclosed another screen shot that shows how the second value list is created, from the original list. Like i say and as the previous shost show, it is the same values, just with :00 added on to the end of each value.

There is actually just one field, but it appears in two different places in two different databases. All the relationships are good and the data moves fine in either direction, it's purely this change in value list that's causing problem.

[comment]

Can you post a small demo showing the problem?

[Lee Smith]

Hi Mark,

If you click into the Source Field of the Value List, I think you will see the problem.

I hope the attached file will help to clarify what is going on.

What ever formatting the original field has, will be kept in the value list.

The answer to your problem is to create a Hard Coded Value list of times, and use that instead.

HTH

Lee

TimeEntry.fp7.zip

Edited March 17, 2007 by Guest

[Mark Ballard]

Hi Lee,

Thanx for that info and the example but i genuinely think I've got a bug here somewhere. It's very difficult to display as the Database i'm using is full of live data and i can't send it to you to see because of data protection.

I have tried to create an example of the problem but to my annoyance, my example works perfectly!!

If you could have a look at this example and imagine that in the "patients" database, when you click on the appointment start time, it is displaying the values with an extra ":00" on the values within the list then that is what's happening.

It is definitely the same value list, so the relationship is fine, just displays badly.

Sorry if i'm not explaining or grabbing this quickly, I'm very much still on the learning stages here and little things like this are really annoying!

Many Thanx

Mark B

Example_Value_Lists.zip

[comment]

Are you sure that the method in your example is exactly the same as in your real files?

What happens if you clone both of your real files, and import some data into the clones?

[LaRetta]

Well, it kinda of makes sense to me - because FM says:

If the value list you're defining contains dates, make sure the values contain four-digit years such as 6/29/2004 instead of 6/29/04. During data entry, a date field formatted to display a value list containing two-digit year dates will convert them into four-digit years as described in Conversion of dates with two-digit years.

It converts date displays (on value lists, even Custom) to display proper 4-digit years. If you change the fields to text, FM won't attempt to fix the value list (make it proper time) before it displays it for you.

UPDATE: And just for clarification, it is displaying the seconds for me as well. Since this is a Custom Value List, you are assuming (fairly logically) that it should DISPLAY your text-time but FM wants to make it true time; just as it displays dates as true dates (within value lists), ie, it matches the legitimate data-type.

LaRetta :wink2:

Edited March 19, 2007 by Guest
Added Update

[comment]

Yes, but this doesn't happen with times. If you make a CUSTOM value list of say "1¶2¶3" and attach it to a time field, it will display as is (at least that's what I see).

[LaRetta]

FileMaker doesn't auto-complete numbers but it does on partially entered times or dates. Custom value lists on both appear to be consistent in this regard.

Oh. My point. Numbers entered in a custom value list as: 12.30¶14¶16.22 ddd, attached to a time field displays in the value list as time as:

0:00:12.3

14:00:00

16.22 ddd

There is a reference in Help about how FM will attempt to match the data type - I think something about how it indexes...

Edited March 19, 2007 by Guest
Finally got to my point & added sentence.

[comment]

Hm. That is strange.

[LaRetta]

:shocked:

Does it take into account system formats for determining the display of Custom time or date?

Edited March 19, 2007 by Guest

[LaRetta]

Are you setting your ValueListItems() calculation to result of time or text ?

What is funny, is that I change my calculation (ValueListItems) to text and attach that same value list to it instead, it displays as yours does! It says FM will attempt to match the value to the FIELD to perform its task, ie, "FileMaker Pro uses the field type to interpret the data for tasks like sorting records and performing calculations." The value list is attached to a time field so FM makes the values (a value index) time or date to ensure correct data entry?

Edited March 19, 2007 by Guest
Added another sentence.

[Mark Ballard]

Hi everyone,

Thanx for all your input.

I know i should have probably tried this earlier but i just tried creating a custom value list in the database that is displaying the list incorrectly.

Even though i put the data in as 08:00, 08:30, 09:00 etc, it still displays it as 08:00:00, so I'm guessing that there is some kind of file formatting that is only affecting that database!?

Does something like that exist, I just can't help feeling like this is a riduclously simple problem, that's probably my fault!

The database in question was supplied from Seedcode and i have tried to adapt it simply to display and edit my bookings in a large calendar form, if that's any help?

Thanx again for any ideas.

Mark B

[LaRetta]

Check the field-level number format on both displays of the value lists. You have two different number formats in the example files you posted [color:green](correction, I changed the format in testing it). Help says this about specifying formats for time fields:

...

You're finished unless the time format you chose displays seconds. If you want to format the seconds component to display fractional seconds, continue with the next step.

5.

Choose Format menu > Number.

6.

In the Number Format dialog box, select the formatting options you want.

Only two options affect the display of the seconds component: Fixed number of decimal digits and Decimal separator. The remaining options have no effect.

Do you have ApptTime as number format or your portal field in your real file?

Edited March 19, 2007 by Guest

[Mark Ballard]

Hi LaRetta,

I have made a clone of my database and stripped as much as i can out so i don't get any grief!

I have put in only 1 booking under my name in the "Patient" database. If you click on the "time" field you will see the value list appear, values from 08:00 to 19:00

In the "Calendar" database you should see the booking, click on that booking and it brings up a small edit appointment window. If you click on the "time" field in there it brings up the same value list but with the :00's added onto all values.

Most of the other things won't work on there as i've stripped it out but i think you should see my dilema. I'm all up for it being my mistake and happy to take the blame! I am concerned though that when you make a custom value list it displays it in the same way (00:00:00)

As ever thanx for your time

Mark B

Clone.1.zip

[Lee Smith]

Hi Mark,

I looked at your files, and the formatting can be traced back to the Bookings entry as I suggested in my earlier post.

If you go to the Bookings and then into Layout Mode, click on the [color:blue]AppointmentStartTime field, then the Format Time, you will see that you changed the format of the field to HH:MM. If you change this back to [color:blue]Leave Data as entered. and then go back to Browse Mode, and then click into the field, you will see that the data was entered using the HH:MM:SS format.

HTH

Lee

[Lee Smith]

Hi Mark,

I just looked at your latest files and I now see what you mean. I think you have found a Bug.

I'm not sure why this is acting this way. It might have do with the Relationship - Portal. I think the quick fix would be to create a second list in the Calendar and use it instead.

Lee

[Mark Ballard]

Hi Lee,

Yes you're right, but it has been entered as HH:MM:SS because the value list is appearing in that format in that file. The trouble is I don't want it to! The value list is just formatted as HH:MM and so why does it appear as HH:MM:SS in that file only?

I'm really sorry if i'm being a dullard here, it just deosn't make sense to me.

In the "Patient" database that holds the original value list, and where the bookings are normally made from, it displays fine and the time formatting there is set on "Leave data formatted as entered"

In The "Calendar" database, it doesn't seem to matter how you format the field it has no effect on the value list, that always appears with an extra :00 on the values, even if you put in custom values so it's not a problem with getting the wrong information from the original value list.

It's weird!

[Lee Smith]

Never mind changing the time value list to calendar, I just tried it, and it didn't work.

[comment]

This is a bit too complex for my taste, but here's what I did:

First, I CLONED your two files. By cloning, I mean 'Save a Copy As..' > Type: 'clone (no records)'.

Then, I sort of fixed your file references. For some reason, you have TWO references, Bookings and Patients, both supposed to point to the SAME file - only they weren't. Once I pointed them to the same file, Filemaker went into a never-ending loop. But after force quitting and reopening the files, it works the way you want it to.

[Mark Ballard]

Hi Comment,

Thank you for instructions. I followed them to the letter and no joy! Honestly I'm seriously doubting myself now.

I have tried cloning and sorting the references but it's still the same. I went back to the original two databases and checked the refernces there, and they were ok, just one reference in each file. I think i must have mistakenly added the second reference to "calendar" while stripping everything else out and trying to make it work.

I have checked it all through again but still no joy. I have tried making clones of the full original files but nope.

******* i thought you had it sorted there!

[comment]

What do you get with these two?

In case it doesn't work, try setting both files to 'Always use file's saved setting' (under File > File Options > Text).

Clone.zip

[Mark Ballard]

Right,

Well we're getting somewhere!

try setting both files to 'Always use file's saved setting' (under File > File Options > Text).

I set this and if i make another custom value list rather than the linked value list it does display as 08:00. I have also had to format the time field in the Patient database to be just HHMM rather than "leave as formatted"

However, and there's always a however...

If you change the time in the edit window in the calendar database, even though it is using the custom value list, it does put time in the field as HH:MM:SS.

I understand that that is the way that FM deals with timing, ie from midnight but what i don't undersatnd is why both databases seem to deal with it differently.

I think the Always use file's saved setting is part of the key here but i wonder if it is something to do with the SeedCode solution being written in a different version of FM.

I only have FM Pro 8 but i think that SC was written in advanced and i'm wondering if it has a slightly different formatting set?

[comment]

I don't think Advanced has anything to do with this. I do think (obviously) that Date & Time settings play a role here. I am not sure I understand exactly how, but my guess is that when the time has to be displayed in a DIFFERENT format, it goes thru another layer of conversion and loses the 'as entered' quality.

In any case, I have not been able to reproduce the added seconds in MY files (same as you), though I can see it on the other files in this thread - until I set them to use their 'native' settings.