1 of 1 people found this helpful
Your Active calc has to be Unstored to remain accurate as a calc field, making it a poor basis of generating a value list based on a field index.
You might consider a scheduled script to update your Active script as a stored value daily or weekly, but have it also set a NameIfActive field to either the name or null, then use that new field as the Value List source field.
Active calc is an unstored calc. Sorry, should've clarified that.
First, as Stephen mentioned, this must be based on stored values.
I do my filtered value lists using a 'globals' or 'preferences' table. In this table, add a global "isActive" field. You can set the value to "1" for true, if you want. I usually filter value lists on a string, but a number field works fine.
Back on your Client layout, add an onRecordCommit() script trigger to the layout, and use it to fire a setField() on the client status, setting to "1" if applicable. If you have access on that layout to the most recent invoice date (maybe from a portal?), you can use that date as the condition. You could also fire this with an onRecordLoad trigger, and loop through existing records to bring them up to date.
Finally, in the configure value list dialog, click 'use values from field'. Click 'specify field', pick your globals table, and enable the 'include only related values'.
Hope that helps!
My suggestion was to change it from a calc to a stored number and update both Active field and NameIfActive via a looping script on a schedule.
Calculation fields can be stored and still update correctly as long as the fields that they reference are in the same record.
There's a simple calculation field that you can add to the Clients table:
If ( Active = 1 ; ClientName ) // be sure to select a text result type.
This field is indexed but empty if the client is not active. Use your field names in place of mine.
Now set up your use values from field value list to include this field as either the first or second field. If using it as the second field, sort your values on this field to drop out the values where this calculation field is empty.
This value list requires fewer fields than a relationship based approach, does not need what is often an additional relationship and can be used from the context of any layout in your file.
This is one of two "hardwired" conditional value list examples that can be found in:
That leaves the issue of how Active gets updated since it currently an unstored calc, so it won't trigger a stored result.
However to do this, you need an indexed field in clients unless you use a different selection tool such as a filtered selection portal.
Hadn't really thought about doing filtered value lists that way (in the same table). That's a neat idea.
I'm used to having a globals table already, for other stuff, so I've grown accustomed to putting status-y values there and updating local fields onRecordLoad or onRecordCommit. Mostly for speeding up finds and filtering portals over a WAN.
Note that a selection portal with a filter, something demo'd in the second Adventure file, can work with unindexed values as the filter can filter for such a value.
Thanks all, I’ll be putting your suggestions to work when I get some time back at the desk. I could change Active to stored as it references fields in the same record.
Out of curiosity, and forgive my ignorance, why is an unstored calc a bad choice for generating a value list?
1 of 1 people found this helpful
A "use values from field value list" lists values from the field's index. An unstored calculation field has no index.
If a calculation field only references fields in the same record as the calculation, there's no reason that I can see not to make it stored.
Ah! I think that there is the aforementioned light bulb moment. Again, thanks all for your help.