Actually, you have two possible outcomes with your case function. YOu can get the sum of all related records or a null value. Which you will get will depend on the value of Bids::Mark for the first related record in Bids for the current Shots record.
This question pops up here fairly often. See this thread for how to get the totals you want: Sum_Calculation based on condition
If you have FileMaker 12, you can also get this selective total using the ExecuteSQL function.
Thank you for the reply Phil, that link was very helpful.
It's funny because these fields were initially set up per your Option 1 in the linked thread, and it worked well for a while. But any time I try to import a large number of records, the 'shot cost' field stops updating. So I was trying to find a work around. Have you heard of this happening before?
I just tried option 2 that you described in the linked thread, and it worked great. My second question would be as follows:
Is there a way to get a summary of the summary field appearing in the filtered portal?
To extrapolate, I have a filtered portal to the 'Bids' table that shows only the records that are "Marked", this portal contains a summary field 'TotalBidsMarked' showing the total price for only those filtered records. On the 'Shots' table I would like another summary field that adds up the 'TotalBidsMarked' field. Is this possible?
Thanks again for your help!
Option 2: (Summary field in filtered portal) is a "display total only" option. It doesn't let you use that total in other calculations. You should use Option 1 for that. I'm a bit puzzled by this statement you made earlier:
it worked well for a while. But any time I try to import a large number of records, the 'shot cost' field stops updating.
Option 1 shouldn't be affected by the number of records in your found set, nor by whether the records are either imported or entered one at a time manually.
I suggest revisting that solution and taking a look at the details in how you implemented that approach. Of particular concern would be if any of the fields involved use aut-enter calcualtions instead of being defined as calculation fields like I describe in the other thread. Auto-enter calcualtions, under certain circumstances--such as an import of records where the "allow auto-entry" option is not specified can fail to update as expected--so that's one possible explanation here.
I decided to go back to basics, and create a new database file to explore Option 1 solution again. I'm still unable to get it to work. In this new solution the 'shot cost' field remains empty. I have uploaded frame grabs of what I built. Can you see what I'm missing? I'm sure it's something simple.
The problem lies with Bids::BidSelectedRelater. It's an unstored calculation and thus cannot be used like this in your relationship.