Percentages of Line Items in Portal

[byty]

I am creating a monthly budget database in Filemaker 10 Advanced. I'm having a but of trouble translating my imagination into filemaker and my googling skills have reached their upper limit.

I want the user to be able to see a percentage breakdown of all expenses by category that are listed in the line items portal.

I have three tables:

- Line Items, which has all incomes and expenses with the fields date, monthyear (which is the monthname and year) income_expense, category, and amount.

- Categories, which has category name, category ID, income, expense, sumamount, and sumpercent

- Main, has one record and is meant to be a display of all the data together. It has all global fields, they are monthfilter, yearfilter, monthyearfilter, income, and expense.

There are two additional instances of Line Item to separate the incomes and expenses. The relationships are:

Line Item::monthyear = Main::monthyearfilter

Line Item expense::income_expense = Main::expense AND Line Item expense::monthyear = Main::monthyearfilter

Line Item income::income_expense = Main::income AND Line Item income::monthyear = Main::monthyearfilter

Categories::category = Line Item::category AND Categories::income_expense = Line Item::income_expense

The Main layout has the following portals:

- Line Item income - shows all the income line items

- Line Item expense - shows all the expense line items

- Line Item expense - where I want to show the category percentages (and maybe totals) for the expense line items

So, what I tried to do was create a couple of fields in Line Items, sumamountexpense, which summed up all the expenses, and sumamountexpensepercent, which was supposed to create a fraction of the total. These work fine in a separate report layout. But, I need to show the percentages of the total amount spent on each category in the main layout.

The closest I've gotten is to create another instance of Categories (categoriespercent) with the relationship Categories::category = Line Item::category AND Categories::categoryid = Line Item::categoryid. In this portal, I put line item expense::category and Line Item expense::sumamountexpensepercent. It looked right at first, but reveals its flaw when there are more than one items with the same category name and id. It lists that category as many times as it is listed in the Line Item expenses portal.

I've omitted a bunch of extra info about the database to shorten this post. If anyone could give me any tips, I would appreciate it very much. I'm stumped!

Thank you,

byty