I need to create a value list containing information from more than 2
fields. So I created a calculation field to combine data from multiple
fields, hoping to use that field in the value list. But FM won't let
me, saying that "This value list will not work because the field
[field_name]cannot be indexed. Proceed anyway?".

If I choose to proceed, it seems to work, to a point, i.e. as long as I
don't try to sort the list on the calculation field. And that's what I
really need to do, i.e. to get an alphabetic listing. So I'm rather
stuck!

If anyone could give me some pointers as to how I *should* be doing
this, I'd be ever so grateful. (I'm new to this!)

I'm using FM8, PC.

Thanks,

Carla.

PS - More detail about the database etc.
3 tables - film (e.g. Titanic), screening (e.g. Titanic, 1:00,
10/10/05), booking (e.g. Ann, Titanic, 1:00,10/10/05). On the booking
layout, I need a value list showing all available screenings, i.e. s_id
(the key of the screening table), film_title, screening_date. So far,
I've tried concatenating film_title and screening_date - and have hit
the problem above.

You could take a different approach to value lists and use scripting to
select the records. See my example of browsing a list of records and
displaying the fields of the active record in the footer section:
http://idisk.mac.com/mpaineau-Public/browse_example.fp7

You would need to add to the footer section a button that starts script
to process the selection.

Michael Paine

Carla wrote:

I assume your fields are in the same table. It is the calculation field
that is causing the problem. Try replacing it with an auto-enter
calculation: define finder as a text field with
auto-enter
film_title & "¶" & screening_date

With the existing records you will need to do a Replace into finder
using the calculation. In this form the field is indexable and the
value list will pull out the film_title and screening_date as separate
values. If you want them to be tied together then replace the "¶" by "
" or "," (any separator other than ¶)

Dan,

Thanks very much for this reply, which has really helped me. The value
list now works, although it doesn't update automatically, e.g. when I
change a film_title. (I have unchecked "Do not replace the existing
value for field" option.) Does this mean I'll have to run the script
to "replace field contents" each time I change a film_title? BTW, the
fields which make up the value list are *not* in the same table - could
this be why it's not refreshing automatically?

Thanks again for your help, which has move me in the right direction, I
think!

Carla.

Thanks for this Michael - interesting approach. I'm going to persevere
with the VL this time, but your suggestion wil be useful in another
context.

Carla Sloan.

Carla

I didn't take account of the possibility of changing film_title once it
was entered. To account for that you need to do a check for active
field. I haven't tried this with fields concatenated from different
tables so I don't know if it will work. Anyway give it a shot.

Auto-enter, replace existing value
Case(
Get(ActiveFieldName) = "film_title"; film_title & "¶" &
screening_date;
Get(ActiveFieldName) = "screening_date";film_title & "¶" &
screening_date;
film_title & "¶" & screening_date
)

Although the calculation is the same under each case the first two will
be activated (I Hope) by changing the appropriate field and I have
separated them for clarity. I have used this technique with fields in
the same table and it works very well.