GetAsText ( “” )

I should note that I think a lot of people, within the context of FMP, do think of "" as Null/null -- and I don't find a whole lot of reason to push back on that. Within the context of FMP, I think both terms are ok -- I was just stating a personal preference, based on how I conceptualize it.

1 Like

Steve, I agree with you on this.

The only important thing is how functions react to the method use.

Regards
Andy

2 Likes

I think this also depends on the field type. For example, with a Number field, you can store a text value, and FileMaker ignores the alpha and only processes the numerics. I have some tables where my SerialNumber field is a Number, but stored with a prefix indicating the table name, e.g.

Table: Customers Field:Serial Number AutoEnter Serial, starting value "cu0000001", autoincrement

Also, I just ran into a potetial "gotcha" where FileMaker would accept an "invalid" date during data entry, parse as as a valid date in all FileMaker situations, but then export it using the original (invalid) date format to an external service. See Partially Valid Dates - #2 by xochi

I'm intrigured by this idea that empty string (''") is not the same as NULL in FileMaker. Are there situations where this matters?

1 Like

FM handles ‘end of string’ internally. For developers, only the content of a string matters (this includes empty string). I am not aware of any other cases.

2 Likes

I think calling the GetAsText function on an empty string is a waste of effort. All it will do is return the parameter that it was passed. It falls into the category of things that give the appearance of doing something important but really have no other purpose.

Thanks for adding the link to the standards @robertjackson. The fact is that FMP does not implement null. Creating a CF that returns an empty string is not the same thing. That becomes obvious in the discussion over there as they describe all the odd behaviours of their null implementation in an FMP context. It would only satisfy one of the meanings below - and it would create potential conflicts with the SQL reserved words.

  • Null (SQL) (or NULL), a special marker and keyword in SQL indicating that something has no value
  • Null character, the zero-valued ASCII character, also designated by NUL, often used as a terminator, separator or filler. This symbol has no visual representation
  • Null pointer (sometimes written NULL, nil, or None), used in computer programming for an uninitialized, undefined, empty, or meaningless value
  • Null string, the unique string of length zero (in computer science and formal language theory)
5 Likes

It would be nice for some clarity here. My mental model is that FileMaker will store anything in any field (which is both handy, and kind of terrifying from a computer science perspective) but that if you want a "clean" version of the data, you can use the "GetAsX()" functions,

However, that appears to be wrong: this assumes "GetAsDate()" would return you a valid date object, in the "proper" format, but as experienced here: Partially Valid Dates that's clearly not how it works.

As an example, here's a quiz.
Run this in the Data Viewer/Watch panel. Before you do, predict what each line will give you.

GetAsNumber("1 - 7") & "¶" &
GetAsNumber("x df 0. ___ & * 0.   0.  0    7") & "¶" &
GetAsNumber("serial000001") & "¶" &
GetAsText("serial000001") & "¶" &
GetAsDate("09/11/2001")  & "¶"  &
GetAsDate("09.11.2001")  & "¶"  &
GetAsDate("09;11;2001")  & "¶"  &
GetAsDate("09_11_2001")  & "¶"  &
GetAsDate("09 11 2001")  & "¶"  &
GetAsDate("2001 09 11")  & "¶"  &
GetAsBoolean ( "" ) &  "¶"  &
GetAsboolean ( "foobar") &  "¶"  &
GetAsboolean ( "1foobar") &  "¶"  &
GetAsboolean ( "foobar1") 

Some of the results are rather surprising.

4 Likes

Love that data viewer quiz. Thanks for that!

2 Likes

GetAsText allows you to type a non-text value explicitly as text.

Example… If you copy a date field's value into a variable, the variable type is date. If you wanted to ensure that the variable have a text value, you would use GetAsText to force the type change.

There are ways to implicitly change a value type, notably by performing an operation whose input value is a text value. Thing is, some implicit type changes do not produce the desired result because an operation could accept multiple value types as input.

3 Likes

I should clarify that I am only saying that GetAsText(“”) is a waste of time when you pass an empty string in the parameter. Calling the function on an empty string returns an empty string.

3 Likes

Interesting topic! - I checked to see if there was any official advice.

https://support.claris.com/s/article/How-to-work-with-Null-characters-in-FileMaker-Pro-and-FileMaker-Pro-Advanced?language=en_US

4 Likes

and do not forget to add language/localization to it especially for dates :slight_smile:

This method inputs a NULL character in a field. In SQL Null is not a value, it's a state. When a field is Null, that means the field is not set to anything, not that it's empty. This is a notion that can't be reproduced in FileMaker. The only provision for something close in FileMaker is that a field can't be set empty.

5 Likes

I think this is a good point, and it's my personal motivation for sticking to the term "empty string" when I am talking about "".

But, I also think that, within the context of FMP development, some of the blurriness around this topic is understandable. One reason is because historically (within the context of FMP development) there has been a habit of using the term "null" to refer to "". As @Malcolm pointed out, you can see this in the discussion over on FileMaker Standards.

Another reason for the blurriness is that we can use ExecuteSQL to search for a field which has been set to "" by using the NULL keyword in the SQL query. And so, right there, we have something built into FileMaker which could be taken as encouragement to refer to the empty string as Null.

It feels similar to language I grew up with as a youth: People would often say "Hand me a Kleenex", when, technically, we all knew that they meant "hand me a tissue". (Admittedly, this is a flawed analogy, as sometimes a "tissue" is a "Kleenex", and not so with "" and null -- but hopefully it still makes some sense.)

4 Likes

Just adding another aspect to this discussion. I have always used Set Field "" myself, but have often wondered why I don't use Clear. With Select entire contents option checked it seems logical that this produces the same result. Any thoughts?

1 Like

I haven't used it for a long time, but IIRC, Clear requires that the field be present on the layout, whereas Set Field does not have to meet this requirement to get the job done.

The memory is foggy now, but I think that Set Field was introduced in v.4, and in v.3 we still had to do some things with copy, paste, and clear to update fields. At the time it was introduced, Set Field seemed like an amazing gift towards more robust scripting.

6 Likes

Ahh, yes. No wonder I don't use Clear! The ability to use Set Field without having to have the field present on a layout is a huge plus.

2 Likes

In the old days, to get a null value I used to define a global field but never enter anything into it. Referring to it gave me a null.

4 Likes

Clever idea!

I was reading through this string wondering if/when someone was going to mention the Clear function.. Personally, I never use it. I've never seen the point when SetFeild="" works so well. I think steve_ssh brought up a great point with the field being required on the layout for the Clear function to work. I always wondered if I might be missing something here but after reading through all of these comments I think I will stick to the old fashioned SetField method...

I do have a couple of notes to make about using Replace Field Contents.. This function does not require a calculation such as GetAsText( "" ), you could just use "" and that would work just the same and would probably be even faster since it doesn't have to calculate anything. The other thing I always point out is that using Replace Field Contents on any field that is not a Global field will result in the modification timestamp and modified by fields to get updated thus making it difficult to audit who edited the record last. I always try to avoid that if possible. Also, it can be a dangerous function if any mistake is made with creating the found set.... just some thoughts.

3 Likes

This is an important consideration for anyone who wants to be able to rely on the metadata associated with any data set. Thanks for raising it @KyleWilliams.

The safe alternative is to generate the data needed for the replacement in a file and import it. Imports can be performed without triggering updates.

2 Likes