1 2 Previous Next 16 Replies Latest reply on Nov 28, 2011 7:06 PM by MikeF

    Table relationship and summary problems

    MikeF

      Title

      Table relationship and summary problems

      Post

       

      Table relationships in an event mgmt system …

      tblProjects  ---  __kp_ProjectID .    

      tblDeals ---          __kp_DealID,  _kf_ProjectID [related to kp_ProjectID above].  Multiple deals per Project.

      tblEvents ---       __kp_EventID,  _kf_DealID [related to kp_DealID above],  _kf_CompanyID [venue where Event takes place].     Multiple events per Deal.

       

      tblEventBoxOffice ---     __kp_EventBoxOfficeID [uncertain if I need this], _kf_EventID [related to tblEvents:: EventID]

      This table has one to five ticket prices, and their relevant capacities and tickets sold [as records] per EventID.   Relevant fields, all numbers --- TicketPrice, Capacity, TicketsSold.  [There are other calculated fields that accommodate Price * TicketsSold, etc.]

       

      tblEventExpenses ---   __kp_EventExpenseID [uncertain if I need this], _kf_EventID  [related to tblEvents:: EventID]  

      This table has numerous expenses per EventID. Each has a category – ie advertising, insurance, etc.

      Relevant fields ---  Category, Description [<both text] FlatRate, VarPerHead [<both number] and Amount [calculated --- FlatRate + Sum(tblBoxOffice::TicketsSold) * VarPerHead .   This is *not* working ].

       

      Projects and Deals are working correctly.  Have run into roadblocks with the Event components.

       

      Have an EventDetail layout where …

      tblEventID:: EventID & DealID fields are up top.

      Portals to tblEventBoxOffice, showing each event’s prices/etc  *and* tblEventExpenses showing each event’s expense & category lineitems make up the rest of the main body.

      *Have shortened the table names below for ease of use. They are in reality exactly as above.

      1. Need to sum tblBoxOffice and tblExpense records by EventID on the EventDetail layout. Do I need to create related summary tables to do this?
      2. A curveball  --- Some expenses are variable, such as insurance which is calculated as say, $1 for each ticket sold. So a calculated field named Amount in tblExpenses needs to have the formula tblExpense::FlatRate  + sum(tblBoxOffice::TicketsSold) *tblExpenses:: VarPerHead  working properly.  Let me know if this is clear enough.
      3. Need to have every record from tblEvents::__kp_EventID show up automatically in the BoxOffice and Expense tables.  Have tried AutoEnter Looked Up Value, but the fields don’t populate.  Any ideas as to what I’m doing wrong?  Are script triggers the solution?
      4. Further down the road, need to have the ability to copy an entire Deal – containing tblDeals:DealID and *every* relevant record from the tblEvents::  schema, into new  records in the relevant tables.  Believe this one can be put aside for now, just thinking ahead and don’t want to back into a corner that can’t be reversed.  Have already done a few dozen of those …!!

       

      Wrestled with numerous relationships/ layouts and have hit a wall where some help is necessary.

      Extreme and sincere thanks in advance for any assistance with the above. 

       Regards

      -          Mike

        • 1. Re: Table relationship and summary problems
          philmodjunk

          Does this look like what you have in Manage | Database | Relationships?

          tblProjects---<tblDeals----<tblEvents--<tblEventBoxOffice
                                                              |
                                                              ^
                                               tblEventExpenses

          TblEvents::__kp_EventID = tblEventBoxOfficce::_kf_EventID
          TblEvents::__kp_EventID = tblEventExpenses::_kf_EventID

          It sounds like tblEventBoxOffice is set up with one record for each ticket category for a given event and TicketsSold is a number field reporting the number of tickets sold for that category. Is this correct?

          1 & 2) If the above is correct, I'm not sure why your Calculation with Sum isn't correctly computing the total tickets sold for a given event. As a test, you might try putting a portal to TblEventBoxOffice on a tblEventExpenses layout and check to see if all the TblEventBoxOffice records for a given event appear in this portal. You do not need a summary table as far as I can see.

          3) Not sure what you want here. Events is the parent table of these two related child tables. Any record in BoxOffice and Expenses should automatically be linked to an Event record and data from that event record should be directly accessible simply by placing the field defined in tblEvent on the layout based on tblEventBoxOffice or tblEventExpenses

          4) there's a way to script this. Get the above issues ironed out, then look at the script in this thread: Duplicating Bill Of Materials (duplicating portal line items)

          To sum up, what you report suggests you may have issues with your relationships or the data present in your key fields that should be linking records via relationship but are not. Check to see if your relationships in Manage | database | relationships match what I've put at the start of this post, and if they match, then check the data in your __kp and _kf fields to see if they truly store matching values. Also, make sure that they are the same data type as this can be an issue as well.

           

          • 2. Re: Table relationship and summary problems
            MikeF

            Phil,

            Thanks for the reply.

             Ok, #1 first...

            " TblEvents::__kp_EventID = tblEventBoxOfficce::_kf_EventID

            TblEvents::__kp_EventID = tblEventExpenses::_kf_EventID

            It sounds like tblEventBoxOffice is set up with one record for each ticket category for a given event and TicketsSold is a number field reporting the number of tickets sold for that category. Is this correct? "

            >>> Yes, example below from tblEventsBoxOffice...

            EventID CompanyID Price Capacity
            1 1053          65.00            5,075
            2 1054          59.50            4,000
            3 1055          65.00            4,500
            3 1055          45.00              500
            4 1056          85.00              400
            4 1056          75.00            1,200
            4 1056          65.00              200

             

            >>>The EventDetail layout for this part looks like ...

             tblEventID::EventID :  1

            Portal on tblBoxOffice...

            1 1053          65.00            5,075

                                                                                              Need Sum Here [5075]

             

            Next Screen [ctrl+down arrow]

             tblEventID::EventID :  2

            2 1054          59.50            4,000

                                                                                              Need Sum Here [4000]

             Next Screen [ctrl+down arrow]

            tblEventID::EventID :  3

            3 1055          65.00            4,500
            3 1055          45.00              500

                                                                                           Need Sum Here [5000]

            Next Screen [ctrl+down arrow]

            tblEventID::EventID :  4

            4 1056          85.00              400
            4 1056          75.00            1,200
            4 1056          65.00              200

                                                                                             Need Sum Here [1800]

             

            ... And so on.

            So where  exactly would I put the "Need Sum Here" field ??

            Do I have to create a field in a [separate] summary table??

            Note 1 - will need it for a few more fields in this portal.

            Note 2 - the Expense categories will go below the "Need Sum Here" field to be deducted from the sum of tblBoxOffice::Gross Revenue [field not shown here] and constitute the event's net revenue.  Will get to that after the BoxOffice is resolved.

            Thank you sincerely for working thru this with me.

             - Mike

             

             

             

             

             

             

             

            • 3. Re: Table relationship and summary problems
              philmodjunk

              There are two methods you can use to get the sum of the records showing in the portal.

              1) Define a summary field as the total of Capcity the tblBoxOffice. Put this field on your EventDetail layout.

              2) Define a calculation field in tblEvents with Sum ( tblBoxOffice::Capacity ) to compute the total of all related records in tblBoxOffice. If you have more than one table occurrence of the events table, be careful to select the correct table occurrence in the "context" drop down at the top of the Specify calculation dialog.

              The summary field is usually the best option if you only need to display a total from records that will not be edited on your layout. It also can be used inside a filtered portal to summarize only the records currently matching the filter expression's criteria where the calculation field bypasses any such portal filter in computing a total.

              In cases where you intend to edit fields that are part of what the summary field is totalling up, a calculation field with the Sum function normally updates much more smoothly than the summary field.

              • 4. Re: Table relationship and summary problems
                MikeF

                 

                "1) Define a summary field as the total of Capcity the tblBoxOffice. Put this field on your EventDetail layout."

                       I know this sounds strange, but how exactly do I define a Summary field on the layout?  Doesn't it have to be in a table first??

                *Am starting to miss queries in Access again..!

                 

                "2) Define a calculation field in tblEvents with Sum ( tblBoxOffice::Capacity ) to compute the total of all related records in tblBoxOffice. "

                      I could do that, but am trying to keep tblEvents "clean".  Perhaps that's simply not the best idea ..!!

                 

                Definitely will be editing in the portal.

                 

                Thanks again Phil.

                  - Mike

                 

                 

                 

                • 5. Re: Table relationship and summary problems
                  philmodjunk

                  1) Yes, it should be defined in the tblBoxOffice table.

                  2) Keeiping it "clean" or at least "cleaner" is one reason for using the summary field instead of a calculation field as the summary field can be used in a variety of different ways. That said, it's pretty hard to get Filemaker to do what you want without having at least some calculation fields defined in your tables along with the data tables. In Access, you'd define the functional equivalent of these calculation fields either as data source expressions for text box objects on your form or as part of the SQL query serving as its record source.

                  There are advantages and disadvantages to each approach (Access vs. FileMaker). One advantage to the calculation field is that a field so defined is accessible from all layouts, scripts, etc that need to access or display that value. Should a change in the calculation be required, you need only make one update  in the table where with some Access systems that I've worked with, you'd have to track down multiple places where the calculation is used on different forms or in different queries in order to make identical updates to each...

                  Definitely will be editing in the portal.

                  You can get the summary field to update more smoothly with OnObjectExit or OnObjectSave script triggers that perform scripts using commit record and Refresh Window Whenever you edit a field that modifies the total shown in a summary field. This can create a bit of "screen flash" along with the need for careful scripting--which is why I usually prefer the calculation field option if such edits will be needed on the layout.

                  • 6. Re: Table relationship and summary problems
                    MikeF

                    Ok, got it.

                    Have set up an EventSummary table --- _kp_EventID related to tblEvents::EventID.

                    All the rest of the fields either sum BoxOffice or Expense fields, or calculate on fields within the table.

                    So ... that's #1 from above done.  Or it w

                     

                    Re #2, the "curveball" in Expenses.  For some reason it's working today where it wasn't yesterday. Uncertain what happened, but ok.

                     

                    So ... #3 is now even more important now, with the EventSummary table.

                    To clarify, whenever I add a record in tblEvents, thereby triggering an EventID, need to have that, and indeed each, EventID in tblEventID automatically add itself to three tables ... Event BoxOffice, Expenses, & Summary.  Again, have used Auto Enter Looked Up Value in each of those tables' EventID fields, expecting them to auto-populate.  To no avail ..!  How can this --- "auto-population" of EventID --- be accomplished??

                    Thanks,

                     - Mike

                     

                     

                    • 7. Re: Table relationship and summary problems
                      philmodjunk

                      Why do you need it? Most of the time, you can get by with adding related records only "as needed" rather than automatically adding one each time a parent record is created.

                      If you have enabled "allow creation of records via this relationship" for the child record, you can add related child recods as needed just by entering data in the bottom blank row of the portal.

                      If you want to create a child record for the current parent record and when starting the script from a layout based on that parent record, use this script:

                      Set Variable [$ID ; value: ParentTable::__pk_PrimaryKeyfield]
                      Freeze window
                      Go To Layout [//Layout for child table]
                      New Record/request
                      Set Field [ChildTable::_fk_ForeignKeyField ; $ID ]

                      • 8. Re: Table relationship and summary problems
                        MikeF

                         

                         In looking closer, believe this only matters for the EventSummary table.

                         

                        But, having an issue w/the EventExpenses table.

                        There are expense categories, ie Advertising / Insurance / EquipRental and a few more, that need to be summed.

                        The easy analogy is a SUMIFS for each category in Excel.

                        Tried numerous versions of If ( Category = Advertising, Sum ( tblExpenses: Amount) ) , but it merely sums the entire amount as opposed to just Advertising.

                        How can this be accomplished?

                        Thanks,

                         - Mike

                        • 9. Re: Table relationship and summary problems
                          philmodjunk

                          Think through how the If function works. It will control whether or not the Sum Funtion will sum up any related records, it has no effect on HOW they are summed up. Sum ( RelatedTableOccurrence::Field ) will sum up all related records. You'd need to include the category in the relationship before you could use Sum in this fashion to get your category based subtotals. That can work, but hardwires you to a specific set of categories and usually requires design level changes if you need to change/add/remove a category.

                          One simple way to get sub totals is to use a summary field in your expenses table to total the expense amount field, sort your records by Expense category and then you put the total in a sub summary part "when sorted by" this same Expense category field. If you need to use the same subtotal in a calculation, you can use the GetSummary function.

                          There are also ways to use a summary field with filtered portals to get subtotals for each category to appear in a layout based on a different table than expenses.

                          • 10. Re: Table relationship and summary problems
                            MikeF

                             

                            Have been looking online, and the lack of an easy SUMIF function appears to be a significant omission in FM.  Hopefully they're working on adding it soon.

                             

                            " You'd need to include the category in the relationship before you could use Sum in this fashion to get your category based subtotals. That can work, but hardwires you to a specific set of categories and usually requires design level changes if you need to change/add/remove a category. "

                            My categories are fairly solid, so how exactly would one do this?

                            Note - the categories are all in a table -- ExpCats -- already related to the Expenses table.  Indeed they're on a drop-down [value] list, ExpCatID's  1 thru 8.

                            Thanks,

                             Mike

                            • 11. Re: Table relationship and summary problems
                              philmodjunk

                              I've never felt the lack of such a function as there are alternatives that are more flexible.

                              On what table occurrence is this relationship based? tblEventExpenses?

                              • 12. Re: Table relationship and summary problems
                                MikeF

                                 

                                 Yes, tblEventExpense contains a numeric field CategoryID that one of 8 categories drops into via a drop-down list / value list.

                                • 13. Re: Table relationship and summary problems
                                  philmodjunk

                                  Yes, but is tblEventExpenses the table occurrence on which your layout where you need to see totals by category is based? if so, One simple way to get sub totals is to use a summary field in your expenses table to total the expense amount field, sort your records by Expense category and then you put the total in a sub summary part "when sorted by" this same Expense category field. If you need to use the same subtotal in a calculation, you can use the GetSummary function.

                                  • 14. Re: Table relationship and summary problems
                                    MikeF

                                     

                                    Ok, am back on this as something doesn't make sense.

                                    This calculated field provides the correct result:

                                     Case ( Deals_EventExpenses_EXPENSECATEGORIES::ExpCatID = 1; Amount)

                                     

                                    However, there are 8 categories and all the other numbers, ie  = 2  thru  = 8  provide nothing.

                                    Why is that?

                                     

                                    Thanks,

                                      Mike

                                    1 2 Previous Next