Understanding why calc fields can't be used in value lists

[oilcan]

Hello all,

I have recently implemented a new database solution for our lab, but there are some things that don't work quite the way I want them to.

One of them is this. There are a number of tables that want to pull calculated reference names from other tables in a value list. If I set the fields to auto enter text calcs, it will allow me to pull them up in the value lists, however then they don't seem to update if any change is made to the originating related records. This is important as we are still in the process of standardizing naming conventions for things. If I then turn them into regular calcs, I am unable to index them, and thus unable to use them for value lists.

So two questions...first, is there any way to work around this pesky problem so that there would be a cascade of updates to these names down the relationship graph if a change is made in the grandparent?

and two...why doesn't it just flipping work in the first place? If you are looking at two fields in a value list, and one of them, the primary key, is indexed, why should it matter that the other field is also indexable as well? why not just use the index from the primary key to display the calc value in that record? perhaps there is a reason for this, but with my current understanding of things this seems more like a limitation of filemaker than with the confines of a relational database system.

thanks for any enlightenment you may be able to give me on this topic.

[Vaughan]

..."this seems more like a limitation of filemaker than with the confines of a relational database system."

It definately can't be done in FMP -- not sure about other systems.

Either way, there is usually some way around the problem. In fact IME hitting this limitation often indicates that something might be sub-optimal in the data structure.

[Søren Dyhr]

It definately can't be done in FMP

Thats correct - and the way to accomplish something in that direction, is to let the user make selections in a dwindled portal instead, perhaps even with a recursive relational structure underneath!

http://www.fmforums.com/forum/showtopic.php?tid/187866/post/255867/hl//fromsearch/1/#255867

...and the recursivity:

http://jonathanstark.com/recursive_data_structures.php

--sd

[Anuviel]

I was reading this post and I am probably totally miss-understanding what is it about so my answer is probably all wrong so I apologize in advance but here it goes.

I basically got a calculation indexed (that it what shows when I look at the options - it says indexed)

I can use that calculation in my value list and when I update information in the fields it calculates from the info changes in the value list

The calculation and the fields are in one table, value list is in second table and it shows calculation result as first option and a field information from the different table as second. Hope this makes sense, here is the file.

That is probably what this question was not about as I am still not clear on it.

Example.zip

[comment]

I am afraid you are missing the point here: it's not about calculations vs. 'regular' fields; it's about stored (therefore indexable) vs. unstored.

[Anuviel]

Thanks., I thought I was on the wrong track, it was probably the title that messed me up saying why you cannot use calc field in value list as I've used one.

Never mind, my apologies...