Calculating Ticket Sales

[kojack1976]

I have three tables (1) Contacts (2)Tickets (3) Guest.. The calculation I have for my EventStatus field is

Case(Tickets::Ticket Qty ≤ 130 ; "SOLD OUT" ; "AVAILABLE TICKETS"

) I'm trying to make sure when the tickets are sold out that that merge field turns red and says SOLD OUT. and if it's not sold out than it will show Available tickets. This should be an conditional format calculation within the merge field.The problem I'm having is that the Tickets::Ticket Qty = 130 its not showing sold out, when I place other numbers that are more than 130 or some that are less it pops up. Maybe it has something to do with the relationship? What do you'll think? This calculation should add up the Ticket Qty in the portal, once the cout = 130 it's SOLD OUT if not TICKETS AVAILABLE should appear if that make sense.

[Wim Decorte]

We had this thread over on TechNet didn't we?

 

Why don't you start with explaining the table structure that you currently have?  Obviously  you have a "Tickets" table, and it has a "Tickets Qty" field.

 

Is the layout also based on "Tickets"?  How is the "Ticket Qty" field updated?

[kojack1976]

Yes we did..and I still can't get it to work...this should be a simple fix. I need to get me a book on calculations and scripts that's my weekness.

 

Okay..this layout is called Contact Details. This is based on three tables Contacts "Parent Table" and Tickets and Guest which are the child tables. The Ticket Qty field is updated within a portal. I have three records 1 Ticket Qty has 50, secod record has 50 and the third has 30 once this = 130 the field should show SOLD OUT in red text if less than 130 it should show TICKETS AVAILABLE . I will show some screenshots so you can see the layout. If that make sense..

[kojack1976]

Here is the table and layout screen shots.

[comment]

The calculation I have for my EventStatus field is

Case(Tickets::Ticket Qty ≤ 130 ; "SOLD OUT" ; "AVAILABLE TICKETS"

)

 

Shouldn't it be =

Case ( Sum ( Tickets::Ticket Qty ) < 130 ; "AVAILABLE TICKETS" ; "SOLD OUT" )

Or, since I see that you have a SumTicketSales field, presumably summing the same thing =

Case ( SumTicketSales < 130 ; "AVAILABLE TICKETS" ; "SOLD OUT" )

For your conditional formatting, use the same condition, i.e. either:

Sum ( Tickets::Ticket Qty ) < 130

or:

SumTicketSales < 130

---

P.S. Please update your profile - surely you're not using version 3, are you?

[kojack1976]

No..I'm using FM Pro 12 Advanced..sorry about that..lol  I'm going to test these calculations to see which work better. The problem I'm having is within the portal on the Tickets Qty when I add more than 130 it doesn't change when I add less than 130 it still doesn't change. Hopefully changing to one of these may help. I also refresh the window to see if that would help the problem...No change..Ok is there anything else that can cause these changes not to take place when the Tickets Qty field is modified? When I change the operators > to <  text will change from SOLD OUT  to AVAILABLE TICKETS the problem is that when I change the numbers in the Ticket Qty field it doesn't change the SOLD OUT or AVAILABLE TICKETS.

The SumTicketsSales Calculation is Sum(Tickets::Ticket Qty)

The EventStatus is Case(SumTicketSales > 130; "AVAILABLE TICKETS" ; "SOLD OUT")

By having all these fields in the Ticket table is that causing the relationship not to work?

[kojack1976]

Case ( SumTicketSales < 130 ; "AVAILABLE TICKETS" ; "SOLD OUT" )

The problem is that when the operator is turned < it shows sold out only..even when I put under 130 or over 130..so there is still an issue of the numbers not being calculated. Whe the operator is turned > then it shows Available Tickets. Still when the number is about 130 or below 130..

[comment]

Uhm, does the attached work for you?

SoldOut.fp7.zip

[kojack1976]

This works when the total count is on 1 record. The way I'm trying to get it to work for example. Lets say Adam is on 1 record Betty is on another record and Cecil is on record 3.. 80 is on Adam 30 is on Betty and 20 is on Cecil.. Then it will total up each record for total of 130 which will = to the cSoldOutLabel and say Sold Out. If that make sense..

[comment]

I am afraid we are not on the same page. In my file, Adam, Betty and Cecil are individual records in the Child table. You see them on the Parent layout inside a portal to the Child table.

 

The Parent table plays the role of Events, the Child table is Ticket Sales. I think you want to make sure that the total of tickets sold per event does not exceed 130. If that's not so, we need to go back to the beginning.

[kojack1976]

