Each year our church puts on a special silent auction in which about 50 food-related events are put up for auction over a four week period. We usually have over a hundred different people bid on these items. Each event has a paper bid sheet and a limit on the number (this varies) of people who can win a place at the table for any particular event. There is one line per person per bid. Nothing is crossed off. If someone wants to place a higher bid he or she just fills out the next available line. At the end of the auction the bid sheets are reviewed by hand and the highest bids determined in the order they were filled out on the paper form.
I am very interested in turning this whole paper-based and very time-consuming process into a FileMaker Pro 11/12 database for next year's event. We have plenty of time to work out a design and use all of this year's data to test how it works. As a first draft of a database I have set up three related tables in one file: Bids, Bidders & Events. Bids is simply a sequential listing of every bid that comes in with bidder/event numbers and the bid amount. Each Bidder page displays a portal into Bids showing what Events they have signed up for. Each Event page displays a portal into Bids showing the Bidders who have signed up. I also have created a summary field that calculates the total dollar amount of bids for each Event, and the total dollar amount of bids for each Bidder.
Where I need coaching right now is how to set up variable computations that can automatically calculate the top "x" number of bids for each event. For example, if Event #1 has only four slots available, and 30 people bid (some of them the same person raising their bids), how can I:
- calculate and highlight just the final four highest bids (in the order in which they were placed in case of ties)
- calculate the total revenue for that event on just those four winning bids, and
- place just those winning bids on the summary page for each winning Bidder?
I know there has to be a way—and maybe an easy one—by which FM Pro can accomplish these kinds of calculations. But right now I don't know how to proceed or even think about how to proceed. I would appreciate any ideas or help to give me a push in the right direction!