HOWTO: a dynamic valuelist

[Lord HellFire]

Hi

I've been struggling with creating a dynamic value list, based on selected options.

The short story is that I've been trying to create a database, that list available translators, based on their language skills and what language the project is for.

I finally managed to create this list, which updates as soon as I change the from/to languages.

For this purpose, I've created two files/databases.

1: Employee database

It contains information about the employees.

The ID field is the short name for the employee.

It also contains 10 x 2 Pulldown-menus for selecting from and to languages. (10 possible language combinations).

An extra field is created; THELIST, which is a calculation, that continously generates a list of these combinations using a calculation:

FROMLANGUAGE1 & " to " & TOLANGUAGE1 & ####here is the next-line tag ####

FROMLANGUAGE2 & " to " & TOLANGUAGE2 & ####here is the next-line tag ####

and so on up to 12. This list looks like this:

--

Danish to English

French to Swedish

--

and so on, based on the from/to fields. I did put more into the calculation to prevent empty fields from showing up in the list, but never mind that here.

This THELIST field is important in the search for the correct translators for projects.

2: Project database.

This database contains all the informations about the projects.

I created a FROM and a TO field, where I select the languages.

Then I created a third field COMBINED, which is a calculation:

FROM & " to " & TO

This calculation creates a line, which must match the content of the list in the employee file.

Under RELATIONS in this database, you create a relation between the PROJECT::COMBINED field and the EMPLOYEE::THELIST

The relation must be a = relation.

Then you create a value list TRANSLATORS.

USE VALUES FROM FIELD

Select the EMPLOYEE database and the ID field.

Set it to INCLUDE ONLY RELATED VALUES starting from the PROJECT database.

Use this valuelist in the PROJECT database.

I've attached a sample database to show this concept. (2 files)

The fields does not have the same names as I mentioned here, as I stripped my existing database down to have only the conceptual fields remaining.

This can also be created in one database using multiple tables.

Hope this helps some of you out there, who are trying as he#¤"#¤ to make something like this work.

-Lord HellFire

project-employee.zip

[sbg2]

Why arent you storing your Employee Languages in a related file as in the attached example?

Emp.zip

[Lord HellFire]

I'm basically just a rookie in designing databases and have only a rudimentary understanding of how databases work. I still don't quite understand all the concepts of the relationship parts.

The database I'm building/improving upon consists of several interworking databases (5-7 or somewhere around there) and each is one file. Keeping them in their own files serves some advantages. Having one big file would create a single point of failure and thats not a good thing.

Your calculation for creating the list of languages is better and simpler than mine.

I still have a lot of things to work on. This self-updating list of translators, based on the two chriterias (from and to language), has been a (insert profanity here) for the last half year.

I couldn't make it work without using a script and a button to update the list.

I finally worked it out after installing the new Filemaker Pro 8. Then tried it in the 7 and it still worked. I felt stupid, because I didn't figure out how it worked half a year ago, especially when the solution was as simple as it was.

I tried looking for solutions online as to how it could be done, but found no real answer that could help me. Not even in this forum.

I may simply have been looking in the wrong places or simply overlooked the posts that contained the right answers.

Anyway... I still have a lot of work to do on the database

Some of the new features in Pro 8 is something I've been missing for quite some time. Proper PDF generation is one, the quicker updating lists and the arrow for showing the lists.

Stuff I'm still missing:

Easier script-editing (notepad style would be nice)

Control over files on the harddrive.

- Our company deals with a lot of different files and it would be nice if the database could, at least, keep an eye on where the files are located. It doesn't have to have a copy of the file in the database (10 gigs of files is a bit much for a database to hold), but the ability to move directories or files would be nice, so once a project is complete, it could move the files to a storage folder, so only active projects are stored in the "active" folder.

Once I get through all the new features in Pro 8, I think I'll update my "list-o-things-filemaker-should-be-able-to-do" and get it sent in for the developers.

Filemaker is the best and easiest database program I've come across. There are still just a lot of nifty features missing.

And I still need to learn how to make good databases :

-Lord HellFire