list relationship (probably easy)

[databaser]

I'm having trouble figuring out how to do this:

I have two tables, with two fields in each.

in table1, i have fieldA, which always has one single name (i.e "John")

and FieldB, which will have a list of things (i.e. "A¶B¶C")

in table2, i have a record for all the possible things that might be in any list in fieldB (say 26 records, where FieldC = "A" in record1, "B" in record2, ...)

Then there's FieldD, in table2, which I want to equal a list of all the names (values in FieldA, Table1) which list the value in fieldC (table2) in FieldB (table1)

I'm having trouble figuring this out.

[Søren Dyhr]

Couldn't we have a file to be "chewing" at ... your urge to keep things as abstract as possible, blurs the way a solution could be picked agaist the other.

The multivalues field occures why and when? Since this is strictly speaking a normalization error needing some ironing!

--sd

[databaser]

in the attached file, i'm trying to create a new field in table2, with the field name "users", where, when widget = "A" (or "B"), users should = "john"

where widget = "C" (or "D"), users should = "Bob¶John"

where widget = "E" (or "F"), users should = "Bob"

(there's no preexisting data, so i'm creating it, testing thing `abstractly`)

databaser80108a.zip

[comment]

I am having trouble following this too. Why don't you use names that mean something, instead of "Table1, FieldA" - we're people here, not machines.

I *think* you're looking for a calculation field = List ( Users::Name ). Or maybe just a portal to the Users table placed on a layout of the Attributes(?) table.

Both require you to have a relationship between the two tables, based on the attribute name or (preferably) the attribute ID. And since you're describing a many-to-many relationship, you should consider using a join table instead of a multi-line.

[databaser]

i'm still having trouble.

it's like if you're making a database of books and students, and who's been reading what.

in table "students", you have a record for each student. in table "books", you have a record for each book.

students::PastReads lists all the books that the student has read

now i'd want to create a field - books::Readers - that lists all students who have read the book.

(from before, FieldA would be students::name, FieldB would be students:pastreads, FieldC would be books::title, FieldD would be books::readers)

also, i can't use a portal (i suspect), because i'll need to use fieldD/books::readers to figure out more complicated stuff later (which i'm not yet able to `articulate`).

[comment]

Take a look at the attached.

Note however, that with this arrangement you cannot tell, for example, when did a student read a specific book. For this (and more features) you need a join table - see:

http://www.fmforums.com/forum/showpost.php?post/246136/

databaserMod.fp7.zip