If formula on child records
Beginner here using Mac with FMP11.0v4.
I've created a small database to track bids placed on gemstones. A stone can have many bidders, because a stone can have more than one bid. A bidder can bid on many stones, because many stones can receive bids from the same bidder. The Relationship Graph shows the many-to-many relationship. Here are the constraints: For example, there are 80 stones (which will vary at each auction). There will always be two bidders, no more or no less, at any given auction. Each bidder must bid on each stone. The bidder who has the highest bid wins that stone. Each auction does not need to be tracked in the database (at each auction, the data for the list of stones on auction are imported into the Stones table, and the bids data are imported into the Bids table). The database then runs a series of reports.
I'm stuck at the part where, on the Stones layout, I have a portal to the Bids table. When I look at each stone record, the portal properly shows two bids, one from each bidder. I then add a Max summary field under the portal to tell me which of the two bids has the highest bid amount. But, how do I have FMP calculate which bidder name got the highest bid (not just the highest bid amount)? Do I use an IF formula? How do I use that formula on related records in this case? I'm good so far and everything is working until this step.
I also add other calculations, like max bid amount for each stone, multiplied by the stone weight (in carats), to arrive at the total extended bid amount.
Here is the final report I would like to run each time I import stones and bids data into the database everytime an auction takes place (with column headings along the top, and each stone in the auction as rows). The report should list each stone and its characteristics, which bidder won each bid for each stone, and what is the total bid amount payable by the highest bidder.
<stone_packetid> <stone_sort_size> <stone_weight> <bidder1 total value if max bid> <bidder2 total if max bid>
1 10ct 10.232 n/a 102,298.22
2 11ct 5.984 n/a 50,234.88
3 +11ct 7.110 553,231.50 n/a
Total 553,231.50 152,533.10
There are other calculations and fields I would use as well which is currently not relevant to this post. This all works fine in Excel, but I'm trying to automate the entire process by using FMP where I can have all the reports created, and just import the data at each auction.
Thank you again for any help you can provide to guide me.