DataType ( )

Function stats

Average user rating
37
161
9999
Support
FileMaker 7.0 +
Date posted
23 December 2008
Last updated
15 May 2009
Version
Recursive function
No

Author Info
 Fabrice

74 functions

Average Rating 4.4

author_avatar



 

Function overview

Prototype

DataType  ( _data )


Parameters

_data  anything


Description

Tags:  Variables   Dev   Debug  

returns the type of a data ("Number, TimeStamp, Date, Time, Container, or Text )

Examples

Sample input

DataType ( "3/2" )
DataType ( 3/2 )
DataType ( $myFile )


Sample output

Text
Number
Container

 

Function code

/* DataType ( _data )

by Fabrice Nordmann

v.1.2 Mar 2009
Bug fix
v.1.1 Jan 2009
Bug fix (TimeStamp)
v.1 Dec 2008

returns the type of a data ("Number, TimeStamp, Date, Time, Container, or Text )

Many thanks to Daniele and Agnès for their help.


*/
Let ([
_decSeparator = Middle ( 3/2 ; 2 ; 1 )
; _timeSeparator = Middle ( Time ( 12 ; 12 ; 12 ) ; 3 ; 1 )
; _dateSeparator = Middle ( Date ( 12 ; 12 ; 2000 ) ; 3 ; 1 )
; _text = GetAsText ( _data )
; _time = Substitute ( _data ;[ "." ; _timeSeparator ]  ; [ ":" ;_timeSeparator ] )
; _dateSplitted = Substitute ( _data ; _dateSeparator ; " " )
; _date = Right ( 0 & MiddleWords ( _dateSplitted ; 1 ; 1 ) ; 2 ) &_dateSeparator & Right ( 0 & MiddleWords ( _dateSplitted ; 2 ; 1 ) ; 2 ) & _dateSeparator & Right ( "20" & Right ( 0 & MiddleWords ( _dateSplitted ; 3 ; 1 ) ; 2 ) ; 4 )
];
Case (
IsEmpty ( _data ) ; ""
// CONTAINER
; not IsEmpty ( FilterValues ( Left ( _text ; Length ( "filexxx:" )) ; "filemac:¶filewin:" ))
or not IsEmpty ( FilterValues ( Left ( _text ; Length ( "imagexxx:" )) ; "imagemac:¶imagewin:" ))
or Left ( _text ; Length ( "file:" )) = "file:"
or Left ( _text ; Length ( "image:" )) = "image:"
or Left ( _text ; Length ( "size:" )) = "size:"
or Length ( _data ) <> Length ( _text )
; "Container"
// TEXT (if ¶)
; PatternCount ( _data ; ¶ ) ; "Text"
// NUMBER
; Exact ( Filter ( Substitute ( _data ; [ "e+" ; "" ] ; [ "E+" ; "" ] ; [ "e-" ; "" ] ; [ "E-" ; "" ]) ; "1234567890-" & _decSeparator ) ; Substitute ( _data ; [ "e+" ; "" ] ; [ "E+" ; "" ] ; [ "e-" ; "" ] ; [ "E-" ; "" ] ) )
and ( Left ( _data ; 1 ) <> "0"
or _data = "0"
or Left ( _data ; 2 ) = "-0"
or Left ( _data ; 2 ) = "0" & _decSeparator
)
and _data <> "-"
; "Number"
//TIMESTAMP
// FileMaker unfortunately shrinks the last ":00" for timestamps, we'll consider they are timestamps, not text.
; WordCount ( _data ) >= 2 and GetAsDate ( _data ) = GetAsDate ( GetAsTimestamp ( _data )) and IsValid ( GetAsDate ( _data )) and GetAsTime ( _data ) = GetAsTime ( GetAsTimestamp ( _data )) and IsValid ( GetAsTime ( _data )) ; "TimeStamp"

//DATE
; WordCount ( _dateSplitted ) = 3 and GetAsDate ( _date ) = GetAsDate ( _data ) and IsValid ( GetAsDate ( _data )) ; "Date"
//TIME
; Exact ( GetAsTime ( _data ) ; _time ) or Exact ( GetAsTime ( _data ) ; _time & _timeSeparator & "00" ) ; "Time"
//TEXT
; "Text"
))

// ===================================
/*

    This function is published on FileMaker Custom Functions
    to check for updates and provide feedback and bug reports
    please visit http://www.fmfunctions.com/fid/161

    Prototype: DataType( _data )
    Function Author: Fabrice (http://www.fmfunctions.com/mid/37)
    Last updated: 15 May 2009
    Version: 2.12

*/
// ===================================

 

Comments

Daniele Raybaudi
23 December 2008



Nice one, Fabrice !

And now my comments:

1) IsNumber: where do you lost the zero ?

