Can you explain a little more about exactly how you want this to work?
Do you want the field to always show the Qty for comps tickets, the current portal row or by some other criteria?
Thank you Phil.
Attached screenshot shows the portal with three rows of data and two portal summaries at the bottom: Tickets Available, and Gross Potential.
To calculate the Average Ticket Price (the green outlined box), it should be a simple equation: Gross Potential/Tickets Available.
However, we cannot include the Ticket Type = Comps in this calculation, because the average ticket price is only based on tickets for sale to the public.
For the moment, my formula for Average Ticket Price is:
If (Ticket type="General Admission"; Ticket price; Gross potential / Tickets Available)
...but there are many other records in this database for which this formula is not sufficient.
What I was trying to do was reference the number of comps that are typed in to any portal, so they could be subtracted from the Tickets Available. Possibly in a formula like this.
Average Ticket Price =
Gross potential / (Tickets Available - Comps Amount)
My thought was to create a new field, independent of the portal, called "Comps Amount" to power this formula, but what I cannot figure out is how to reference, or copy, the number of comps typed into the portal row and auto-fill the "Comps Amount" field.
There's a good chance I'm over-thinking things.
Thanks for your help.
You have two options:
1) set up a special relationship to a new occurrence of the portal's table. Use the same match fields that you do for the portal's relationship but then add one more pair of match fields constComp = Type, where constComp is defined to return the text "Comps" in all cases. Then you can refer to fields in this new table occurrence to just get numbers for Comps tickets and you can use that in your calculation. No new fields need be defined for this method. (But you may want to use Sum() if there is the slightest possibility that two comps entries might be made for the same event.)
2) Use ExecuteSQL to access this data. A WHERE clause can limit the query to just comps records.
Thank you for this thorough advice, Phil. I will try #1 and see if I can make it happen.