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...
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.
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.
I am using 11 and may consider that.
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).
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.
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.
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.
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.
categories 2 won't work without adding some more occurrences. Categories looks like it might work here though.
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?
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?
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.
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)