Creating value Lists with Unstored Calculations

If you’ve ever tried to create a value list based on an unstored calculation, you’ve probably come across this error:

Normally this means using workarounds to create the value lists by creating lookups, or auto-enter calculations, etc. However, there are some times when this just isn’t feasible; especially when you want to make those lists truly dynamic.

It turns out there is a way to create value lists using an unstored calc; it involves a bit more overhead in that it requires an extra relationship and table occurrence to work, but it seems to solve the problem nicely.

As an example, I’ve set up a simple table called valueList that consists of three fields: a number field called id, an unstored calculation called color, and a calculation called constant (which is simply a calculation field set to 1).

Now I try to create a value list based on the fields id and color, sorted by the color field.

The system gives me an error, and when I try to use the value list in a popup, nothing shows up:

In order to get around this, we’ll need to do the following:

• Create a new table occurrence of the valueList table (I’ll just call it valueList2 for now)
• Create a relationship between valueList and valueList2 using the constant field. I set up the relationship using the ‘X’ operator so that relationship will match all records

• Modify the value list that was previously created so that it only shows related records starting from valueList2. You will still get the error saying that the value list won’t work; just click OK for now.

Now try using the value list in a pop up – this time, it works, even though we are using the same unstored calculation as a part of the value list.

It’s a bit of extra work to set up, and adds a bit of overhead to you relationship graph, but it’s a pretty useful trick.