Hello, Debby. Welcome.
I don't 100% understand what you're working with, but it sounds like you want to use the results of a summary field as part of a calculation. Check out this function:
Thanks for responding so quickly Mike.
I attempted the GetSummary and included the Amount spent as my summary field and Category as my breakfield but that is not showing the individual summaries for each of the categories. Suggestions on what I am missing. ??
Did you sort the records by that field first? It doesn't work if you don't.
Yes, I sorted by the category and on the table view I see a subtotal for each of the categories, but can't seem to get that subtotal to populate in a field of its own.
Yes it does.
Debby - what exactly did you do, and what was the result?
Okay, where is this field?
What kind of field is it? How are you populating it?
What exactly is the syntax of your calculation?
So the layout is based on the Expense table and the highlighted fields are supposed to be the subtotals of each of the categories from the sub-summary below.
The Account Budgets are in a related table in the portal with beginning budgets for each category, (will be one for each category)
The syntax looks like this for each of the expense totals...
Clear as mud???
Okay, so you want something like a crosstab report. That's a little more involved. You won't be able to use a simple summary field for this, because your "categories" are essentially fixed. There's not a straightforward way to associate the categories with the separate fields you have.
There are a couple of ways to accomplish it. The easiest is probably to set up a series of relationships from the table where you are to the table where the expense items live (which looks like it might be the same table). Each relationship is to be based on a constant field equal to a given category (SALARY, FRINGE, WAGES, etc.). Then you can simply display the summary field you already have underneath each category. (I'm assuming the summary field is in the expense item table here.) Naturally, this has the significant disadvantage of having hard-coded something that should live in data (i.e., the categories).
Another method involves, instead of using summary fields, a hard number field in the categories table. From there, you update it via scripting when something changes that affects the total (deleting a value, creating a value, changing a value). Then you simply display that number field under its corresponding category. Again, separate relationships are needed, but this does have the advantage that it's indexable and therefore will perform much better.
The most complex, but most flexible and scalable, alternative is to script the population of your header area. Set a series of global fields equal to the categories, and then populate the number fields as well. This again has to be updated whenever a change is made that affects the total, but it avoids duplicating relationships and is a lighter data load on the system.
I hope that makes sense. Please ask any questions.
Is the Monetary Amount Total a summary type field?
It needs to be in order for this to work.
Mike asked you the right question: what kind of field is it?
Thanks Mike. I will need to work on sorting through the alternatives. Brain is a bit fried. Will probably be in touch!
Yes, the Monetary Amount Total is a summary type = Total of Monetary Amount (with sort of Category)