2) I'm curious: why 3/2 and not 1/2 ( with Left() )?

3) A FileMaker number field accepts the thousand separator .
(Edited by Daniele Raybaudi on 23/12/08 )
  General comment
Daniele Raybaudi
23 December 2008



4) It do not see a time field if the current separator is "."

5) IsContainer fails somewhere...searching where !
(Edited by Daniele Raybaudi on 23/12/08 )
  General comment
Fabrice
23 December 2008



Hi Daniele,
thanks for your comments.
Not sure I understood 1), but I updated the function so if a "number" has a leading 0, it's text.
2) why not, indeed. :)
3) Yes but if you GetAsNumber it, it looses it, as for the leading 0. What would you like the function to do in this case ?
4) Updated - hope it works
5) I think I tested images (stored or reference), files (stored or reference), quicktime, sound, pasted layout objects.

Thanks so much for your feed-back
  General comment
Agnès
23 December 2008



Hello,

DataType ( "4/5/2008" ) give Text,
DataType ( "04/05/2008" ) give Date
DataType ( 4/5/2008 ) give Text
DataType ( 4/5/08 ) give Number
is normal ?

DataType ( GetAsNumber ( 4/5/08 ) ) = number
but DataType ( GetAsNumber ( 4/5/1908 ) ) = text

Thanks for Test Beta ! good night !

Agnès
(Edited by Agnès on 23/12/08 )
  General comment
Daniele Raybaudi
23 December 2008



"Not sure I understood 1)..." ----> 1000 is a number ! ;)
You miss the 0 in your calc for IsNumber.

"3) Yes but if you GetAsNumber it, it looses it, as for the leading 0. What would you like the function to do in this case ?"

I do not know for sure but 1.001,12 is a number, not a text. Maybe using NumToJText ( ) ( like a sometime ago your idea )

5) there must be some conflict with the IsTimeStamp... sometimes that is the result.
(Edited by Daniele Raybaudi on 23/12/08 )
  General comment
Daniele Raybaudi
23 December 2008



BTW: your CF works really with any data... you sure know that it works even with $var !
[ vs FieldType ( ) ]
(Edited by Daniele Raybaudi on 23/12/08 )
  General comment
Fabrice
23 December 2008



Ooops :-)
Fixed now (zeros). Also, I found a case where a container would not be recognized (if it contained some text). Fixed now.
Agnès, hope it's fixed.
For variables... well, you understood it was the point I'm sure ;-)
(Edited by Fabrice on 23/12/08 )
  General comment
Agnès
23 December 2008



Fixed... It's really depends to you,
if DataType ( "08" ) is a date and DataType ( "208" ) is number, is fixed.... but ;)

Fixed or on way ?

euh... now, DataType ( "0¶1¶2¶3" ) and DataType ( "a¶b¶c¶d" ) are date ? sure ?

edit too ;) : ok for v1.6 but DataType ( "08" ) give text
(Edited by Agnès on 23/12/08 )
  General comment
Daniele Raybaudi
23 December 2008



"5) there must be some conflict with the IsTimeStamp... "

The new version, evaluating the ISContainer BEFORE the IsTimeStamp, works.
  General comment
Fabrice
23 December 2008



Agnès, new report corrected. Thanks to both of you. Now time to sleep for me, but I'm sure I'll wake up tomorrow with many more bug reports :)
I really enjoy this cooperation !
(Edited by Fabrice on 23/12/08 )
  General comment
Agnès
23 December 2008



// DataType ( "¶" ) and DataType ( " " ) = TimeStamp
// DataType ( "-" ) = Number
// DataType ( 5 > 0 ) = Number but DataType ( 5 < 0 ) give Text

ok, I stop here !
I know this is nonsense writing, but a calculation must operate at all times ( He says to me ;) )
(Edited by Agnès on 23/12/08 )
  General comment
Daniele Raybaudi
23 December 2008



// DataType ( 5 > 0 ) = Number but DataType ( 5 < 0 ) or give Text

