Core FileMaker Blog

Keep up to date with CoreSolutions

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:

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.

Leave a comment

2 comments

CoreSolutions Logo

London Office:
1-1615 N Routledge Park
London, ON N6H 5L6

Toronto Office:
2425 Matheson Blvd East
7th Floor
Mississauga, ON L4W 5K4

Toll Free: (800) 650-8882
London: (519) 641-7727
Toronto: (416) 410-8649
Email: This email address is being protected from spambots. You need JavaScript enabled to view it.

SUBSCRIBE

Click the 'Subscribe' button to sign up for news & software tips.

Beyond Software

Copyright © 2018 CoreSolutions Software, Inc. All Rights Reserved