Cannot figure out relationship logic

[John Canino_71441]

Using: FMP Adv 11 on a Mac with OS X Lion.

I normally consider myself pretty good with FMP, but cannot think through the relationship schema for a current project, which is for a language/dictionary database (for a constructed/artificial language. Nerdy, I know). Although the content of my database may seem strange to some, I think the techniques I am trying to learn could be of value to others. So here goes...

My example here uses three tables. I have more, but learning how deal with the third will solve my problems for the remaining ones.

TABLE 1 (my starting point) is called ENGLISH, containing SERIAL # (number), ENGLISH WORD (text) and POS (part of speech, text).

TABLE 2 is called TARGET, containing SERIAL # (number), ENGLISH WORD (text), POS (text), ROOT (text) and TARGET WORD (text).

TABLE 3 will actually become multiple tables, one for each PRE-DEFINED POS, containing SERIAL # (number), ENGLISH WORD (text), TARGET WORD (text), POS (text), ROOT (text) and various fields calculated as text based on the POS.

For example, if POS = NM (noun masculine), then all words linked via tables 1 and 2 and having the POS = NM should populate this table. For each different POS, the correct table should be populated. Within each of these tables I will be able to automate word formation by concatenating the ROOT with appropriate affixes, which I know how to do already.

I have tried the following relationships, but something seems to be missing:

ENGLISH TARGET MASC NOUN

=========== =========== ===========

SERIAL# SERIAL# SERIAL#

ENGLISH -------> ENGLISH -------> ENGLISH

POS -------> POS -------> POS (I assume I need to auto-enter matching/pre-defined POS here)

TARGET -------> TARGET

ROOT -------> ROOT

INDEFINITE FORM (text calc: root plus ending)

DEFINITE FORM (text calc: root plus ending)

PLURAL FORM (text calc: root plus ending)

PROBLEM: With this set up, even after defining fields on the layout, in browse mode the field display says "unrelated table". I am not able to enter any data. I do not know how to trouble shoot this. I suspect it may be quite simple ...

My intended workflow is as follows:

1) Manually enter or import words into table ENGLISH

2) Enter or correct field POS (in ENGLISH)

3) in table TARGET, enter TARGET WORD and ROOT

With just these three steps, my hope is that the table MASC NOUN will take care of itself.

Thank you for your help and consideration. I appreciate it very much.

-John

[comment]

Well, you got me confused, at least. I don't see the logic in the way you have planned your tables. The second table seems to be an exact duplicate of the first one - with some additional fields. Not sure where you are going with this. Will there be a one-to-one match between an ENGLISH WORD and a TARGET WORD? If yes, then why wouldn't one table be sufficient?

You might also want to take a look at:

http://fmforums.com/forum/topic/70345-help-with-tables-relationships-for-simple-multilingual-glossary/page__p__333263#entry333263

[Christine Sephton]

In my best opinion you do not need to duplicate the fields. I would lay it out like:

TABLE - ENGLISH, (Fields: SERIAL[Number], ENGLISH WORD [Text])

TABLE - TARGET, (Fields: SERIAL[Number], ROOT [Text] and TARGET WORD [text])

TABLE - POS, (Fields: SERIAL[Number], POS [Text]).

Then Simply LInk them all By SERIAL[Number]

Unless I am missing something?