Challenging Fundraiser Project

[alterbentzion]

I've been challenged to create a database for a local organization to use at an annual holiday basket fundraiser. Here's the rub: The system would resemble any inventory/order processing system, except that the customer list would also be the product list. Plus, there's a "reciprocity" option, which I'll explain in a minute. Please help me set this up!

Here's how the fundraiser works. All past and present members of the organization receive in the mail a list of names, which includes all those past and present members. The recipients check off the names of people to whom they want to send baskets, then return their forms (with a check, of course!). The size of the basket any given person gets depends on how many people ask us to send one to him/her, and the basket is delivered with a list of all those senders. Out-of-towners receive a greeting card in lieu of a basket, and the card is imprinted with a simliar list. To make things more complicated, there is also a "reciprocity" option, whereby members can request that we add them to the "senders" list of anyone who sends them a basket. ("Reciprocity" orders are billed at the end of the fundraiser.)

I need to provide our volunteers with a way to record orders, to automatically generate "reciprocal" orders, to tally those orders (so we know what size basket to send), generate de-duped lists both for baskets and cards, and bill for the "reciprocity" option.

Can I do this with multiple table instances/relationships? How? Any suggestions would be greatly appreciated! Thank you.

[comment]

I may not be seeing ALL the requirements here, but it looks like you need only two tables: People and Links. The People table has PersonID and other details. The Links table holds FromPersonID and ToPersonID.

You will need to connect People to several occurences of the Links table. This relationship:

People::PersonID = Links::FromPersonID

shows the people to whom the currently viewed person wants to send.

This relationship:

People::PersonID = RevLinks::ToPersonID

shows the people who want to send to the currently viewed person.

In order to remove the people already on the "To:" list from the "From" list, you will first need to create a value list of the "To" ID's (values from field Links::ToPersonID, show only related, starting from People). Define an unstored calculation field cToIDS (result is text) =

ValueListItems ( Get(FileName) ; "ToIDs" )

This relationship:

People::PersonID = BackLinks::ToPersonID

AND

People::cToIDS ≠ BackLinks::FromPersonID

will show the people who sent to the currently viewed person, but not the other way round.

[alterbentzion]

That was quick - thanks!

I don't think I've ever seen the notations "=rev" and "=back" before. What do they mean/do?

[comment]

RevLinks and BackLinks are names I picked for what would initially be Links 2 and Links 3, i.e. additional TO's of the Links table.

I forgot to mention that you will also want to attach a TO of People to each one of the Links occurences, so that you can see their names in the portal/s.