What would cause related( to work for most, but not all fields in a database? I can get it to work with text, dates and numeric fields overall, but a few text fields absolutely refuse to work. I’ve double-checked names and spelling, and I’ve used catcherror(“X”,related({ProvinceFrom},“Workers”)) which comes up blank. There are just a handful of fields that routinely fail. Why? I can’t find anything about them that is different from fields that work, and yes, they have content.
Most likely you have either a global or fileglobal variable with the same name as the field. In any formula, if there is a variable with the same name as a field, the variable will take precedence.
Probably the best solution is to get rid of the variable, or at least rename it to something else. But I think you could also use the fieldvalue( function, though I am not 100% sure that this function will work in this context, but most likely it will. So you should be able to use:
related({fieldvalue("ProvinceFrom")},"Workers")
I think what probably threw you off here is you may have been thinking that the first parameter of the related( function was a field name. But it’s not – it’s a formula. So that’s why a variable value can sneak in there. I think this is a good thing, there are situations where being able to use a variable would be advantageous. But of course variables can always trip you up if you have a variable with the same name as a field.
Checking for like-named variables turned up one case, probably inadvertently caused by my efforts to get it working. Others still refuse to work.
I had seen the field name as the simplest form of a formula in related( and it works well - usually. It does work when adding fieldvalue( but not per your example. It requires naming the source database again, which just seems to go back to the field name alone being the simplest way to do it. So, if per Help, {FirstName+" "+LastName} is a valif formula, why not just LastName?
It requires naming the source database again
That actually makes sense. It’s not “again” - the formula specified in the first parameter has no knowledge of what the second parameter is. In your overall formula the active database is Donees, so that is the default database the fieldvalue( function will look in. So you’ll have to explicitly specify that every time you use the fieldvalue( function.
the simplest way
You’re not looking for the simplest way, you’re looking for a way that works.
So, if per Help, {FirstName+" "+LastName} is a valid formula, why not just LastName?
Both of those are valid formulas. But that doesn’t mean that LastName will refer to a field. If there is a variable named LastName then both of these formulas will refer to that variable, not to the field.
To debug this I think you’re going to need to change your mindset a bit. You originally asked why related( would work with some fields but not others. Phrasing the question that way is going to lead you astray. The related( function knows nothing about fields. It doesn’t deal with fields in any way. It only deals with a formula. Generally it would only make sense to use a formula that contains one or more fields in the second database, but the related( function doesn’t know anything about that. It just knows that you gave it some text that contains a formula, and it evaluates that formula to get the value. If you gave it a formula of "hello" it would happily return the text *hello" to you, even though that has nothing to do with the second database.
Perhaps if I provide a step-by-step explanation of how the related( function works that will help.
related({First+" "+Last},"Workers")
The first step is to locate the record in the Workers database that matches the key in the current record of the Donees record. If a match is found, it proceeds to step two.
Now that it knows what the matching record is, it calls Panorama’s formula evaluation engine to find out what the result of First+" "+Last is. The formula evaluation engine parses the formula, and identifies what the values of First and Last are. If variables exist with those name, the variable values will be used. Otherwise it will use the field values (or if there are no fields, an error is returned).
Note that there related( function does not parse the formula - it has no idea what is inside the formula (fields, operators, etc.). Panorama only has one formula evaluation engine which is used universally - any time Panorama needs to know the value of a formula, there is ONE subroutine that computes that. Whether you are calculating the value of an assignment, what to display in an object or a message statement, in the Formula Workshop, etc. - any time a formula value is calculated there is one subroutine inside Panorama that does that. There is not a special evaluator for the related( function.
Others still refuse to work.
I’m pretty confident that all of these will boil down to a variable you weren’t expecting. There’s no other explanation that makes sense.