Problem updating calculated field based on a relationship
Chris McKay
I have a strange problem. I have 3 databases. The first one
(database1) consist of only one record, holds a number field, indexed
(numeric1).
The other two (database2 and database3) are related among them
(relationship1) based on two calculated fields (one for each). The
calculated field on database2 (calculated1) uses the numerical field
value of database1 (the user can change this value at will). When the
value changes, this should also changes the final value of calculated
field1 of database2. In fact, it does so correctly, but...
There is a field in database2 that takes its value based on
relationship1. This value should of course change based on the value
of calculated1. It does not. It only changes when I go into File >
Define Relationship and open the relationship. I do not need to make
any changes, just open the Edit Relationship dialog box. When I do so
and close it, the value on calculated1 automaticallly changes to the
correct value (the value based on the new user input in field numeric1
of database1).
What am I doing wrong?
Why the value only updates when I open the Edit Relationship dialog?
I am sure the relationship is OK and the left part field is indexed
(otherwise I would get an error dialog while creating the
relationship).
I would appreciate your help on this issue,
Chris
Howard Schlossberg
--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 321-5176
FM Pro Solutions Los Angeles, California
Associate Member, FileMaker Solutions Alliance
John Weinshel
difference, although I hope you will write back with the result. What fields
are used to build the relationship between database2 and database1 (there
must be one if calculated1 is defined as Relationship::numeric1)? Similarly,
what fields are used for relationship1?
I am a bit confused by your description. First you state "in fact, it does
so correctly, but...", and then you later seem to be describing one problem
as calculated1 not changing properly. I thought the problem is that
calculated1 does change properly, but another field in database2, using
relationship1, is not.
Finally, are you certain the fields are not, in fact, changing properly? It
is possible they are changing, but the screen is not being refreshed (common
for related values). Going into Edit relationships (or just about anywhere
else) would force a screen refresh.
--
John Weinshel
Datagrace
Vashon Island, WA
(206) 463-1634
Associate Member, Filemaker Solutions Alliance
"Howard Schlossberg" <how...@fmprosolutions.com> wrote in message
news:3C9CBCF3...@fmprosolutions.com...
Chris McKay
Please excuse me both if my description misguided you somehow. I'll
add some more details now.
1.- Storing or not the results does not make any difference at all.
2.- It is not a screen refresh problem. If it were, just minimizing
and maximizing the database would sort it out (or moving to another
layout within the database, etc.), but it does not.
3.- I have found that changing in calculated_field1 for just field2 (a
field I have created in database2 to try to sort out this problem)
related field to a field within the file makes all the difference and
then the relationship works. You see, my calculated field was using
(relationship2:field2 & "_" " & field3). Indexing or not this fields
would not make any difference either, but all started to work when I
changed relationship2:field2 for field2 in database2.
The thig is that although I can make things work now, it is much more
convenient for the users of the database if I could make it work the
other way. They would just go to a special layout in DATABASE1 and
assign a new value for Filed1 and this should change the calculated
fields in DATABASE2 (it does change it but then RELATIONSHIP1 does not
work).
I am not sure you need any more information about this issue. I wonder
if it is just a "standard" FileMaker behaviour. I still do not
understand why it dows not work when the calculated field uses a field
from another database (I guess which relationship I take and to which
database I related to would not make any difference either, if it is a
problem of FileMaker database design...).
This is more or less what I had:
DATABASE1: 1 record only
Field1 (numeric, indexed)
DATABASE2: Several records
Calculated_field1: ((relationship2:field2 & "_" " & field3)
RELATIONSHIP1 (Calculated_field1 - DATABASE3:Calculated_field2)
This is the relationship that only works if I change Calculated_field1
for field2. The problem is not that relationship2:field2 does not
update the value of the calculated field. It does. It is just that
even if it updates the value, RELATIONSHIP1 does not return the
correct value for those fields in DATABASE2 that take values from
DATABASE3 based in this relationship. They do change to the correct
values though, if I open the Edit Relationship dialog box. This is
what really puzzles me. Now I have tested using field2 (a new field I
have just created) instead of relationship2:field2 and it works fine.
Why? I do not understand. This could be a temporary solution, but it
is not the way I would like to see things working.
RELATIONSHIP2 (Link - DATABASE1 Link)
Note: Link is just a text field with the autoentered value "A",
indexed. I use it for many things and it works fine, so this is not
the issue.
DATABASE3: Several records
Calculated_field2: ((field2 & "_" " & field3)
This calculated field is indexed (to make the relationship1 works).
Do you have any ideas about why it is behaving like this?
Thanks
Chris