Not sure what the TicketID or TicketType are exactly. Is this for selling tickets or tracking sales? Both?
It seems you are trying to track sales that have happened in the past as well as manage current pricing.
Lets say I bought a ticket 2 weeks ago for the Upper lever on Wednesday at $10. Now that same ticket is $15 with a price increase closer to the event date, but you still need record of my $10 order, right?
In the end you need a few more than just two tables to manage this. There are a few people here who have done venue seating solutions and they may have more detailed direction for you.
You need all your pricing and you seem to have that in your pricing table. Price changes can be done in bulk with scripting or you can arrange your pricing based on date ranges.
You might consider a table for orders and one for line items that allow you to track actual sales better. The invoices starter solution might be a good place to take a look at the graph for some understanding.
If there is actual assigned seating you need a Table to track availability on specific days. I have seen some solutions with very detailed seating charts and lots of colors. I am sure you need to keep count even if it is a GA ticket free for all.
Thanks for the questions. I didn't want to get too involved with whole process. Let me better explain.
The ticket has a barcode that we'll use to check them in at the event. Thus the TicketID is used to identify the buyer and check-in. Ticket type is seating level.
The pricing does need to be tracked for daily sales reporting.
I'm hoping to use pricing by date range, but I am unsure the best way to do that.
There isn't any assigned seating, just GA, but there are floor and balcony levels.
Tickets:Ticket Type = Price:Ticket Type
Tickets:Date Sold = Price: Date
That relationship does not match to the behavior you report:
the earliest (and cheapest) price keeps coming up
You haven't told us how you set up this method of getting the ticket price, but the method you are using does not appear to be using the relationship you describe. This could be due to errors in layout design, scripting or field options depending on what you have set up.
A relationship that matches by date range can use one of two basic approaches. You can use inequalities, (<, > ) instead of = possibly with a sorted relationship that sorts by date.
You can set up a text field and load it with a return separated list of dates that span a range of dates and then use the = operator in your relationship.
your're correct the date in the relationship is greater than or equal not equals, my mistake
Then this relationship could match to multiple records. If you do not specify a sort order in the relationship, it will match to the oldest related record--and that's consistent with what you report.
If you were to sort the related records (In Manage database) to sort by date in descending order, the most recently dated related record becomes the one accessed by look ups, auto-enter settings and fields from the related table not placed inside a portal.
Thank you, I will mess around with it this weekend. Frustrating thing is I had this working a couple of years ago, then I did something to the script or relationship somehow to mess it up.