1 2 3 Previous Next 64 Replies Latest reply on Aug 26, 2011 11:03 AM by philmodjunk

    Table Occurrence help request

    Tbear

      Title

      Table Occurrence help request

      Post

      I have not yet had to create a second TO of any of my tables in the small (10 table) database I'm setting up. I think I may have just arrived at that point.

      I have a CONTACT table related 1-to-many with EVENT INVOICE which is related 1-to-many with EVENT INVOICE LINEITEM. Additionally, EVENT table is related 1-to-many with EVENT INVOICE LINEITEM.

      I realized as I was creating a "Registered Guest List" report to use for check-in at the event, that a customer (Contact) could conceivably buy tickets for a whole group of people.

      I need help figuring out how & where to store the names of those guests for whom someone else paid. (I realize I would have to get a list of the names from the paying customer but that is not a problem)

      I tried creating a second TO of CONTACT to relate to EVENT INVOICE LINEITEM but got lost and ended up dumping that effort. 

        • 1. Re: Table Occurrence help request
          philmodjunk

          What does each lineitem record in Event Invoice LineItem represent?

          • 2. Re: Table Occurrence help request
            Tbear

            Each Line Item record includes the InvoiceID, Invoice Date, EventID, LineItemID, Customer Name, Event Name (comprised of Venue, City, State, Date), Unit Cost, Quantity Purchased, Extended Price and SubTotal.

            • 3. Re: Table Occurrence help request
              philmodjunk

              So one line represents one batch of tickets all for the same event. And instead of customer name, you need a list of those names, one for each ticket sold, correct? And the data on those names comes from the contacts table?

              • 4. Re: Table Occurrence help request
                Tbear

                That is correct except for the very last thing you stated. I only have data in the Contact table for the person buying the batch of tickets. If that customer supplies a list of names of the people who will receive the tickets I could enter those names into the Contact table as well but I don't how best to tie those people to the one "Registration Group" (for lack of a better term). 

                • 5. Re: Table Occurrence help request
                  philmodjunk

                  That's a key decision here. If all you need are a list of names, then it wouldn't make much sense to enter that data into the contact table where presumably you capture more info than just the name.

                  This reads like a case where it would be nice to have a portal inside a portal where a portal inside the line items portal would be used to list the names of the ticket holders, but this is not an option in FileMaker.

                  If all you need are names, you might just add a text field to the LineItems table where you'd enter each name on a different line of text. A script could auto-enter the purchasers name from contacts as the first name if you wanted.

                  You could also set up a "Line Item detail" portal where you'd enter each name on a separate row in the portal. Scripts would synchronize this portal on your invoicing layout to list all the ticket names for the current row of the line item portal. Clicking or tabbing into a field in the lineitem portal triggers a script that synchs the "detail" portal to list the names for that ticket.

                  Using that approach, you'd need an additional table:

                  Contacts---<Invoices----<LineItems----<TicketNames

                  Fortunately, this is a one to many set of relationships as you read them from right to left. If you need to list your ticket names on a printed sales invoice, you'd base your layout on the TicketNames table instead of lineItems. (Fields from LineItems could be listed in a sub summary part with fields from Invoices and Contacts added to header, footer, and/or grand summary parts.

                  Let me know what you think and what questions you still have at this point and we'll go forward from there.

                  • 6. Re: Table Occurrence help request
                    Tbear

                    I was thinking about adding a Note tab to the Event Invoice LineItem layout - that is sort of like one of your suggestions.

                    I only need the list of names included in the 'batch sale' for one reason and that is so I can get them to show up on the "Registration Guest List" report. Of course I could always just put them into an Excel file to print off name badges etc. but I'm learning so much about FM already that I thought I could come up with a slick way to do this. 

                    I will have to study your suggestions a little at a time - hopefully I'll be able to grasp what you're saying and come up with a half-way intelligent reply in a day or two! :)

                    As always, thanks for helping me work through this. 

                    • 7. Re: Table Occurrence help request
                      philmodjunk

                      Ok, that is definitely an issue and the text feld approach won't work here. Should have reviewed your first post last time I responded here.

                      You haven't discussed how or if you've set up a table for such a registered guest list or not, but the "detail" portal I am suggesting would be that exact registered guest list as it could store the two key pieces of data that will make that happen: the EventID and the guest's name.

                      You could use this relationship between Event Invoice and this table to make that happen automatically:

                      Event Invoice::gSelectedEventID = Guests::EventID AND
                      Event INvoice::InvoiceID = Guests::InvoiceID

                      A script trigger on your Event Invoice layout updates gSelectedEventID with the EventID of the current lineitems row in your line items portal and with Allow Creation of Records Via this Relationship enabled for Guests in this relationship, you can simply fill in the line item row, then enter names into the portal to Guests.

                      This then takes us to your second occurrence need. To see the guest list for a given event, you'll need this relationship:

                      Event::EventID = RegisteredGuests::EventID where RegisteredGuests is a second occurrence of Guests.

                      A portal to RegisteredGuests on the Event layout can list all such guests from all invoices where tickets to that event were sold and a list layout based on RegisteredGuests can be used to print out the list for any given event.

                      • 8. Re: Table Occurrence help request
                        Tbear

                        My brain if fried for today. I've never made a global field yet either so I'm going to try to take this one little step at a time. It looks like the first thing I need to do is create Guest table which I should relate to the Invoice table via EventID AND InvoiceID. Right? (I've never done a multi-criteria table relationship before either).

                        The Registered Guest List report was simple when I was only thinking 'one sale, one guest'. (I'm a little overwhelmed at the moment) :)

                        • 9. Re: Table Occurrence help request
                          philmodjunk

                          Looks like I've given you a whole list of new things to try out. Wink

                          Give it your best shot and don't hesitate to post back with questions if you can't figure things out.

                          • 10. Re: Table Occurrence help request
                            Tbear

                            OK now...

                            I've done a bunch more reading to understand Global Fields better and Table Occurrences somewhat better although I'm still weak on that concept. I have created the new EVENT GUEST table and the new "Event Attendance Roll" layout based on the new table. I have all the fields entered into the new table (Name, gSelectedEventID, EventID and EventInvoiceID). I will create the relationship between EVENT GUEST and EVENT INVOICE as you described next.

                            My question now is regarding placement of the portal(s). Do I put the new portal (looking at EVENT GUEST on the Event Invoice layout or the Event layout? or both? (obviously I already have an Event Line Item portal on my Event Invoice layout and I think I understand that it is that portal where I get to script the new global field with whatever event ID I want via that portal - right?).

                            The concept is coming into focus little by little. 

                            • 11. Re: Table Occurrence help request
                              philmodjunk

                              gSelectedEventID should be defined in Event Invoices, not Event Guest. You can't set up the relationship needed for the new portal if you don't.

                              Since you would record the names of guests at the time you create the invoice, I would assume that you would need this added portal on your Event Invoice layout and that's what I have described here.

                              You can use an OnObjectEnter trigger selected on the entire line item portal to perform this script step to bring up the guest list for that combination of Event Invoice ID and Event ID:

                              Set Field [Event Invoice::gSelectedEventID ; Event Line Items::Event ID]

                              Since you might not have selected an Event for this line item yet or may change it when filling out the invoice, use OnObjectSave set on the Event Line Items::Event ID field to perform the same script.

                              If you haven't already read this, here's more info on Table Occurences and how they control the results you get in FileMaker Pro:  Tutorial: What are Table Occurrences?

                              • 12. Re: Table Occurrence help request
                                Tbear

                                I did define gSelectedEventID in the Event Invoices table (I put the field for now on the Event Guest layout so I could see what, if anything, it would do there). Just trying to help myself understand the whole picture.

                                OK, I'll add the portal next (that's where I figured it should go but needed to be sure) - I already added a place for it on the Event Invoice Layout.

                                Then I will dive into setting up the scripting w/ trigger you defined. 

                                Thanks for the extra reading too - I'm going to start with that. :)

                                • 13. Re: Table Occurrence help request
                                  Tbear

                                  I think I've done everything now BUT: At first I couldn't create the "Set Field" script because I could not figure out how to do it as you wroted it unless I used 2 "Set Field" lines which did not work. Then I saw the calculation specify so I finally got this on one line:

                                  Set Field (Event Invoice::gSelectedEventID; Event Line Item::EventID]

                                  I set the triggers as you described on the Event Line Item portal first row and I was able to type in guest names in the new portal I placed on the Event Invoice layout which put those name records into the new Event Guest layout.

                                  I did something wrong though because ALL name records always show whichever Event Invoice ID and whichever Event ID I'm currently on. If I change to a different invoice then that number populates ALL name records.

                                  • 14. Re: Table Occurrence help request
                                    philmodjunk

                                    I don't see how that's possible. OPen up a table view of this table where you can check the eventID and Invoice ID fields to see what values are being entered. Cross check them with your values for an Invoice ID in Invoices and Event ID's in Events.

                                    Check the relationship to make sure that you have the correct field's matched to each other.

                                    1 2 3 Previous Next