Multi-criteria relationship. Calc field involved.

[Enigma20xx]

Using a multi-criteria relationship I want to accomplish a conditional value list.

 

pk_ID (not equal) fk_ID

cTotalAmount (greater) gZero

 

but as cTotalAmount is a calculation field it doesn’t work properly.

 

I’ve tried with a calculated numeric field (=cTotalAmount), but this doesn’t updates automatically as a calculated field.

 

Any suggestion?

[Kris M]

The important thing to investigate is what type of calculation is it? Unstored calcs used as a predicate in a relationship are problematic because they are not indexed.

 

http://forums.filemaker.com/posts/2061e27604

[MikeKD]

How often does the calculation change?

Could you run a scheduled script to populate the field every hour / whenever?

[comment]

pk_ID (not equal) fk_ID

cTotalAmount (greater) gZero

 

Your explanation is not clear, esp. the context. Please name the tables involved and explain [a] which table is supposed to be the source for the value list, and how exactly are the matchfields defined - for example, is gZero a global field?

 

 

 

How often does the calculation change?

Could you run a scheduled script to populate the field every hour / whenever?

 

That's not a good idea: you want your data to be correct at all times - not just on the hour or "whenever".

[MikeKD]

 

 

That's not a good idea: you want your data to be correct at all times - not just on the hour or "whenever".

That makes sense.

Would it still be a bad idea if the script was triggered whenever the relative record or field was updated?

 

It will be interesting to see how this relationship is going to be used!

[comment]

Would it still be a bad idea if the script was triggered whenever the relative record or field was updated?

 

It depends on whom you ask. I would rather not depend on a script trigger (i.e. a layout-level device), if the data can be made to self-organize using calculations and relationships (i.e. data-level devices). But Wim's answer may be different ...

 

And, after all, a conditional value list is a layout device itself - so this may work even if the field is updated by the action of the user wishing to use the value list, just before doing so. However, you must also consider the possibility of record locking by other users.

[Enigma20xx]

As my ability to explain myself (only in English ) is quite bad, using the some of the Estimates starter solution structure, I’ve made a sample file to try to throw some light on it.

 

The example goes about people and their bets.

 

My goal is to make a value list that shows only Bettors with balance enough to bet, then, only the ones that are not already betting.

 

But writing this down I realized that there’s another problem.

 

At first, Bettors can have a positive balance (so the VL will show them), but the amount they have to face, depends on the number of bettors, so that positive balance could not be enough to cover the bet , and this could become a vicious circle .

 

Thanks for all your answers.

BetsDB.zip

[Enigma20xx]

Any idea?