Help with tables, relationships for simple multilingual glossary

[LanguageAmbassador]

I need a little help setting up the relationships for a multilingual glossary where a record for a given word might have the same definition across the different languages, but the example for each is unique for each language.

The design is so simple that I provide descriptions here in lieu of an attached file, with explanations of table names, fields, and relationships in parathesis.

Glossary (main glossary table)

Esp (field related to other field of same name of table EspGlos)

Fra (field related to other field of same name of table FraGlos)

Por (field related to other field of same name of table FraGlos)

EspGlos (Spanish glossary table)

EspId (unique identifier field, with values 10001 to 19999)

Esp (field of actual Spanish words)

FraGlos (French glossary table)

FraId (unique identifier field, with values 20001 to 29999)

Fra (field of actual French words)

PorGlos (Portuguese glossary table)

PorId (unique identifier field, with values 30001 to 39999)

Por (field of actual Portuguese words)

Defs (English definitions table)

DefId (unique identifier field, related to EspId, FraId, and PorId)

Def (field of English definitions)

Exs (examples table)

ExId (unique identifier field, related to EspId, FraId, and PorId)

Ex (field of examples of words in sentences)

As for functionality, I simply want a form with table "Glossary" at the top, displayed possibly with a portal. Under that I want a text field for the definitions and a text field for the examples. When you click on any word anywhere in the glossary at the top, which will be scrollable and sortable by each language, the value of the definitions text field and the value of the examples text field will change with respect to the word you click on in the glossary.

Ultimately, this database will have many other tables that mirror the structure, relationships, and functionality of those of tables "Defs" and "Exs." I plan to provide pitfalls, idioms and more, but if I can just get the above-mentioned tables to work, I'm there. Then I can just pattern everything else accordingly.

[comment]

It doesn't seem (to me) lke a good structure to have. To begin with, there's no need for a dedicated table for each language. I believe a better approach would be something like:

Entries:

• EntryID

• Lemma

Languages:

• LanguageID

• Language

Terms:

• TermID

• EntryID

• LanguageID

• Term

Examples:

• ExampleID

• TermID

• Example

[LanguageAmbassador]

Thanks for the reply and sorry I'm just now replying again myself. I've restructured the db per your suggestions, with a little bit of variations in the field names on my part. Here is a link to download the file.

Form "Ui" is the main data entry/viewing form, but it has a problem. At the very top I want a portal for viewing (tabbing through, scrolling, sorting, adding, deleting) all of the terms in Spanish, French, and Portuguese. I can't figure out how to make it work with just one portal, so I created three, one for each language. As you scroll through the records, the values of the definition text box and examples text box work perfectly!

We're getting close!

How can I make one portal that shows all of the Spanish, French, and Potuguese values of field "Term" from table "Glossary" under their respective labels? A filter? How?

Thanks-

Edited February 4, 2010 by Guest

[comment]

How about this way?

LangAmbass2.fp7.zip

[LanguageAmbassador]

Thanks for suggestion. Layout won't work though. This link is to a mockup. Can you figure out how to make it like this?

thanks-

[comment]

What - if any - is the connection between what's shown in the portal and the definition/examples beneath it?

[LanguageAmbassador]

At the risk of sounding tiresome, THANKS yet again for bearing with me. I hope that you have need of a writer someday as much as I do a FileMaker expert.

Anyway, to answer your question, the first file ( link again) is my preliminary whack at this. In that (deficient) prototype the portals at the top function properly so far in relation to the definition and example text boxes below. The goof in the prototype is that I could not figure out how to make a portal that shows all of the Spanish, French, and Portuguese records at the same time, each in their own respective columns.

The . .png file is just a mockup of how I want it to look more ideally.

Sorry if I'm confusing things.

[comment]

It IS a bit confusing: if you show ALL records in the portal, there will be no connection whatsoever to the other fields on the layout (at least not until you select one of the portal rows).

That's not necessarily a flaw - I'm just trying to understand what exactly you're after.

