Relationship question

[jessicanacac]

I'm new to relationships. Is it possible to create a realational database like this & if so how? I'd have 3 databases

1. Items

2. Donors

3. Baskets

I would want to pull out information like Basket B = item 2 & donor 3. Basket C = items 4,5 & 6 & donors 2, 3 &4.

Is that possible to do? Would I need 3 databases? Thank you for your time.

[QuinTech]

Hi, Jessica. In almost all situations, the best structure is to have one database for each type of object; Items, Donors, and Baskets certainly qualify. (Additionally, Donees would probably also qualify as a different type of object even though they, like Donors, are probably the same type of object in the physical world, namely humans. But the definition of an "object" from a database perspective has more to do with function than anything. But that's beside the point. )

In each file, have an ID number field. This should be an auto-enter serial number field, preferably prefixed with the object name ("donor00001", "basket00005", "item00537", etc.) Also, in the Baskets file, have a DonorID field; you will use this to associate each basket with its donor. (It is much better to use ID numbers in relationships than to use names; names can change or be duplicated.) And in the Items file, have a BasketID field.

You will probably want to enter baskets from inside the Donors file; for this, use a portal based on a relationship from Donors::DonorID to Baskets::DonorID which allows creation of related records. Also, you will probably want to enter items from inside the Baskets file; for this, use a portal based on a relationship from Baskets::BasketID to Items::BasketID which allows creation of related records.

There is a lot to unpack here; try taking it step-by-step and asking questions as you go. Your original question has a very BROAD scope.

HTH,

Jerry

[RalphL]

From what I see here, ithink you have a more complex relationship. An Item can be in 1 or more Baskets, A Basket can contain 1 or more Items. A Donor may give 1 or more Baskets. A Basket can be given by 1 or more Doners. Thus we have at least 2 many to many relationshiips. If there is a relationship between Donor and Item it too would be a many to many. This might best be represented by a star join. One join file which is related to each of the other 3 files.

Start with ideas proposed by Quin Tech and see where that takes you. Then we can add the join file(s).

[QuinTech]

I see where you're coming from, Ralph, and i had considered going into join tables; but it's not clear that there will be many-to-many relationships. For instance, two baskets may contain a bottle of whiskey; but is it really important that that bottle of whiskey be represented by the same record? That may introduce more complexity than needed.

But when you mention the Donors-Baskets relationship, i can see how this could very easily become a many-to-many relationship; one donor gives two baskets by himself, then contributes part of one other basket along with two other people. Yeah, i could definitely see that happening. So thanks for the catch. Although i think a star join is out of the question (using FMP 5).

J

[jessicanacac]

I'll try to explain it better. Some donors will donate muliple items. Some items will stand alone other items will be combined into a "basket". The items in the basket will not necessarily be from the same donor. I'm trying to set this up for a silent auction for our non-profit. Thank you for your help. I've been working on this for 2 days and I keep going in circles.

[RalphL]

I haave put together a set of 4 sample files which is a simplier than what I had first suggested. Take a look at them and let us know if this close to what you want. They aren't fancy.

[jessicanacac]

These seem to be exactly what I've been trying to do. I don't understand relationships very well. From which database would the data entry occur?

[RalphL]

These are not finished. I intended that donations would be entered through the Donor table (file) and the Bakets be assembled through the Basket table (file). Do you need to split a gift, i.e., Donor 3 gave 2 items? These could be kept together or split between 2 baskets.

Another button is required to remove items from a basket.

Would you like me to write the scripts for that button?

[jessicanacac]

Isn't it possible to enter the additional items per donor right on the donor database in the items portal?

Yes Donor 3 can give 2 items which then can be split between 2 baskets. Does that need another button?

The button to remove the items from the basket would be great. Thank you for all your help. I really really appreciate this.

[RalphL]

Yes, I have set the relationship allow the creation of new records.

No, it will require that the quanity be entered each time and the enter key pressed.

See the attachment.

[jessicanacac]

How do I get the value of the basket if there's more than 1 item in the basket?

[RalphL]

Add a calculated number field Total = Sum(Line::Value).

In the value field the amount should be per unit, i.e. the 2 bottles of wine should be $30 each.

In Item you should add a calculated number field Extended Value = Quanity * Value

In Donor you should add a calculated number field Total = Sum(Item::ExtendedValue).

I keep seeing things I missed.