Portal of "Glossary" field with two columns, one "Spanish" and one "French"

[LanguageAmbassador]

I want to use a portal to display I guess what you'd call two "instances" of one field. Table "Glossary" has only two fields: "GlosId" and "Word." See following:

 

Table: Glossary

• Field: GlosId (number, where records 0-199 are for Spanish words, and records 200-299 are for French words)
  
• Field: Word (text, Spanish or French words, depending on the value of field "GlosId")

In the data entry/viewing GUI of the database (layout for table "References"), I've labeled the left "instance" as "Spanish" and the right "instance" as "French," but it does not work right. I want the Spanish "instance" to display only records whose "GlosId" values are 0 to 199, and I want the French "instance" to display only records whose "GlosId" values are 200 to 299.

 

Below the portal I want to display two simple text fields, "Def" and "Ex," which simply provide the definitions and examples respectively of the Spanish or French word that the user selects in the portal. 

 

CAN THIS BE DONE? PLEASE OFFER SOLUTION OR A BETTER WAY! THANKS-

[efen]

Isn't this a different structure from what was suggested by comment in your earlier posts?

http://fmforums.com/forum/showtopic.php?tid/213081/post/350129/hl//fromsearch/1/#350129

I don't think that this is possible in one portal using the same field.

The only (?) way that I can think of doing this is two portals - one showing rows 1- n and the other showing 201 onwards side by side and dummy records to fill any spaces in the sequence of records to the end of the Spanish words where no Spanish words are present - see attached

02-19-10_Copy.zip

[David McQueen]

There are a couple of problems here.

1. Your records represent French and Spanish and they do not know that. Therefore your selection portals also cannot know that.

2.Right now your records are effectively paired, but they do not know that either. For every French record there is a corresponding Spanish record. Both of them lead to a definition.

For #1:

Create a second key that is a language key, "L_Key", for each record. It will contain either an "F" or an "S" depending on the record and will be indexed. Then create two calculated fields with values "F" and "S" respectively "c_L_Key_F", "c_L_Key_S".

This will allow you to create two self relationships:

c_L_Key_F::L_Key_F for French

c_L_Key_S::L_Key_S for Spanish

Then create two portals on the screen based on these and the records in one will be French and the records in the other will be Spanish.

You records now know what language they are. Note that the orders will not correspond between portals unless you do some serious ID coorelation example:

ID 101 in French is the same word as ID 201 in Spanish

Then sorting by ID in each portal would give the corresponding pairs if that matters.

Problem #2 is a little trickier.

You need to be able to pick a word in one language and see the definition and example in the other .

If in the initial records you also put both a definition and an example field, you could access one from the other with a self relation via a join table.

In this table there would be a JoinID, which is a unique identifier, LinkIDFrench and LinkIDSpanish in each record. These would be the same ID's as the corresponding ID's in your main table.

So a join record is created that has the ID for the French word and associated definition and example and the ID for the same Spanish word.

Then you can create two slef relationships through the join back to each other:

1.

IDFrench:: LinkIDFrench to the join table

LinkIDSpanish:: IDSpanish back to the main table.

This would give you the Spanish for the selected French

2.

IDSpanish::LinkIDSpanish to the join table

LinkIDFrench::IDFrench back to the main table

This would give you French for the selected Spanish.

The big trick will be creating a process to simply create the join table records.

Hopefully I have been clear. I have covered a lot of ground.

Also, there are probably other ways of doing this, but every little bit helps when you hit a wall.