Value lists from fields in different tables

[polarpro]

Hey,

I wonder if there is a solution to this problem:

I have a table1 that contains a field called Keywords1, and a table2, that contains a field called Keywords2. (In the Keywords fields the user can enter any word that he thinks is important to help him categorizing the record.) Now, ideally, when entering words the user can choose from a value list that uses values or words of both fields.

Well, I tried value lists, calc fields, text fields / auto enter, and so on, but I couldn’t find a solution. Even though the tables are related (table1:table2 is 1:n) I am not successful.

Any ideas?

Thanks a lot,

Mike

[comment]

It cannot work the way you describe, because a value list must be based on a field that can be indexed (actually, a value list IS the index of that field). A calculation referencing a related field cannot be indexed.

Perhaps if you describe the actual situation rather than an abstract one, another solution may be found.

[polarpro]

Hey comment,

Thank you for your (again super fast) answer.

Well, the situation is pretty much the one as described. The database I am currently working on is a kind of knowledge pool. In one table users can create “topic” records, and to each topic they can attach several “detailed sources” (e.g. they can store links to websites or to instruction files on the server). The users can enter details about their items in description fields, and most of the users like to attach their own additional keywords to their electronic material; many of them use their own classification system or lingo. To make keeping their entries consistent easier I’d like to attach the described value list. The aim is to have one value list for both topics and

detailed sources, to reduce having something like "B.C." in a topics record, and "British Columbia" in a detailed sources record.

In another database I created a separate table for such a list of controlled terms. This was a good thing to do, as in this case the terms were indeed controlled by one person, and users could not add their own values to the list. But with the current file there is no need of a “higher instance” controlling the keywords, and it would be a good thing to give the users easy access to what they earlier entered via such a value list.

[comment]

Well, since you can only use one of the fields as the source for the value list, I believe it would be best to keep the "master" values table up-to-date with the custom entries. This could be done periodically (e.g. at opening/closing the file, import any added values to the master table), or in real time by scripting the selection process (i.e. users select from a lsit/portal in a new window, rather than from a drop-down list or whatever you're using now).

[polarpro]

Thanks a lot :

I like the idea of having a “master values” table, as I realized this is a good thing to make a set of (controlled) terms / values available to other tables. For example, the in Post#303706 mentioned list of controlled terms is about country names; and having this list as a separate table makes it easy to attach it to other tables where we need a consistency in country names.

For the time being, I would like to keep the extra effort a user has to go through when adding and deleting “personal keywords” to his value list as low as possible. So, I prefer to have some background things going on rather than sending him to a list in a new window. (All the topics and detailed sources are already open in their own windows.) I already have a Close Window button on my topics and detailed sources windows, this can easily be changed into a Submit Changes and Close Window button.

When a new keyword is added, things seem to be easy: On closing the window I tell the script to check if any of the keyword values are already in the “master values” table. If a value is not, a new record will be added.

However, I wonder what the best way is to check if a keyword has been deleted. I want to avoid having an endless list with keywords that are not in use anymore. Any suggestions?

Thanks.

[comment]

I am a bit confused by your last request. If you don't want unused values to be on the list, why not base the list on values from the actual field, and let go of the "master" list? Or do you mean that the same value list is being used to populate more than one field?

[polarpro]

Sorry for having been confusing. Maybe the term “master value” table was ambiguous.

(In the example of the country names value list I was talking of “officially” controlled terms: One person takes care of the values and nobody can add or change or delete these values. Here it makes sense to store unused values, as in the course of time somebody might need one of these values, and then it is necessary to have the correct one at hand.)

In the current case (my actual question) I want to use the same value list to populate more than one field; and the terms are not “official” ones, every user creates his own value list. So, as the fields that need to be populated are on different tables, I created a new table (“keywords”) that takes care of these values. Every new value that is being entered in table1::Keyword01 gets its own record in the table keywords, and from there it is available in table2::Keyword05, for example.

I just tried it and am very content with it.

The only thing that is missing is a smart way to get rid of values in the keywords table when a user deletes a keyword, so that it does not appear in any of his records anymore. It still would be in the keywords table, and from there it would appear in the value list based on this table.

[comment]

To check for unused keywords, you can use the FilterValues() function. Something like:

FilterValues ( ValueListItems ( Get (FileName) ; "MyKeywords" ) ; List ( table1::Keyword01 ) & ¶ & List ( table2::Keyword05 ) )

would give you a unique list of used values only.

How is an object marked with multiple keywords? From your "table1::Keyword01 ... table2::Keyword05" it would seem there are multiple fields for this purpose - that doesn't seem like a good arrangement.

[polarpro]

Thanks for that!

You got me on this here:

Currently, in Edit Mode there are 10 separate fields for the user to enter his own keywords. Having one field only where the user enters all his keywords (maybe separated by slashes, just like the keywords are shown in View Mode) seems to be an easier approach; on top, the number of personal keywords would not be limited anymore, which is a major advantage...

Am grateful for a better approach.

[comment]

Separating by a carriage-return might be better, because Filemaker understands this as a 'list of values'. For example, using such field in a relationship can quickly find records that CONTAIN a keyword. You could still display the list in any form you like, by using a calculation field that substitutes ¶ with another character/string.

Using a proper join table might be even better - all depends on how you plan to eventually use the keywords. Some forms of reporting are possible only with a join table.

[polarpro]

Hey comment,

To check for unused keywords, you can use the FilterValues() function. Something like:

FilterValues ( ValueListItems ( Get (FileName) ; "MyKeywords" ) ; List ( table1::Keyword01 ) & ¶ & List ( table2::Keyword05 ) )

I wasn’t very successful with this, as the List function only listed the values in one record. But I use a simple search script now that runs when the database is being closed. Works fine.

Separating by a carriage-return might be better, because Filemaker understands this as a 'list of values'. For example, using such field in a relationship can quickly find records that CONTAIN a keyword. You could still display the list in any form you like, by using a calculation field that substitutes ¶ with another character/string.

Thank you. I use the slash now for both data entry and display, but I translate it into a ¶ as soon as I do a calculation. For the time being, a simple additional table for the keyword values works very well; after all, it won’t be a big system of mutual controlled terms (I think...)

Thank you very much for your help!!!

Mike

Edited September 22, 2008 by Guest