Agnes, the first one is good ( True = 1 ), the second one is also good ( False = 0 ) if Fabrice stated that 0 isn't a number. He have to change the IsNumber part of the CF to evaluate a SINGLE zero as a number ( not text ).
  General comment
Daniele Raybaudi
23 December 2008



My last comment for now ( not Today, just tomorrow ! )

The time part must be corrected to something like:
; _time = Substitute ( _data ;[ "." ; _timeSeparator ] ; [ ":" ;_timeSeparator ] )
AND
Exact ( GetAsTime ( _data ) ; _time ) or Exact ( GetAsTime ( _data ) ; _time & _timeSeparator & "00" ) ; "Time"
  General comment
Fabrice
23 December 2008



Good morning. All these reports were fixed.
Amazing how many bugs you can report !
  General comment
Daniele Raybaudi
23 December 2008



Good morning, Fabrice

next step will be the simplification procedure.
  General comment
Daniele Raybaudi
23 December 2008



Another thought...
Are we sure that it is a good thing to have a result in plain English ?
What about:
-1 ( container )
0 ( text )
1 ( number )
2 ( date )
4 ( time )
8 ( timestamp ) ?
  General comment
Fabrice
23 December 2008



Hi Daniele,
just updated with many changes.
Not sure about English vs codes. Functions like GetAs... don't understand codes. Plus it's something more to remember...
I made this test case (should return only positive results)

Let ( _trigger = text ;
// DATES
List (
"DATES"
; Case ( DataType ( "21/10/2008" ) = "Date" ; 1 ; -1 )
; Case ( DataType ( "21/1/2008" ) = "Date" ; 2 ; -2 )
; Case ( DataType ( "08/08/2008" ) = "Date" ; 3 ; -3 )
; Case ( DataType ( "8/8/2008" ) = "Date" ; 4 ; -4 )
; Case ( DataType ( "8/8/08" ) = "Date" ; 5 ; - 5 )
; Case ( DataType ( "8/8/8" ) = "Date" ; 6 ; - 6 )
; Case ( DataType ( "10/10/2008¶" ) = "Text" ; 7 ; - 7 )

; "NUMBERS"
; Case ( DataType ( "0" ) = "Number"; 1 ; -1 )
; Case ( DataType ( "-0" ) = "Number" ; 2 ; -2 )
; Case ( DataType ( -,05 ) = "Number" ; 3 ; -3 )
; Case ( DataType ( "01" ) = "Text" ; 4 ; -4 ) & DataType ( "01" )
; Case ( DataType ( "1¶" ) = "Text" ; 5 ; -5 )
; Case ( DataType ( 10^50 ) = "Number" ; 6 ; -6 )
; Case ( DataType ( -10^50 ) = "Number" ; 7 ; -7 )
; Case ( DataType ( 10^-50 ) = "Number" ; 8 ; -8 )
; Case ( DataType ( "208" ) = "Number" ; 9 ; -9 )

; "TEXTS"
; Case ( DataType ( ¶ ) = "Text" ; 1 ; -1 )
; Case ( DataType ( " " ) = "Text" ; 2 ; -2 )
; Case ( DataType ( "toto" ) = "Text" ; 3 ; -3 )
; Case ( DataType ( "3+2" ) = "Text" ; 4 ; -4 )
; Case ( DataType ( "32/13/2008" ) = "Text" ; 5 ; -5 )


; "TIMESTAMPS"
; Case ( DataType ( "21/10/2008 10:10:10" ) = "TimeStamp" ; 1 ; -1 )
; Case ( DataType ( "21/10/2008 10:10:00" ) = "TimeStamp" ; 2 ; -2 )
; Case ( DataType ( "21/10/2008 10:10" ) = "TimeStamp" ; 3 ; -3 )
; Case ( DataType ( "21/10/08 10:10" ) = "TimeStamp" ; 4 ; -4 )
; Case ( DataType ( "21/10/8 10:10" ) = "TimeStamp" ; 5 ; -5 )
; Case ( DataType ( "1/6/2008 10:10" ) = "TimeStamp" ; 6 ; -6 )

; "TIMES"
; Case ( DataType ( "10:10:10" ) = "Time" ; 1 ; -1 )
; Case ( DataType ( "10:10:00" ) = "Time" ; 2 ; -2 )
; Case ( DataType ( "10:10" ) = "Time" ; 3 ; -3 )
; Case ( DataType ( "10:10:00AM" ) = "Text" ; 4 ; -4 )
; Case ( DataType ( "1:1:00" ) = "Text" ; 5 ; -5 )
; Case ( DataType ( "1:1" ) = "Text" ; 6 ; -6 )
; Case ( DataType ( "1:01" ) = "Text" ; 7 ; -7 )
))
(Edited by Fabrice on 23/12/08 )
  General comment
