Using unindexed calculation field in value list

[Matt Klein]

Hi All -

I recently did some fooling around with value lists based on some responses to a different topic that I posted here. As a result of the fooling around, I stumbled across something that I thought was interesting so I thought I would post it here. I don't know if maybe it's already been discussed, so forgive me if I am reiterating a past topic.

Here goes...

So, the general rule of thumb is that you can use calculation fields in value lists only if they are indexed.

I found a that you can actually use an unindexed calculation field in a value list if you also show a second field and you sort by that second field. The important thing is that the second field must be indexed.

I stumbled across this after reading a post here that suggested using a second field in a value list to create a custom sort order. The second field simply has space characters the quantity of which corresponds to the position in the sort order you wish for the item to be in.

So, if you create a value list that shows the values from the first and second fields, the first being an unindexed calculation field and the second one being the indexed field with the spaces, and you sort the list by the second field, the resulting value list will show the items from the unindexed calculation field. Since the spaces aren't really visible characters, the user won't even know they are there.

The really odd thing though is that if you setup your value list as outlined above and click OK, FileMaker will actually throw up an error stating the value list won't work because the first field is not indexable. If you just ignore that message and go on your merry way, it seems to actually work.

The only thing you have to ensure is that each record has a different number of spaces in the field you use as the second field.

I'm interested to hear what you guys think. Is there any reason to not accept the fact that it works even though FileMaker throws an error when creating the list?

One caveat is that, according to "comment", this is limited to approximately 100 values.

Actually, "comment" suggested the following calculation in lieu of entering spaces by hand I presume:

Substitute ( 10^SortOrder - 1 ; "9" ; " " )

comment, what is the benefit of using that calc? I want to use it just because it looks cool, but am unsure of the true benefit.

[comment]

You may have noticed that duplicate values are removed from the field that is designated as the sort field. So it would seem that indexing does take place, only the field roles are switched around. Of course, this is pure speculation.

what is the benefit of using that calc?

Do you mean as opposed to typing 100 spaces while counting aloud?

[Matt Klein]

LOL!

That's what I figured the benefit was. Thanks!

Also, you are correct. In the example I gave, the second field was the sort field and I stated that the second field must be indexed. So, there is definitely indexing going on, just not in the calculation field used as the first field in the value list.

So, you can use a calculation field that can't be indexed because it contains a global or related field as the first field in the list.