Why doesn't this calc work?

[Darren Burgess]

The following unstored, Calculation yields a result of "Yes":

field_Test = Case ( RightWords( GetFieldName ( Self ) ; 1 ) = "Test" ; "Yes" ; "No" )

The following unstored calc, where Field is any field in the table, yields no result (Null):

field_Test = Case ( RightWords( GetFieldName ( Self ) ; 1 ) = "Test" ; Field ; "No" )

Why does this not work? That is, why does the calc work when the true case result is literal text, and it does not work when the true case result is a field. Note that the calc result is set to the correct data type, so date type mismatch is not the issue.

Darren

Darren Burgess

[Darren Burgess]

Clarification - This issue is related to the storage of the calc.

So this calc:

field_Test = Case ( RightWords( GetFieldName ( Self ) ; 1 ) = "test" ; AnyField ; NULL )

When the results are unstored, either because it is manually set to unstored or because AnyField is an unstored field, then the calc breaks.

Why?

[David Jondreau]

Hmmm...the more reductive question is:

An unstored calc field = GetFieldName(Self) returns the correct result.

An unstored calc field = RightWords(GetFieldName(Self) returns a null result.

Weird.

In fact,

Let(

$name = GetFieldName(Self);

$name)

returns a null result, though the var $name is created.

[comment]

Can you post a file showing this? I suspect your first calculation doesn't work either.

[David Jondreau]

This should do it...

mysandbox.fp7.zip

[comment]

If you want to reduce the question even further, try:

GetFieldName ( Self ) & "X"

[David Jondreau]

So what the heck is going on?

1) For:

GetFieldName ( Self ) & "X"

I get TABLE::Field

2) For:

"X " & GetFieldName ( Self )

I get X TABLE::Field

3) For

Let([

$start = "Start";

$name = GetFieldName(Self);

$end = "End"];

1

)

I get a null result, but the $start and $end vars are populated.

If I change the storage from unstored to stored, I get the correct result.

Edited March 30, 2009 by Guest

[comment]

I don't know. Frankly, I'm surprised that ANY reference to Self in an unstored calculation doesn't return an error. But in fact Self + 1 returns 1, which is clearly incorrect.

I suppose there is some safeguard in place to prevent an infinite loop in case of circular reference, and it kicks in even though GetFieldName ( Self ) isn't really a reference to self.

[Darren Burgess]

ok, from another forum I got this response.

"Ah!

In an unstored calc the SELF function (or any self reference) doesn't resolve until the whole calculation finishes. It is the stored value which allows the GETFIELDNAME to resolve correctly. Not sure how to get around this."

Darren

[Darren Burgess]

further clarification of the actual problem to solve:

Basically what I was trying to do, in a calculation field, was to use exactly the same calculation for each of several fields. Looking something like this:

calcField_Nurse = Case ( RightWords (GetFieldName ( Self ) ; 1 = Category ) ; anotherCalcField ; NULL )

calcField_Surgeon = Case ( RightWords (GetFieldName ( Self ) ; 1 = Category ) ; anotherCalcField ; NULL )

etc, etc

Where category is one of several person categories ( Nurse, Physician, Surgeon, etc) and anotherCalcField is unstored since it references another table.

I have sort of solved this with a custom function, but I am curious if it is possible to do this directly in the calculation.

Darren

[comment]

from another forum I got this response.

"Ah!

In an unstored calc the SELF function (or any self reference) doesn't resolve until the whole calculation finishes. It is the stored value which allows the GETFIELDNAME to resolve correctly. Not sure how to get around this."

Could you name the source of this information, please?

I am not sure what exactly is your overall purpose here. Perhaps if we knew that, a simpler solution would present itself.