1 Reply Latest reply on May 8, 2012 1:24 PM by PeterCross

    Questions About Creating An Auction Management Database


      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:


      1. calculate and highlight just the final four highest bids (in the order in which they were placed in case of ties)
      2. calculate the total revenue for that event on just those four winning bids, and
      3. 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!



        • 1. Re: Questions About Creating An Auction Management Database

          Hi and Welcome,


          With most things in FileMaker, there are a million different ways of doing things.  I prefer to keep things as simple as possible and to me this sounds like a good situation where you would use a script trigger that would summarize the entries in the bids table when the bids are entered.  The script could then evaluate the entries in the file, summarizing them according to the logic that you want to use for this. Written out in english, the script would look something like this.  Excuse the simplicity.  This is just an idea.


          Go to the Related Bids // For the Item

          Sort // By person who made them and the amount of the bid in descending order.

          Go to the first record   // this is the highest bid for this person



               Capture information from bid by storing in a variable or text field for viewing   (Set Field or Set Variable)


                    Go To Next Record // the are all lower bids  (Exit after Last!)

                     Exit Loop when Person who made the bid changes

               End Loop


              Go to Next Record // Exiting on the last Record


          End Loop