[LanguageAmbassador]

When you select one of the CELLS in the portal, not one of the rows or when you tab from one to another, not when you select a row.

If the tables and relationships could be structured and form "Ui" designed so that it looks like the mockup, then that's all I care about.

The functionality is simple. At the top is a big word list divided into three languages. Click any word in the list, and the definition and example text boxes change their values according to the word you click (in the list at the top).

The layout needs to show all of the words right next to each other, not one above the other as your example did.

Does this help? Thanks for hanging with me still.

[comment]

OK. But there are a few issues you should consider before deciding what's the best way to implement this:

The structure I have suggested suits ANY number of languages. But a layout with three portals side-by-side fits only three. If you ever add a language, you will also have to redesign the layout, and perhaps add a couple of fields and relationships.

OTOH, if you are sure you're always have only these three languages, then the structure is unnecessarily complex for its purpose.

[comment]

BTW, and in the same vein: your demo file shows one example per glossary entry (i.e. a term/language combination). If that's going to be the rule, then you don't need the Examples table.

[LanguageAmbassador]

The portal listing words in parallel at the top will accommodate four languages: Spanish, French, Creole, and Portuguese. Given a word (term) anywhere therein, that same word will have exactly one definition and exactly one example.

For instance, say you click "palabra (la)" under the "Spanish" label in the portal. Accordingly, the (simplified for this example) value of the definition text box will be "1. word; 2. wordX." Likewise, the value of the "Example" text box will be a sentence in Spanish illustrating use of "palabra."

Consider another example. Click "mot (la)" under "French" in the portal. The value of the definition field may say "1. wordN; 2. wordY; 3. wordZ." The value of the "Example" text box will be a sentence in French illustrating use of "mot."

That's it. It seems simple to me with regard to the tables, functionality, and layout. If I get this much working, then and I'm on my way.

BTW, you're my hero...

Edited February 5, 2010 by Guest

[comment]

Here's one possible way to look at it. Note that it requires that every definition has glossary entries in ALL languages - otherwise there might be a shift to the wrong column in the portal.

LangAmbass3.fp7.zip

[LanguageAmbassador]

Looks promising except for one MAJOR deficiency, and that is that every term, has to have a unique definition. As it is now, a given record in Table "Definition" shares one definition for all three terms, Spanish, French, and Portuguese. That won't work. For example, the record for the Spanish "palabra (la)" might have a definition like "1. wordX; 2. wordN." The same record for the French "mot (la)" may have a definition like "1. wordX; 2. workY; 3. wordZ." The Portuguese definition might differ from the other two as well.

That every record has to have a definition or the whole thing breaks bothers me. Every record should have a definition, even if it's provisional, like "tbd" or "null." Can "tbd" or "null" populate field Definition upon creation of a new record and then be replaced any time later?

I want to refine the "Ui" form:

• If text box "gLangId" goes away from underneath the portal, will that break table "Definition?" I know that field "gLangId" is necessary for the backend, but does it have to be visible on the "Ui" layout?
  
• Can "Definition" go away from the portal?
  
  
• Can text box "Term" go away from underneath the portal too? Can the portal itself be made to accommodate entry, deletion, or editing of terms?
  

The mystical alchemy you've used baffles and interests me, and I'm not the only one. Did you notice how many views this thread has? Wow. I hope to understand precisely what you did, and so do hundreds of others!

Edited February 5, 2010 by Guest

[comment]

I am getting confused again.

There is a Definition field in the Definitions table. This definition is - per your original request - in English, and is common for all Glossary records related to the Definition record. It can be empty, and it does not have to be shown to be in the portal.

The field Term in the Glossary table can be empty too - but the way it's built now, every record in the Definitions table must have three related records in the Glossary table. Otherwise the portal won't even out: if there's no record for the French term (even an empty one), then the Portuguese term will slide over to the French column.

It doesn't have to be built that way - but all it requires now is a single repeating calculation field and no additional relationships.

The field gLangID does not have to be shown - I placed it on the layout for clarity only.

