6 Replies Latest reply on Oct 21, 2016 2:12 PM by pics4u

    Ticket Price database based on date


      Hello all,

      I am working on simple database for selling tickets to an event. I would like it to lookup the price of the ticket based on Ticket level (lower, upper etc) and the date sold.


      I have a tickets table that has Ticket ID, Ticket Date sold, Ticket level, Qty and How paid


      I have a ticket price table that has Ticket level, Date and Price. I have entered every possible level, date and price combination as separate records.


      I have setup a relationship with the ticket and price tables:

      Tickets:Ticket Type = Price:Ticket Type


      Tickets:Date Sold = Price: Date

      I have the Price records sorted by descending Date


      For some reason the price for the earliest (and cheapest) price keeps coming up, instead of the last weeks (& more expensive price). I set my test up with this week, last week and the week before that dates. I would also prefer to not enter every single day of the 3 week sales period, generally there are 3 prices changing weekly.


      If anyone can give me an idea or direction to go in, that would be awesome.

      Thank you,


        • 1. Re: Ticket Price database based on date

          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.

          • 2. Re: Ticket Price database based on date

            Hi bigtom,

            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.

            Thanks again

            • 3. Re: Ticket Price database based on date

              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.

              • 4. Re: Ticket Price database based on date


                your're correct the date in the relationship is greater than or equal not equals, my mistake

                • 5. Re: Ticket Price database based on date

                  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.

                  • 6. Re: Ticket Price database based on date

                    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.