Valuelist based on codetable?

[novusordo]

Hi

What I'd like to do with Filemaker is this: Instead of using several tables for static values, I'd like to use one central table.

1) I have a table which is named "codetable" with the fields "id", "domain", "value", "text", "language"

This table contains different values, which are usually used for valuelists. For example for a salutation field on my address layout the codetable would have values like

"1", "salutation", "1", "Mr.", "1"

"2", "salutation", "2", "Mrs.", "1"

If I had a status field on the same layout, I'd like to use a valuelist which takes the corresponding values from the codetable

"3", "addressstatus", "1", "active", "1"

"4", "addressstatus", "2", "in-active", "1"

The solution I've found so far has the problem, that it changes all the shown values in the field if I change the "source" for one field (i.e. salutation shows "1" after I choose "active" for the status.

My question is now: How can I populate my Valuelist with the corresponding values, based on the field on the layout ?

Does anyone have an idea, how to solve this?

Thanks in advance

novus

[Brent Durland]

Let's say you have a table called "People" and you want to show a value list for salutations. You'll need a special relationship from people to codetable. In the People table, add a text calc field that returns "salutation", and use it to match to the domain field in codetable. That's how you'll display the list. Then, you'll need a separate relationship to display values from codetable based on the id entered. The trick here would be to use the primary key of codetable to populate the People salutation field, and not the "value" field you're using now. So, if the salutation code field has "34" entered, then 34 is the primary key of codetable, and that would match to one, and only one value in that table. Make sense?

[novusordo]

Thank you Brent for your hints.

I'm pretty close to the solution I wanted, but there's one thing which doesn't work.

I've attached a screenshot with the tables (although it's partly german, you should be able to understand it, I think)

The field CD_test is a global textfield, which will be filled with the desired domainname through a script.

In addition to this, I have one valuelist which is based on the codetable and includes only related values starting from the TO Domainfilter (which is based on codetable). Field 1 is CD_ID (primary key), field 2 is CD_Text.

With a script trigger, I do change the domainname to show the corresponding values. As you can see in the second screenshot (Layout), this works fine and it does save the primary key of the codetable in my field of the person table. The only issue now is, that don't know how to display the right text for after I changed the domainname. In this case, after the value 3 for PE_Salutation there should be the content of the cd_text for the cd_id 3.

Somehow I feel that the solution is right in front of my eyes but I don't see it. As usual: 90% of all computer errors sit in front of it ;-)

[comment]

Try it this way, perhaps?

IndyCondVL.fp7.zip

[novusordo]

Thank you.

I think the problem starts if you have a second or more fields on the same layout.

How would you solve it, if you would have additional fields ValueID2 which uses the currency domain and ValueID3 for the materials domain ?

Thank you.

novus

[comment]

Then you would have to also add additional global (or calculation) fields, and additional table occurrences and relationships, and additional value lists.

Which brings up the question what possible advantage can there be in keeping all values in the same table to begin with.

[novusordo]

Thank you all for your valuable inputs.

I realize, that there is no easy way to satisfy what I wanted to do.

Maybe, what I have so far is of some value for others and therefore, I have attached my latest version to this post.

What I've tried in this version is this. The fieldnames contains the domainname, which it should "get" the data from. With a script-trigger, this domainvalue is evaluatedand the codetable valuelist is filled. Works somehow - or not.

Codetable.zip

[comment]

The problem with your method is that the script is triggered AFTER entering the field - too late to affect the value list items shown. You could perhaps solve this by making the field itself a button that sets the global/s and then enters the field - but I still don't see what is being gained by this complication.

Edited September 8, 2009 by Guest

[novusordo]

Thank you for your idea.

That is exactly the problem - I'm too late to trigger the event.

The basic idea behind this was, that I have to maintain only one table with static values instead of several small tables. In addition, I wanted to allow my application to be truely multilingual so not only labels are changed based on the chosen language but also the codevalues.

Maybe this is some "legacy code in my brain" because I usually work with oracle and mssql databases and a loooot of sql ;-)

Thanks again

novus