Can the portal itself be made to accommodate entry, deletion, or editing of terms?

It could, but then (a) it won't be clickable anymore, and ( you will lose some finding/reporting abilities, because there won't be a Glossary table anymore with a separate record for each term.

On a more general note:

There are two issues here: the data model (tables, fields and relationships) and the UI. I wouldn't let the UI drive the data model.

[LanguageAmbassador]

OK, I guess I'm making what should be simple seem not so. I don't want to aggravate by repeating and above all don't want to seem ungrateful for your help. I am grateful. Maybe if I sort of start over...

Given:

GLOSSARY

I want a four-column glossary list, with the headings labeled "Spanish," "French," "Creole," and "Portuguese.

This list does not have to be a portal. It could be a table.

I want to click any of the four heading labels to sort the whole list alphabetically for any of the languages.

I want to tab among the words from one language to the next and back and scroll up and down.

I want to be able to enter, delete, and edit the words in the glossary. Doing so with a separate series of text boxes/buttons would be OK--say--one for entering, one for deleting, and one for editing.

DEFINITION TEXT BOX

I want one text box whose values change depending on which word, not record, you click in the glossary. Each word in the glossary must have a unique English language definition. Functioning as a combined Spanish-English / French-English / Creole-English / Portuguese-English dictionary, the definition text box will provide common definitions in English, numbered in order of usage frequency.

Although the definition of a given word in one language might be "1. wordX; 2. wordY," the definition of the same word in a neighboring language might be "1. wordZ; 2. wordN; 3. wordA."

EXAMPLES

Functioning much like the definitions, the word, not record, that the user clicks on in the glossary determines the value of the examples, each of which will be unique for each word, not record.

[comment]

The first part could be achieved very simply by a single table with 4 fields: Spanish, French, Creole, and Portuguese. Similarly, there would be 4 more fields for the definitions, and 4 fields for the examples.

I want one text box whose values change depending on which word, not record, you click in the glossary.

One text box whose value changes would have to be calculated - and therefore not editable. But you could use a tab control object to show the correct field (out of the 4 available).

[LanguageAmbassador]

My gosh, I'm grateful to you!

Clearly, I think what this calls for is two main forms, one for viewing only ("UiView")and one for editing (adding, deleting, editing), perhaps named ("UiEdit").

I thought about one big table, as you suggest, for example...

Glossary

• Spanish
• French
• Creole
• Portuguese
• Definition
• Example

A record in this table might be as follows:

SpanishWord1 | FrenchWord1 | CreoleWord1 | PortugueseWord1 | Definition | Example

Thus, every word, regardless of the language shares the same definition and the same example, which would not work. What am I missing?

"SpanishWord1" might have definition "1. WordX; 2.WordY." The example for "SpanishWord1" would use "SpanishWord1" in a sentence, not "FrenchWord1," "CreoleWord1," or "PortugueseWord1."

Ditto for the other languages. Again, I am evidently missing something, not explaining something clearly, or probably a combination, but I still hope the Saints win Sunday anyway.

For the umpteen jillionith time,

thanks!

[comment]

What am I missing?

I said 4 fields EACH, i.e.

Glossary:

• SpanishTerm

• FrenchTerm

• CreoleTerm

• PortugueseTerm

• SpanishDefinition

• PortugueseDefinition

• FrenchDefinition

• CreoleDefinition

• SpanishExample

• PortugueseExample

• FrenchExample

• CreoleExample

[LanguageAmbassador]

Are you tired of my saying thank you yet?! I hope not, because here goes again... THANK YOU.

I tried permutation after permutation of schemas on the way to where I am with this now. Maybe (and hopefully) I missed this variation.

I can't wait to get myself free enough as soon as possible to try it! WOO-WHO!

[LanguageAmbassador]

I'm stuck. File link here.

I'm not just dumping this, but I have to put out a fire right now. I'll be back on this as soon as I can manage right away...

thanks-

Edited February 5, 2010 by Guest