One-to-Many

[MrRage]

How do you establish a One-to-Many relationship database? I was making a simple list example and noticed that each time I added an item to my list it created a copy of that item, if it were one to many then I should be able to add a single item to many lists. At least thats what I think it will do.

[RalphL]

One to many relationship, on the one side a record can have many related records on the many side. For example a Family has many Members. If you add a member to a faimly you don't add a member to many families. Another example you are checking out at the supermarket. The checker in another aisle scans an item, do you want that added to your bill?

I hope this clears up your thinking.

[MrRage]

Hmm, so if one to many isn't the type of relationship I'm looking for to solve this problem, any ideas on what is?

Here is my table layout:

ListName // A name associated with a list

ID // This ID is what the relationship is based on

[item]

ItemName // Items name

ID // This ID is what the relationship is based on

I'd like to have multiple lists that use the same items, however I don't want to copy the item for each list. Somehow ID needs to contain references to more than one table, like a set of ID numbers instead of just a single ID number.

Any input would be very much appreciated.

[MrRage]

I think you need a 3rd table to joine the two tables togeather... but not sure exactly how that would work

[Brian C]

The answer to this is actually very simple.

Just as you can use a multikey to link to many related records, you can also reverse this idea to have it work as you are describing.

Multikeys are stored in a return delimited format.

Create a global field called "GSelect" in the Items database. This will be used to store the ID of the List that you want to add.

Next use:

setfield(target,source)

target = the ID field

source = ID & "P" & GSelect (The "P" is a Hard Return character)

This will allow your ID field to store multiple IDs.

Example:

Item ID =

1

3

2

7

The above will allow any Lists with the above ID numbers to match to this Item.

you may want to make the result of the setfield function use an IF or a CASE function to determine if the ID field is blank (use the ISEMPTY(field) function) so that you do not wind up putting a blank line at the beginning.

[MrRage]

Ah cool, thats a very simple solution - thanks a lot