13 Replies Latest reply on Jan 23, 2014 8:34 AM by erolst

    Relationship and Table issue.

    MallieWilliams

      Good morning guys..I have three tables which I think that I may need to add another. The problem is I have 124 tickets for Friday and 124 tickets for Saturday. Each contact or record can have tickets for Friday, Saturday or Both. I need to be able to track the amount of ticktes and when they run out do an conditional value saying "SOLD OUT".

       

      I also have 3 hotels the Hampton, Courtyard and Spring Hill. The Hampton has 12 rooms all doubles which can be booked for Friday, Saturday or Both. Then I have the Courtyard that has 7 Kings and 7 Doubles which can be booked for Friday, Saturday or Both. And the last hotels which is Spring Hill has 15 Kings and 5 doubles which can be booked for Friday, Saturday or Both also. What tables should I modify or add to keep track of the tickets Sold and the Rooms sold. This problem does come from my last post which was fixed thanks to the help of everyone.

       

      I know that the first table contacts should be one-to-many. Due to many contacts can have many tickets. The question is do I need to create a table for Friday, Saturday and Both tickets? How would I count down the ticket count for both days? Attached is the tables that I have created. I just confused into where I should go from here.

        • 1. Re: Relationship and Table issue.
          erolst

          You should finally get around to adding an Events table to your setup, as was recommended to you several times in diverse threads; solving portal problems won't help you if your structure can't provide what you need.

           

          In light of these new requirements, you will need some more tables in addition to Events (did I mention that you need (at the very least) an Events table? Don't come back before you have one!) , like …

           

          Contacts --> Guests (with numTickets) >--< Events (date, capacity) >-- Rooms (capacity) >-- Hotels

           

          where a Guest is simply a contact with one or more tickets; each Event has a capacity (which it could inherit from its Room); to know if an event is sold out, you'd use something like Events::capacity <= Sum ( Guests::numTickets ).

           

          If you need this for a specific date range, use a date-filtered relationship from Hotels into Events.

           

          Events will be the basis for a schedule; you could use another table to define standard days for Rooms and use that as the basis to automatically create new events.

          • 2. Re: Relationship and Table issue.
            MallieWilliams

            Can you give me an example of how you would layout these tables? And now I do see why the table will be called the Events table..due to the tracking scheduling of the days.

            • 3. Re: Relationship and Table issue.
              MallieWilliams

              I created a check box list for Friday, Saturday or Both on the contacts layout. This is the way I have my layout setup..And this is the way my tables are set up and naming conventions.

              • 4. Re: Relationship and Table issue.
                erolst

                MallieWilliams wrote:

                I created a check box list for Friday, Saturday or Both on the contacts layout. This is the way I have my layout setup..

                Did I mention that you need an Events table? IMO there's not too much point in this discussion before you add it (plus (probably) the other tables I suggested)? – But let me try to give some pointers to get you started:

                 

                As to the layout (relationships) of the tables: see the previous post. Take it as a starting point and try to think of all the things (entities and their attributes, i.e. tables and fields) you need to track (and the things you need for those things …), and how they are related (as indicated by the arrows in the example). Add what you think missing. – A session with pen & paper may help there. Also, this is an iterative process; some false starts will only become obvious after some use, and you will revise some of your initial decisions as to what belongs where, or how it is related.

                 

                You should probably read this: http://fmhelp.filemaker.com/fmphelp_12/en/html/create_db.8.2.html#1027557

                 

                Then experiment a bit with your initial setup. Create a hotel, some rooms, an event (based on a room and its capacity), and create some guests with a certain number of tickets, to check if the Sold out calculation works. Do the same with other fields and functions.

                 

                Also, consider putting aside your existing database – which is obviously based on a Starter Solution – and starting with a fresh file. This will let you work with just the things you actually need for the problem at hand, without being distracted by existing layouts, data and functionality.

                 

                How to actually flesh out this structure depends on your workflows, i.e. creating and tracking events, distributing tickets to contacts, and the type of data you want to generate (reports, statictics etc.)

                 

                As the great Robert Heinlein used to state: TANSTAAFL

                • 5. Re: Relationship and Table issue.
                  MallieWilliams

                  This events table that you keep telling me I need. Is this a join table that I need in between Contacts and Tickets? Or is this just another Child table that needs to be added? I thought that maybe I just needed. Contacts, Tickets, Hotel and Days. Where would the event table come in?

                  • 6. Re: Relationship and Table issue.
                    erolst

                    MallieWilliams wrote:

                    I thought that maybe I just needed. Contacts, Tickets, Hotel and Days. …

                    If you think that, then we're back on square one. Tickets for what? How could you tell that something is sold out if you don't have a something?

                    And what about the rooms you mentioned?

                     

                    Also, I already showed the structure, as I understand it:

                     

                     

                    Contacts --> Guests (with numTickets) >--< Events (date, capacity) >-- Rooms (capacity) >-- Hotels

                     

                    Why don't you try to describe to yourself what entities you're trying to track (and read up on relational database design, e.g. in the link I provided):

                     

                    "A contact can be a guest for (attend) many events (and buy >1 tickets per event), and an event can have many guests."

                    "An event has a date (and a time?) and a contingent of tickets, and it takes place in a (1) room."

                    "An event is sold out if the sum of the tickets bought by the guests exceeds the capacity of the event."

                    "A room can host many events, but each room belongs to one hotel."

                    "A hotel can have many rooms."

                     

                    Think about that, and try to fine-tune it in regard to the attributes of those entities and your business rules (e.g. Contacts have preferences as to the weekdays they wish to attend?! Take into account when showing events to assign Contacts to Events; or: can the same room host several events on the same day (matinée, afternoon, evening?) etc.

                     

                    Then create the tables for the entities you've identified and start ”playing around“.

                    MallieWilliams wrote:

                    This events table that you keep telling me …

                    Actually, it's the Events table that everyone is telling you you need: http://fmforums.com/forum/topic/90589-calculating-ticket-sales/page-2#entry415977

                    • 7. Re: Relationship and Table issue.
                      MallieWilliams

                      I'm going to try to explain to what I'm trying to do. I just think that there is a communication problem of how I'm explaining things. I do have an Events Table it's just the naming convention is different. My tables are setup exactly the same as this example and they both work the same. I'll explain the difference. Okay. Looking at the sample file SoldOutMOD. In the Event Layout the Event field should be the First Name looking at Example 2.png file that I uploaded. The second difference is that the Contact ID represents the ticket sales. Each line can have a qty up to the capacity level. What I would like to happen is that each time you put at qty of tickets sold the Capacity number will decline . Keep in mind that it needs to decline for each record (EventID) if that make sense.Once the capacity reach 0 than the cSoldOutLabel will be SOLD OUT. What changes or calculation is needed to get this to work? It seems that the cSumQty  only Sums up the Tiket qty in a single record instead of across the board.

                      • 8. Re: Relationship and Table issue.
                        erolst

                        Sum() doesn't work for the found set; it sums related records, or a number of values or field references that you feed it directly.

                         

                        But here's an example: if the layout you're on is your Events table, and the tables has a field for capacity; and if the portal you're looking into shows records from your Guests table (where guest is a contact with a ticketQty of 1 or more), then you can

                         

                        • create a field as cSumTickets, = Sum ( Guests::ticketQty )

                         

                        • create on your layout a text object "Sold out" and use Conditional Formatting (CF); calculation: cSumTickets < Events::capacity; format: text size 500 (which means its effectively invisible as long as the condition is true)

                         

                        if you don't need the field as such, you can use its calculation directly within the CF: Sum ( Guests::ticketQty ) < Events::capacity. Also, you can use the hardcoded value 130 instead of Events::capacity, but then events take place at different venues, each of which has a different capacity which then is an attribute of the event – as per my previous post(s).

                        • 9. Re: Relationship and Table issue.
                          MallieWilliams

                          Aight..so I followed the steps in the previous post. The only difference is that the Guest::ticketQty is actually the Contact::ticketQty. This is the SoldOut2MOD file. Attached is the tables that I've edited occording to your previous post. Let me know if I misunderstood what was written in the previous post. After making these edits, the ticket qty in Contacts (Guest) table in the portal goes to the next record (Event) field. The related records in the portal are from the Contacts Table (Guest). In the ticket Qty should get closer to the capacity say "130" for each record. (example) EventID 1 can have a Ticket qty of 100 then EventID 2 can have an Ticket qty of 30 which the EventID1 and EventID2 should sum up the "130" which will = tickets being SOLD OUT.

                          • 10. Re: Relationship and Table issue.
                            erolst

                            Yes, you're getting closer … I guess it comes down to what you want to track. If you are (or have to be) interested in Contacts as such (Addresses, Names, Invoicing asf), then a ticket sale is a join table entry between Contacts and Events; otherwise you just have a TicketSales table that is a child table of events, where you can also enter the name; but you wouldn't necessarily care for relational “cleanliness”.

                             

                            In order for this not to take another several fruitless weeks, here's a quick sketch that hopefully will help you along.

                            • 11. Re: Relationship and Table issue.
                              MallieWilliams

                              Hey..Erolst I really appricate you helpong me out. The file that you sent shows a real close example of what I'm needing. In the previous message I stated that

                              EventID 1 can have a Ticket qty of 100 then EventID 2 can have an Ticket qty of 30 which the EventID1 and EventID2 should sum up the "130" which will = tickets being SOLD OUT.

                              On the TicketSales file you sent. I changed the Event::_fk_contactID drop down tabs to Friday and Saturday since these are the two days that tickets will be sold. Friday will have 124 and Saturday will have 124. So the capacity of each is set to 124. As far as the Contacts that will be in the Events::Name field out side of the portal like the example that I have attached. I need the capacity to maybe be a global field. So that when you go from one record to the next it will total up. For example capacity set at 120, so first record Event::Name= Tom who has TicketSales::qtBought=100 than next record Events::Name=Jane who has TicketSales::qtBought=20. Now qtBought totals to 120 which the two records are added now = SOLD OUT. I think the issue is in the way I need to write the cSumTicketsSold. Some kind of way the calculation should be sum by the two records or something.That is the only thing that I'm trying to fix with this problem so this will not take another several fruitless weeks. I hope you understand what I'm saying. If you look at example 1 and 2 the way I have my portals set up. Looking at the way I have my portal set give me your feedback on Tickets tab and the Hotel tab.What I'm trying to do is Sum both the highlighted qtyBought for both records  as shown in Example 3 and then display the SOLD OUT..

                              • 12. Re: Relationship and Table issue.
                                MallieWilliams

                                I think I got it.. I had to use a Summary field to add the records..Now I just have to write a calculation once it reaches the capacity.

                                • 13. Re: Relationship and Table issue.
                                  erolst

                                  MallieWilliams wrote:

                                   

                                  I think I got it.. I had to use a Summary field to add the records..Now I just have to write a calculation once it reaches the capacity.

                                   

                                  Good for you … but you're still confusing Events with Contacts.

                                   

                                  Good luck!