I am writing a book about privacy and identity, as part of the research phase I am finding that I need several databases. I done OK, so far, but my attempts at creating relational database today has left me stumped. I am ashamed to admit that I have worked in Filemaker for sometime but it is not my regular gig and after returning, post long hiatus, I find I am not so familiar with my old friend.

 

I have to search online for for various terms related to the subject, often in other languages. I have to keep pairs of search terms: English and other language. Paper lists are not cutting it anymore.

 

Its quite possible my concept is flawed, if so, I am more than willing to change tack and redesign. Just started so no major marriage to first try.

 

I created a table named "English" with one field, "English Term" and another table named "German" with one field, "German Term." I then linked the two tables together via those fields.

 

I created a list layout named "English Terms" with a field for the various English terms and a field beside it for German equivalent from that related table. I also created a value list named "English Match" that gets its values from the "English Term" field in table, "English." I tested this list, it works.

 

In a layout named "German" I have a field for the related English terms and it is setup with a popup menu to show those terms from the value list "English Match" and that field is supposed to show those values from "English" table. To the right of that field is the field for German term.

 

The Problem:

 

What is supposed to happen is I click on the "English Terms" field, while the German table, and get a choice of English search terms, pick one and then enter the German equivalent in other field. The goal would be that while English list I could see each matching German term. Once this works I would copy procedure for many other languages. 

 

It is not working, while in the German table the English terms field remains unresponsive, no popup menu list appears, so I am unable to choose.

 

Fairly sure that this is a relationship problem of some kind. I am working Filemaker Pro Advanced version 8.0v1. I am on a Mac running Snow Leopard.

 

Thanks for any guidance.

 

Attempted to upload file but received the following error message:

 

 

"Search_Term_Translation.fp7

You aren't permitted to upload this kind of file?"

You need to zip your file in order to upload it.

Thanks Eos, done.

Search_Term_Translation.fp7.zip

I created a table named "English" with one field, "English Term" and another table named "German" with one field, "German Term." I then linked the two tables together via those fields.

 

That makes no sense, because the two values must be the same in order for the two records to be related.

 

 

Will there be always only one German term corresponding to each English term? Or is it possible to have alternatives?

That makes no sense, because the two values must be the same in order for the two records to be related.

 

 

Will there be always only one German term corresponding to each English term? Or is it possible to have alternatives?

 

 

"That makes no sense, because the two values must be the same in order for the two records to be related."

 

Your question made me realize that I am in a bit of Catch-22. Let me explain more clearly:

 

I will enter a number of search terms (record for each) related to privacy and identity in the English table. These will act as an anchor. I should also point out that I may not know the exact term in advance for another language, such as German, for English term.

 

When I am in the German table (layout) I will create a new record, for a known German term, and then choose from the popup menu the matching English term. Until that point, there is no relationship but I do need to see the list in advance to make that happen. Thus, the Catch-22. I thought you could show a Value list in another table without requiring a relationship?

 

"Will there be always only one German term corresponding to each English term? Or is it possible to have alternatives?"

 

Yes, there will only be one German term and one English term matching. Some English terms may not have a German equivalent so they will not have a matching German record. No alternatives, but as I mentioned earlier, there will be other tables for other languages hooked to the English table. I will have buttons to switch between different layouts views to show them.

Yes, there will only be one German term and one English term matching.

 

Then why not make this dead simple and use a single table with fields for English, German, Spanish etc.? Although it's not strictly "correct" relationally, given that you are the sole user (IIUC) and also the developer, and that this is a temporary tool to be put aside when your book is finished (again, IIUC), it should serve you very well.

 

The other option is to have two tables - Terms and Translations - where Translations would have an individual record for all the translated terms, regardless of their language (and thus fields for TermID, Language and the TranslatedTerm itself).

 

 

When I am in the German table (layout) I will create a new record, for a known German term, and then choose from the popup menu the matching English term. Until that point, there is no relationship but I do need to see the list in advance to make that happen. Thus, the Catch-22. I thought you could show a Value list in another table without requiring a relationship?

 

There is no Catch-22 here: you can use a value list without a relationship. However, you must have a foreign key field in the Translations table to enter the selected value into. In addition, you don't want to use the English Term directly as the matching value, because if you correct it even slightly, you will break the links to all corresponding translated terms.

 

Another question is how many terms will you have altogether - since selecting from a value list can become inconvenient when the list becomes very large.

FWIW, I have fixed your file to work the way you have described. But I do believe you should use one table for all foreign languages (if not one big flat table as explained earlier).

Search_Term_Translation2.fp7.zip

Comment, thank you. Not only does that work, suing your example, I have expended it to other languages.

 

"But I do believe you should use one table for all foreign languages (if not one big flat table as explained earlier)."

 

I did not go deeply into the user experience issues when I described my problem earlier. The layout I am using, first version rather primitive compared to where it is going, needs to focus on one language at a time. There are two reasons for this: one is the lack of screen real estate, I need avery tight, columnar list showing just one language and its English counterpart. The other reason is that I will be searching the web and other documents, one language at a time. Showing other languages would be distracting and get in the way.

 

Really want to thank you again. I knew, in the back of mind, to take the approach you shared with me but like I said earlier I do not work in Filemaker often enough to keep the chops up to par.

 

There was a day, but no longer. Its too bad because whenever I return to Filemaker my needs seem exceed my current ability. I do not care for a lot of Filemaker's interface design but it is one hell of a tool.

You can have one table as Comment suggested but not have all the fields on one layout. He's speaking of data structure rather than UI or presentation. You can have as many layouts as you wish based on a given table. Each can show different data sets.

My two cents.

Edit: I meant to say it wasn't necessary to have all fields and fields from related tables on the same layout.

Well a bit morte valuable than 2 cents, as I get it now.

 

Comment and you meant like a spread sheet. Columns are a language and rows are the individual search terms, English and the rest following in that row.

 

Thanks.