1 2 Previous Next 24 Replies Latest reply on Sep 30, 2011 2:11 PM by philmodjunk

    Need CALC for getting subtotal for Categories field

    brianquillin

      Title

      Need CALC for getting subtotal for Categories field

      Post

      I am tracking budget categories that have many budgetlines assigned to one category (ie. Under the Costume category, I will have Character/Actor Names as specific BudgetLines).  I will also have many purchases that will be assigned to those categories (the purchases will not necessarily be tied to the specific BudgetLines but rather to the overall Costume category.

      My budget categories are currently stored in a Value List (using Custom Values).

      I want to do two things:

      I want to Total all the BudgetLines assigned to a Category (meaning I want to obtain the total amount for Costumes, Props, etc).

      I also want to Total all the purchases made to a Category (meaning several purchases of fabric, etc. will be assigned to the Costume category).

      I am creating a portal that I want to show the Category Name and the Budgeted Amount (my first 'want') minus the Purchased Amount (my second 'want').

      ***My fear is that because I set up my Categories in a Value List using the custom values I may have made this task more difficult than necessary.  However, I'm having a tough time wrapping my brain around what step to take first.

      Thanks in advance...

        • 1. Re: Need CALC for getting subtotal for Categories field
          philmodjunk

          You might also look at setting up a summary report of expendetures and budget data grouped by category. This approach can work with either a single budget item, all budget items or a sub set of them. Thus, this can be a very flexible approach and one that uses the values you've entered from your value list so custom values or not it works the same.

          I am creating a portal that I want to show the Category Name and the Budgeted Amount (my first 'want') minus the Purchased Amount (my second 'want').

          Ok, but on what table occurrence will the layout for this portal be based? (That determines the relationship you need to use or create for you portal.)

          You'll need to tell us more about the design and purpose of that layout, before we can suggest much...

          • 2. Re: Need CALC for getting subtotal for Categories field
            brianquillin

            I do have a Budget Report setup and have the subsummary sections sorted by Categories.  That is working great.  I'm looking for a quick snapshot budget category status.  (Honestly, I'm trying to stay tied to my current layout which incorporates a left side vertical portal area and I'm trying to make functional use of the space.)

            The portal is on the Purchases Layout (based on Purchases) and the Portal is referencing the ShowBudgets Table.  Connected via:

            ShowBudgets::budgetline_ID --- < Purchases::budgetline_id

            (Seeing this now makes me question this relationship, I don't actually need a payment attached to a SPECIFIC BudgetLine but rather an overall Category - which I previously mentioned are stored in a Value List (using Custom Values).  

            Should I connect ShowBudgets to Purchases via the field that I'm using for the Budget Category fields (neither is a key field)?

            I still may not have provided enough information.  Let me know if I need to add more here.

            • 3. Re: Need CALC for getting subtotal for Categories field
              philmodjunk

              If you are using Filemaker 11, you might try using a filtered portal where a filter expression let's you select the Category for which you want to see this data.

              • 4. Re: Need CALC for getting subtotal for Categories field
                brianquillin

                I am using 11 and may consider that. 

                thanks...

                • 5. Re: Need CALC for getting subtotal for Categories field
                  brianquillin

                  I added a NEW Categories Table and all seems to be working properly (although the graph looks a little crazy).  Currently:

                  Shows::show_ID ---< Categories::show_id
                  Categories::category_ID ---< Purchases::category_id

                  (also, Shows::show_ID ---< ShowBudgets::show_id)  ShowBudgets is not directly connected to Categories except that the ShowBudgets::category_id is referencing the NEW Categories Value List which uses Categories::category_ID & category_name to display).

                  I'm almost where I need to be.  I just need to find the calculation expressions that give me the TOTAL budgeted amount for each Category (as assigned to a BudgetLine_ID) and the TOTAL purchased amount for each Category (as assigned to a purchase_ID).

                  (I was also able to acheive the portal list I wanted by using a Categories 2 TO connected to Purchases.  This is where I will use the calc fields to show how much is available to spend for each category).

                  • 6. Re: Need CALC for getting subtotal for Categories field
                    philmodjunk

                    I'm afraid my memory isn't good enough to recall the exact structure you've described in earlier posts. If BudgetLIne is a different table than Showbudgets, then you need to include a relationship to it in order to access the needed data for a sub total.

                    Does your portal list categories or purchases? I think you are listing categories, but if I understand your database design correctly, this may not work as you need to link to records by ShowID as well as by category unless we add an additional value in a field so that we can link only to records for a given combination of Category and ShowID.

                    • 7. Re: Need CALC for getting subtotal for Categories field
                      brianquillin

                      I have a Categories portal (showing Categories) on the Purchases layout (based on the Purchsases TO) that is working properly.  I want to create calculation fields that will total BudgetLines assigned to a Category and another field to total Purchases assigned to Category.  (I have some ideas to try later today but your guidance would be greatly appreciated).  

                      I would provide a copy of the db but I'm away from the server right now.  I'll provide a picture of the relational graph.

                      • 8. Re: Need CALC for getting subtotal for Categories field
                        philmodjunk

                        A Summary field from ShowBudgets that totals the field in ShowBudgets that you want totalled could be added to the rows of your portal to display a total for each category. The Sum Function can also be used in a calculation filed defined in Categories to compute the same total so you may want to try that option in a calculation that subtracts a similar total from purchases from the Show Budgets total for a given category.

                        • 9. Re: Need CALC for getting subtotal for Categories field
                          brianquillin

                          That was where I was headed.  Thanks.  I'll try that later this evening and will repost with results.

                          Also, I mistakenly indicated that the Categories portal was referencing Categories.  It should have said Categories 2 for that Portal.

                          • 10. Re: Need CALC for getting subtotal for Categories field
                            philmodjunk

                            categories 2 won't work without adding some more occurrences. Categories looks like it might work here though.

                            • 11. Re: Need CALC for getting subtotal for Categories field
                              brianquillin

                              The Categories 2 portal on Purchases Layout is currently showing each of the Categories like I wanted.  When I tried just Categories TO, it only showed ONE category.

                              I am working (off and on) with the calulations right now but those are fairly straight forward now that I know where to put them.

                              Are you saying that Categories 2 won't work for the calculations?  Any more guidance on this before I run into a wall trying to make Categories 2 work?

                              • 12. Re: Need CALC for getting subtotal for Categories field
                                brianquillin

                                I realized I hadn't mentioned that I need the Calculations based on the Categories assigned SHOW.  I had planned on using the same Shows::show_status = "current".  Do you have any thoughts on this?  

                                • 13. Re: Need CALC for getting subtotal for Categories field
                                  philmodjunk

                                  I should have noticed the difference in operators.

                                  From Categories 2, you have only one relational link, to purchases. You'll need to add the occurences needed to link to the tables where you have data you want to total.

                                  • 14. Re: Need CALC for getting subtotal for Categories field
                                    brianquillin

                                    Sorry for the confusion... Will you take another look here?  I added the ShowBudgets 3 (linked to Categories 2 which is showing me the correct list of Categories in my portal) but I'm not making a connection to your last post.

                                    Here's what I'm hoping to accomplish in the left hand vertical portal (on my Purchases Layout):

                                    (Category Name)          (Remaining Budgeted Funds)

                                    Costumes                     $5,000
                                    Props                          -$259.87
                                    Orchestra                     $234.44

                                    1 2 Previous Next