I want to replace the contents of table::field with a null value. I’ve seen some developers use Set Field ( “” ) and others use Replace with Calculation: GetAsText ( “” ). Any advantage to the GetAsText methodology?
“” clears a field.
The return value of GetAsText (“”) is undefined, according to the technical documentation.
Char (0) returns an empty string, according to the technical documentation.
2 Likes
I seem to remember reading about this on FileMaker Standards website
https://filemakerstandards.org/plugins/servlet/mobile?contentId=557129#content/view/557129
2 Likes
Hi Steve,
My experience leads me to believe that, for all practical purposes within the context of the FMP calculation engine:
The use of an empty string, i.e. "", and the empty string wrapped with GetAsText, i.e. GetAsText( "" ), are interchangeable with respect to the result that they yield.
Any preference towards one or the other, then, would have to do with readability or simplicity. Now, mind you, I can't back the above up with under-the-hood knowledge, but I believe it enough to post this reply.
Personally, I would not refer to either of these two as a Null or null value, though I accept that others might. I generally refer to them as an "empty string".
As for an approach to updating a field via Set Field or via Replace Field Contents, there are certainly differences. Replace Field Contents is a very handy tool, but I generally save it for cases where it is ok if there is an update failure (commonly due to a record lock). So -- I might use it if I needed to tinker around with some data manually, and I'm able to subsequently verify that my update worked out ok. But in cases where I am scripting business logic, I generally prefer to use Set Field with some error capturing. There may be certain instances where it is tolerable for some failure in the update, and in those cases I might use Replace Field Contents.
Hope this is of some help.
2 Likes
Thanks for the distinction. One of the things I’m enjoying is learning something new every day. I appreciate your help.
1 Like
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.
4 Likes
and do not forget to add language/localization to it especially for dates 
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