Daniele Raybaudi
23 December 2008



Ummm...
This is why i like the result as a number:

If [ DataType ( $var ) = 2 ; "You need to enter a valid timestamp, not a date !" ]

If [ not DataType ( $var ) ; $var ]

I do not understand your last post.
  General comment
Fabrice
23 December 2008



Just a test case (for me, but as you and Agnès are now involved in testing beyond expectations, I thought you might want it as well.

As for your examples :
If [ DataType ( $var ) = "Date" ; "You need to enter a valid timestamp, not a date !" ]
If [ DataType ( $var ) = "Text" ; $var ]
I don't see a big difference. Agnès (and others), what do you think?
  General comment
Andries Heylen
08 October 2010



for the date type it only works for dates after 2000. Is my birthdate not a valid date :-(

Maybe you could do the following for the _date parameter. It checks if the provided year has a length of 4 characters, if so, it returns simply the year, otherwise it will format it into a valid 2000+ year. Or is this modification breaking another functionality?

; _date = Right ( 0 & MiddleWords ( _dateSplitted ; 1 ; 1 ) ; 2 ) &_dateSeparator & Right ( 0 & MiddleWords ( _dateSplitted ; 2 ; 1 ) ; 2 ) & _dateSeparator & Case ( Length ( MiddleWords ( _dateSplitted ; 3 ; 1 ) ) <> 4 ; Right ( "20" & Right ( 0 & MiddleWords ( _dateSplitted ; 3 ; 1 ) ; 2 ) ; 4 ) ; MiddleWords ( _dateSplitted ; 3 ; 1 ) )


You could even make it more dynamic by changing the "20" into Left ( Year ( Get ( CurrentDate ) ) ; 2 ), so in the year 3000 they can still use this custom function!
(Edited by Andries Heylen on 08/10/10 )
  General comment
Fabrice
08 October 2010



Hi Andries,

I thought you were younger ;)
Sorry, I can't remember why we had this. I let you try and tell me what kind of new bug it creates ;)
Just a small question: why are you using this function for ? I think I only used it once, so I'm just curious.
  General comment
Andries Heylen
12 October 2010



As you are borrowing sigarets from me for the past two years, you should know it is illegal to borrow sigarets from someone who was born after 2000, so you should now better about my birthdate ;-)

I use it for defining a sort field in a portal, where I use the technique of Exelisys to convert everything to a number. First I created for each datatype a different global field... but that was not efficient and too much work to implement and make it a portable solution. So that is why I wanted to use your function, so I could now if it was a date, number or string. This result in the following function:

Let (
[
_value = GetField ( zg_SortFieldName )
; _type = DataType ( _value )
]
;
Case (
_type = "date" ; GetAsNumber ( _value ) ;
_type = "text" ; SortNumeric ( _value ; "" ) ;
_type = "number" ; _value
...
)
)

But I am sure there is a smarter way, and you will tell me. But I didn't have much time to reflect on how I could do this in the most efficient way, and as the number of portal rows is limited, performance is not really an "issue". On the other side I really want to use this numeric sort, as in the future I want to extend this function to allow sorts on multiple columns.
(Edited by Andries Heylen on 12/10/10 )
  General comment
Fabrice
12 October 2010



Hi,

great minds...
I also wrote this function for a sort challenge. Funny.
Cheers,
Fabrice
  General comment
Andries Heylen
12 October 2010



you know what is funny: that this function has probably the most comments, but might be used only twice... for me... that is funny :-)
  General comment

 

 

 

 

 

Top Tags

Text Parsing  (33)
List  (31)
Date  (28)
XML  (26)
Format  (23)
Sql  (22)
Dev  (20)
Debug  (17)
Interface  (15)
Layout  (15)
Text  (14)
Variables  (12)
Layout Objects  (11)
Filter  (11)
Design  (10)
Array  (7)