I see..I'm Looking at he Layout::Parent Record (Alpha) whick is  record 1 in the portal it shows Adam, Betty and Cecil on 1 record. When I go to record 2 Parent Bravo it shows in the portal David and Eve.. The record is named by the ParentID or Parent name. So how is it that Adam Betty and Cecil are on different records? If you look at the attached thumbnails at the top you can see the layout that I upload. What needs to be added are the Ticket qty on different records. If that make sense. The ticket table and the Events table should work the same. They are both being sold until the count at the max. I appricate the example though.

[comment]

Sorry, I don't understand what you mean. Your screenshot shows a portal under the title "TICKET SALES FOR THIS EVENT". I presume that is a portal to the Tickets table, placed on a layout of the Contacts table. That is the exact equivalent of the portal in my file.

[kojack1976]

In the parent table you have 4 records..Each with different names in the portal and different qty in the portals. I wanted the qty for each record to be totaled, if the total = the 130 than it's sold out if not than it's available tickets. The Sold Out file adds up just the qty in the portal. So if the qty in the portal is totaled it will display Sold Out if not Available ticket. This is all in one record if that make sense. The calculations that we used works on a single record to total. It's just not working to total all records. I guess it's like having a summary field that adds all records and once it gets to the grand total of the number its = Sold Out if it don't than tickets available.

[comment]

In the parent table you have 4 records..

 

I am afraid you are very much mistaken about that. There are two records in the parent table.

 

This doesn't seem to be going anywhere. I suggest again that you explain - in non-technical terms - what exactly are you trying to accomplish here.

[Wim Decorte]

This is pretty much what happened on TechNet too and I realize it must be frustrating for Kojack1976.

 

Kojack: can you work with Comment's demo file or is that too different from yours?

 

What you are trying to achieve is not that difficult but we simply don't have a good view on your current structure and your explanations don't always seem to help; I think they trigger some assumptions on our part that are not true.

[comment]

What you are trying to achieve is not that difficult

 

At this point I am not at all sure what exactly is it that kojack1976 is trying to achieve; if it's what I think it is, it certainly shouldn't require >15 posts.

 

@kojack1976:

I am attaching a slightly modified version of my file. It's actually the same thing, only with some names changed and a few annotations added. Hopefully it will help to move this issue forward.

SoldOut2.fp7.zip

[kojack1976]

Yes your right..It shouldn't require this many post. I know I am speaking in plain terms. All I ask is for the qty across the records to be totaled. Once the total of the qty across for example two records like in the last file that was sent = 130 the it's SOLD OUT. I'm sorry if you're not understanding what I am saying. On each record there is a client that is purchusing 1 or more tickets. When the total of sold tickets reach 130 it's sold out. this is a one to many where 1 client can have many tickets.Like I said again nicely..I appricate your help. I tell you what..how about I upload my file will that help?

[comment]

I know I am speaking in plain terms.

 

 

No, not really.

 

 

All I ask is for the qty across the records to be totaled.

 

 

That's not "plain terms". There are two tables here. When you say "across the records" it's not clear which records in which table you mean.

 

I suggest you leave the records aside for the moment and explain the real-life scenario you are trying to track. So far, I think we have all assumed that you can have many events, and that for each event you can sell no more that 130 tickets. That is exactly what my file does. If that's not what you need, you must explain the situation we are supposed to deal with.

[kojack1976]

The three tables I have are Contacts, Tickets and Guest. Each Contact can have in the portal under ticket qty 1 or more. Once there are 130 tickets Sold. the EventsStatus field should = SOLD OUT or if less Available Tickets. I'm tracking the amount of tickets sold for each contact. The file that you sent = SOLD OUT only on the first record if it = 130. Take away the event that your talking about and think per contact if you don't want to say record.

[LaRetta]

The problem appears to be perspective.  Kojack is standing on the Contacts layout while in Comment's file you are standing on the Events layout.

 

It appears that Kojack wants the message to display from perspective of the "ticket buyer" (Contact).  Maybe if the Contacts table were added and perspective changed to there for the calculation?  I could do it but I do not want to interfere and add confusion.  I might have already and if so then I apologise.

 

Kojack, hang in there.  We'll get it nailed yet.  And yes, attaching your file (zip it first) would help.  :-)

[comment]

I'm tracking the amount of tickets sold for each contact.

 

Well, that's good. So just take my last file, and change the name of the Events table to Contacts and the field names in this table accordingly. That's all you need to do. Note that the names of the "contacts" in my file are Alpha and Bravo. The other names (Adam, Betty, Cecil, David and Eve) are the names of the people who bought tickets; it sounds like you don't want to track them, so just delete the TicketBuyer field in the TicketSales table and you are done.

[kojack1976]

Thank you LeRetta.. you hit it dead on the nail. I will upload my file.

