Multiple Dynamic Value List

[terrencebrown]

Thank you for reading this!

Think of Books>Chapters>Verses in The Bible.

I have a static popup menu of the Books and a dynamic popup menu of the Chapter numbers based on the chosen book.

Easy so far.

The hard part is then a third popup menu (dynamic) of Verse numbers based on the two choices of Book AND Chapter.

A calculation field result displays text based on these three choices.

Is this possible yet?

[ March 09, 2002, 08:02 PM: Message edited by: terrencebrown ]

[RussBaker]

Terrence,

Make a dymanic menu field for the verse using a relationship on a new calculated text field which = Books&Chapter, then your setups would be:

1. Select the Book from the Book field

2. Select the Chapter from the dynamic value list in the Chapter field (only chapters from that book will show).

3. Select the Verse from the dynamic value list in the Verse field (only verse numbers from the chosen Book and Chapter will show).

This process can continue on for more than 3 fields. Just keep adding new relationships based onthe sum(&) of the preceeding fields.

You might also think about having a portal for the verse instead of a dynamic field. This would be based on the Book&Chapter reltionship. That way, once you had selected the Book and Chapter, then the verses from the chapter would show up in the portal in numerical order - with perhaps the first couple of lines of the verse - then a scripted button in the portal row could take you to the full verse.

nice picture !!

Russ Baker

Canberra, Australia

The Land of TWOGM

[terrencebrown]

That is great and very clever. I truly appreciate it!

Not only did it do the trick but your suggestion about the portal has really helped me create an awesome browser. Thank you so much.

BTW Nothing like a scope to bring your goals in to focus.

[billypilgrim]

Russ, Terrance and the rest,

I have similar dilemma and came up with the multiple relationships as a possible solution, however, my results are not quite as satisfying. Using Terrance's examples, I choose a Book and I can get the related Chapters to appear, however when I go for the Verses, I get all the Verses in all the Chapters of all the Books, not just the ones in my selected Chapter. I suspect the Value list is defined at the time the record is created (the Book is not really chosen, but rather assigned through a script that also creates the record), rather than looked up when the Verses field is accessed. Close, but not close enough given the inattentiveness of my users. Is there someway to relookup the value list, if this is the case? Or perhaps I am missing something in my defined relationships? Any insight would be appreciated.

Dan

[billypilgrim]

Nevermind, I found the problem. I was defining the relationship on a calculation that was a number rather than text. Duh. Thanks anyway.

[ryano]

Ok i need help please, I am only learning basic fm here, i have 3 dynamic value lists going, although I want the 3rd list to show the data from the related lists of the first two only... can anyone help please, in detail, cos my brain is mush at the moment

[CobaltSky]

The approach you're looking for is as outlined in RussBaker's responds to this thread.

You have three fields. Let's call them Choice1, Choice2 and Choice3. I assume that you want the conditiional value list to pick up related values from another file. To get a conditional value list for Choice3 based on the choices made in Choice1 and Choice2, you've got to follow four steps, as follows:

1. Create an unstored calculation field called "Choice1and2_[key]" of result type Text in your main file with the formula Choice1 & Choice2.

2. Create a Stored calculation field called "Value3_[key]" of result type Text in your related file with the formula FieldForChoice1 & FieldForChoice2 (where the two fields used are the fields from which the values for the value lists you're using for Choice1 and Choice2 are taken).

3. Create an additional relationship called "Choice3Value" between your main file and related file which links the "Choice1and2_[key]" field with the "Value3_[key]" field.

4. Create a value list for your Choice3 field which uses the "Only related values" option, specifying the ""Choice3Value" relationship.

That's it. Your value list for Choice3 will now display only those values which relate to both Choice1 and Choice2.

The additional calculating fields need not be displayed anywhere - and of course you will choose more appropriate names for the fields and relationship than the examples I've used.

[ryano]

My life is now worth living.... thanks heaps...

ok now i have some serious databasing to do!

Thanks again. Ryano

Not sure why it works, but it does.