Sum of Prices using Case Statement
I am trying to use a calculation field to get the sum of a subset of records using a case statement.
I have a table called 'Bids' with the fields 'Bid Price' and 'Mark'. I have a second table called 'Shots' with a field called 'Shot Cost'
On the 'Shots' layout, I have a portal to the 'Bids' table containing the fields mentioned above. I'm trying to get the 'shot cost' field to display the sum of 'bid price', but ONLY when the 'mark' field contains the word "Mark".
The 'shot cost' field is currently set up as a calculation with the following:
Case ( Bids::Mark="Mark" ; Sum ( Bids::Bid Price ) )
But this results in a sum of ALL records instead of only those that are 'marked'
Record 1 = bid price $10, MARK
Record 2 = bid price $20 (not marked)
Record 3 = bid price $30 (not marked)
Result of Shot Cost = $60 (I only want it to count record 1, it should be $10)
Any assistance would be greatly appreciated.