Custom Functions don't see fields

Given the following custom functions:

RegisterCustomFunction "", "GETCOMPANY_1(",0,||||

RegisterCustomFunction "", "GETCOMPANY_2(",0,||||
    Execute({FunctionValue «Company»})

And using them as follows:

    "GetCompany_1() => ["+GetCompany_1()+"]"
    +"GetCompany_2() => ["+GetCompany_2()+"]"

I’m seeing the following results:

GetCompany_1() => [Field or variable “Company” does not exist.]
GetCompany_2() => [Bella Jule Fine Jewelry]

(You can probably repro this by substituting Company for any field in your database)

I realize I’m probably just a bit brain-dead this morning.

Would somebody explain why? :slight_smile:

Thank you!

Can’t say why your first version does not work (I tried my own experiment with the same results), but this alternative code does seem to work properly.

RegisterCustomFunction "", "GETCOMPANY_1(",0,||||
    fieldvalue(«Company») |||
1 Like

Gary: thank you for affirming (a little bit of) my sanity. :slight_smile: Thank you also for the alternative method to get the value.

Custom functions are not directly related to any particular database, so they cannot directly access database fields. You can get around that by specifying the name of the field, as Gary’s technique does.

In this situation, I’m not sure why you don’t just use the field name. Perhaps you are planning to create a more complicated custom function that happens to include references to fields.

I think this technique of using the fieldvalue( function will be slower if the formula is used in a loop, for example in a search or arraybuild. When a field is referenced in a formula the first time, the field number is determined and cached. So for example if you search thru 10,000 records using the field directly, it will only look up the name of the field once - the next 9,999 times it will already know the field number. Using the fieldvalue( function, this caching will not happen, it will have to convert the field name into a field number every time. Depending on your application this could be a significant difference.

Yeah, I actually started with a long, complicated function that completely failed.
However, it works fine when used “in-line” as a formula.

It took me a while to reduce it down to just the field name being the issue.

Since I’m looking up a pile of different/unique fields, I don’t think the caching would help the speed.

Thanks for the comments. As always, very helpful.

I don’t know what “looking up a pile of fields” means.

Whether or not the caching helps depends on how you use the formula. If the formula is used in any kind of loop, the caching will make a difference. Loops would include searching, selecting, arraybuild, etc., or also a loop in your code.

So for example

select Company = "Acme"

will be significantly faster than

select fieldvalue("Company") = "Acme"

Just FYI, I simply want to make sure you are aware of the tradeoffs.

(By the way, there would be no speed difference between these two methods in Panorama 6. Panorama X has a new optimization for accessing fields in a formula that Panorama 6 did not have.)