I wanted the Alpha and the Bravo qty to be added. Say Alpha had 100 tickets qty and Bravo had 30 tickets qty = 130 so there for Sold Out. Two contacts who bought a grand total of 130 tickets so now it's sold out.The cSumqty is calculating just the Alpha or just the Bravo. Instead of the qty in both Alpha and Bravo.

EventPlannerb.zip

[bruceR]

Your description does not make sense and does not match your file.

 

Though you claim that "Contacts" are actually Contacts, everything about the layout says that it is an EVENT. 

NOT a contact.

And you want to show how many tickets are sold for the EVENT.

 

You don't even have an EVENTS table.

You don't know which EVENT a CONTACT is selling tickets to.

[LaRetta]

This is the Event Starter Template and it had an Events table.  Why did you remove it?  That is where you should store EventDate, Quantity of tickets to sale - see Comment's demo again.

 

Questions:  Can a single Contact have more than one Event?  Do Contacts buy tickets or do Guests buy tickets?  Or do Contacts buy tickets and then distribute to Guests who are then recorded?

 

We need to understand the workflow a bit.  :-)  Almost there!

[comment]

These two statements contradict each other:

 

I'm tracking the amount of tickets sold for each contact.

 

I wanted the Alpha and the Bravo qty to be added.

 

The Alpha and the Bravo qty are "the amounts sold for each contact". If you add them together, there will be only one amount - the total of tickets sold for all contacts ever.

[kojack1976]

I want the calculation to be based on the total of tickets sold for all the contacts. meaning if Alpha and Bravo and Charlie if thier total tickets doesn't add up to 130 than it's still tickets available. If one of them or all of them had 130 then it's Sold Out. Did you look at my file to see what I was trying to do?

[LaRetta]

I want the calculation to be based on the total of tickets sold for all the contacts. meaning if Alpha and Bravo and Charlie if thier total tickets doesn't add up to 130 than it's still tickets available.

 

You want total sales for all Contacts for a single Event to total.  This is what you are missing.  If you do not have an Events table then there is no place to store the total Capacity for the Event, the Event Date or the Event Name.  IOW, you need something to 'group' the ticket sales for all Contacts by the Event.  You removed that table from your solution.

 

I decided not to make the changes in your file (the original Events Management template) because it is more complex and I wanted you to see Comment's file which shows it in clean perspective.  If you wish to replicate the style in which it started, go to the original starter template and import the Events table then work from there but understand the logic in Comment's file (modified as attached) where I have included the Contacts table to round out the requirement.  The portal to Tickets was copied from Events and placed directly onto the Contacts layout and it displays all ticket sales for that Contact.  Note now that a single Contact can attend more than one Event.  

 

Now go to the second Event Bravo and change the Capacity from 75 to 100.  The message changes to 'Available' since only 77 were sold.  I hope this moves you forward.  

Also note that I have added a ContactID into the TicketSales table.  Now you can have multiple Events and you can have multiple Contacts attending single Event and you can have a single Contact attend multiple Events.  Cool beans.

 

EDITED:  I replaced the file because I had neglected to change a label on the portal.  On Contacts, the first field in the portal is the EventID and NOT Contacts.  

SoldOut2MOD.fp7.zip

Edited December 31, 2013 by LaRetta

[bruceR]

Kojack: you just are not making any sense.

You have stated:  "If one of them or all of them had 130 then it's Sold Out."

The crucial part is what is >>IT<<.

 

IT is an EVENT.

Your EVENT has a capacity. The total number of ticket sales for an EVENT cannot be greater than the EVENT capacity, and tickets might have been sold to any number of contacts.

[kojack1976]

BruceR... I am making sense.. If one or more of the contacts = the total sold limit which is 130 than its sold. If less than tickets are available.I'm speaking in palin english.

[bruceR]

Speaking in Palin English. Perfect.

No Kojack, you absolutely are NOT making sense. Your descriptions are inaccurate and incomplete. Comment provided the correct answer at message eight.

[kojack1976]

No Comment provided the correct answer at message 27.. And yes I was speaking in Plain english.. PERFECT!

Thanks LeRetta for your patients and help!

[bruceR]

LaRetta's answer in #27 is the same as Comment's answer in message #8. It is the sum of tickets sold for an event.

[kojack1976]

Loretta’s file in comment 27 is totally different from comment 8 file. There are three tables vs. two. There is also a capacity level. It’s ok BruceR we got it.

[LaRetta]

Speaking in Palin English. Perfect.

 

Please do not ridicule people if they make a typo or speak a bit differently than you think they should.  People come from all around the world and for some this is not their native language.  It clearly addresses this in forum rules, Bruce.  I do not see how your posts are contributing positively in any way - only to pick.