Hello,

Is there a way to have a date as a number field? I use several concatenated fields as a match field for a number of lookups and matching-record imports. All of the fields are number fields except for a date field, which has meant I use the numtotext and datetotext functions and the match field is defined as a text field.

The lookups and imports involve large number of records, and I've noticed FM is much slower processing text fields in this situation compared with number fields. If there is some way of using a number for the date, so the match field can be a number field, and both FM and I will be happier!

If you are just using the date field in your concatenations, and you don't care how much it looks like a "real" date, then you could embad a DateToText within a TextToNum directly in your concatenation, like:

TextToNum(DateToText(DateField)) & OtherFields

Steve Brown

Date already IS a number. You can see that if you define a calculation field (result is number ) = datefield.

However, numbers as such cannot be concatenated, so perhaps this is not getting you anywhere.

Thanks very much for the replies. I think both solutions work. Comment - why do you say numbers as such cannot be concatenated? I tried it and it seems to work fine.

Because concatenation is a text operation. If it 'seems to work', it means that the number has been converted to text. These data type conversions need watching, or you might get some weird results.

I, too, have used concatenated number fields without any trouble. The calc on the concat is result number, and the fields being concatenated are defined as number. Nowhere is there a conversion to text, unless FM converts to text for the concat, then immediately converts the result back to number, because result number is specified. Weird & seems unlikely.

The only issue I've encountered that bears watching is the strippinjg of leading zeros in text to num conversion. If you are building a key field, this could become important. It will also affect sorts, as 00214 becomes 214, which will sort ahead of 21399. But if the source data for the concat never contains leading zeros, there ought to be no problem.

I am working in FM 6, though., as is the original posteer This behavior may differ in 7.

Steve Brown

I can only repeat what I said before: concatenation is a text operation. You cannot concatenate numbers as such, just as you cannot multiply characters or raise a container to the power of 2. This is not a Filemaker issue. It's a matter of definition of what is a number.

Yet there my test file sits, right next to this browser window. Two number fields (clearly so defined) and a calc field (result number, concatenating the first two) are staring at me accusingly from my monitor, the data within them flawlessly concatenated.

I also have the knowledge that my elaborate 80-file FM 6 served solution has been ticking along for three years, using concatenated number fields as key relationship fields in many places. Not a single error regarding those key fields has ever occurred.

What am I missing?

Steve Brown

I believe you are missing the conversions that Filemaker makes for you, instead of forcing you to write:

TextToNum (

NumToText (number1) & NumToText (number2)

)

Nevertheless, that is exactly what happens.

Well, if that's what FM is doing, then it seems to make no difference.

One way I am aware I could have gotten tripped up is if the number fields were set to display in any way other than originally entered. Show two decimals, for example, when the real number goes to four decimals.

But if you are careful, and concatenate the kind of number fields that neither have any, nor need any number formatting, then even though FM will convert to text and back, the resulting concatenation can be accurate and useful.

Steve Brown

if you are careful

That was my point.