Creating value Lists with Unstored Calculations

Creating value Lists with Unstored Calculations

Keep up to date with CoreSolutions

Creating value Lists with Unstored Calculations

Creating Value Lists with Unstored Calculations Main Title Image

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

unstored_value_list_1

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).

unstored_value_list_2.jpg

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

unstored_value_list_3

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

unstored_value_list_4

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

unstored_value_list_5

  • 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.

unstored_value_list_7

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.

unstored_value_list_8

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.

Comments

Leave a Comment