Calculated Container field issues

[sublunar]

I'm trying to make a container field that is filled or not depending on a calculation.

If I have a Table "MyTable" with a field "N25", I can do a calculation:

Substitute ( GetFieldName( Self );"MyTable::N";"")

and that will put the characters "25" into the field.  It doesn't seem to matter if the field is number or text.



But what I want is a container field that takes the contents of another container field if the numerical part of its name matches the contents of a third field:



Example:



I have 3 fields D25; N25; Dot.  D25 is a number field.  Dot is a global container field containing a graphic object. N25 is a calculated field that returns a container object.



Basically, if D25 equals 25 then N25 should equal Dot.

or more generally: if Dx = x then Nx = Dot.



The point of this exercise is to create a bunch of fields that all have the same calculation, but respond appropriately to data input.



So the idea is that I put a number into field Dx and if x is equal to the number I entered, then container field Nx will equal container field Dot.  Otherwise Nx will be empty.  



The purpose is to convert numerical data into dots on a form.  Each dot has a field Nx corresponding to its location.



So this is what I came up with, that doesn't work-- look at Nx's "x" and compare it to the contents of Dx to choose whether to populate Nx with the contents of Dot.

If (GetField ("D" & Substitute ( GetFieldName ( Self );"MyTable::N";"")) = Substitute ( GetFieldName ( Self );"MyTable::N";"");Dot;"")

Why doesn't that work? Do I have to convert text to numerical?

An alternative I found is conditional formatting, but the problem is that I have hundreds of dots. I don't want to have to edit the code in every calculated field. It's much more efficient if the name of the field controls the calculation.

Thanks for any help...

[comment]

If (GetField ("D" & Substitute ( GetFieldName ( Self );"MyTable::N";"")) = Substitute ( GetFieldName ( Self );"MyTable::N";"");Dot;"")

Why doesn't that work?

I don't know - it worked for me when I tried it (see attached). However, this could be a LOT simpler using repeating fields.

atest.fp7.zip

[sublunar]

Hey thanks.

As for the idea of using repeating fields... that's something I've never looked into.

At first I thought your example didn't apply because your "Result" field and "N" fields were not container fields. But changing them in your example database gives me the desired result.

Then I change "Result" to a Global field and the calculation no longer works. Annoyingly, it continues to fail if I turn off global storage.

I cannot reference a Globally stored Container field in a calculation. I get no error, but the stored image never appear.

Is this a bug or a feature?

Edited October 14, 2009 by Guest

[comment]

Yes, it seems there is a problem with using GetFieldName ( Self ) in an unstored calculation:

http://forum-en.filemaker.com/fm/board/message?board.id=aut&thread.id=2111

Anyway, I'd suggest you try the attached instead.

atestR.fp7.zip

Edited October 14, 2009 by Guest
Added a reference to previous bug report