Can't Index Calculated Field based on Summary

[jimb51]

I have 3 files; A,B,C
I created a calculated field in B based on a Sum of fields in A. I use this as a flag which I concatenate with another field in B to create a relationship between C and B. Currently this concatenated field (ID) is a Number with a calculated auto-entry. When I create a new record, this setup works great and the correct data shows in the ID field. However, when the Sum value changes, the flag value changes BUT the ID value does not until I delete the data in the ID field then reenter one of the keys for the particular record (NAME in this case.) It appears this is the normal occurrence in FMP. Why not just make the ID field a calculated field you say? Because FMP will not let me index on a field that derives it's data from a Summary field, my flag field from B. AND my current layout uses a relationship between C and B which needs that ID field in B to be indexed.

Is there a way around this problem?

 

[Schlogg]

Do you want the concatenated field to remain constant or change as the sum changes? I'm having a bit of trouble working out your fields but am guessing at -
SSSum = Sum(A::fieldx)
ID = auto-enter a number (a serial?)
Concat = ID & SSSum
If you make SSSum an auto-enter field, FMP will let you use it as (part of) a key BUT it will remain constant. You can get it to change by deleting the value and doing a relookup on the key to file A.
(Note make it an auto-enter calculation, not an auto-enter lookup.)


Cheers,
Paul J.

 

[lebisol]

perhaps OT:
u can always
have a "temp" field that will do a calcualtions etc. and have your "real field" in plain text,number....it would require scripting...but in essence:

SetField [ID,c_Concat]

as your "final step" after u have done all the calcualtions....so "Concat" can be indexed
All the best!

:--------------------------------------------------------------------------:

ok,ok...I did shoot the deputy but he told me he was the sheriff!
:--------------------------------------------------------------------------: