I'm not sure if a Portal is what I need here as I have never actually used one...

Basically I am making my own money management system, what I'm stuck on is how to handle transactions with multiple categories...

Each transaction will have it's own record in table or list view (might do form view if it will work) but some transactions may have multiple categories, so I would almost need multiple records within a record with a calculation to add up the total...

I will have no way of knowing how many different categories might be on any 1 transaction, and most transactions will only have 1 category...

Help!

Portals are used to display related records. Related records are used to store multiple values of similar information. I'd suggest that using related records to attach multiple categories to a transaction might be a good choice.

However, why can a transaction have multiple categories? Consider the impact this will have on reporting.

Say one $50 transaction is categorised as "business" and "entertainment". When doing the reporting, how should this $50 be displayed? If it is listed as both business and entertainment the $50 will appear twice and thus be counted twice in the total.

If the amount should be split between the categories then an interface is required to enter the split amounts. This will get complicated to build and it will make data entry harder and more error prone.

So the question may really be: should a transaction be allowed to have more than one category, and if so how should the amount be allocated for reporting.

To carry my example a bit further, perhaps there should have been a category called "business entertainment". However be careful with the number of categories allowed. The aim is to group similar objects, however I've seen systems where the number of categories expanded to the point where each item had a category to itself.

but some transactions may have multiple categories

Come on Vaughan, this is pretty straight forward book keeping via a ledger, some postings have up to several parts it is composed of ... usually sales taxes of various kinds. This means that the most straight forward way to handle it is to split it via a series of records.

Unfortunately is this something requiring either event triggers to maintain properly or a slight violation of the normalization.

--sd

Hi Soren, I haven't done much work with accounting systems and that must show.

Would you agree that using related records to attach multiple categories to a transaction might be a good choice? -)

Indeed, hence the use of the word "series" but your suggestion could imply a recursive structure, I would instead suggest that people in this topic study, looks at what Todd Geist does to one of the included templates:

http://www.geistinteractive.com/content/inventory-transactions

The jumping issue here is if this should be performed in the ledger, and then be split out in components later on the commitment to the ledgers entered values ... there are often an urge to see these figures right away ... to spot a flaw in the entered!

--sd

I think what you may be wanting is to add a journal table that issues transactions to your ledger.

Here's a description:

http://accountinginfo.com/study/je/je-01.htm

The journal would be a separate table.

Unfortunately is this something requiring either event triggers to maintain properly or a slight violation of the normalization.

Why is that?

Do you have other ways to produce an arbitrary number of records depending on the choice of account. I would probably use a repeating calc'field and then let it stuff each repeating with appropriate values, and then make the required records via import, between tables, splitting each repetition into individual records.

But I would gladly learn about arbitrary recursions depth?

There should be only one table holding all transactions shouldn't there?

There is no simple way to get this live, this journal to ledger conversion is there?

--sd

I think we need a better definition of the problem. It seems we have Transactions and each transaction can have a variable number of TransactionItems?

If so, does each item have an Amount attribute (total of amounts being the transaction amount)?

And if so, how is this "split" determined?

True we do not know enough here, but usually is it when salestaxing is on board, different percentages whether it's considered a luxury, food or lasting commodity (I'm unaware what it's termed in english here!) often have each it's own fixed taxation percentage.

--sd

Wouldn't each item be a single "transaction" with a tax rate of its own?

I would if you do it by hand, but todays standard seems to encourage "type ahead"'ish features to prevent blunders. So in the journal you simply enter a type, and in the migration to the ledger it morphs into genuine records. Take a thing like chocolate, it might have certain percentage of straight forward sales tax and then another for being considered a luxury, and if it's bought say outside EU the handling shipping to inside EU should be taxed as well.

--sd

That's why I asked how is the split determined. If it's calculated from known criteria, then I can see why you would want to script this. Not sure that's the case here, though.

Wow, thank you for the great response guys!

To clarify, using the the previous example, say I have a transaction (record) that will have a total of $50, but $10 went to entertainment, $30 went to business and $10 went to tax.

So somehow I would need to add multiple categories with an attached amount to the transaction...

So there would be a total of the whole transaction and then an amount for each category...in the reporting, the categories would reflect their respective amounts...

If you are the one making the call how much to allocate to each category, then yes: it would be best to use a portal to create three (in your example) records in a related table.

Seems like there would actually be three tables here:

Transactions -< TXitems >- Categories

so that for each item you would only select the category and enter the amount.

PERFECT! I was able to make it work (In limited testing capabilities with no calculations...)

Although, I only used two tables, I couldn't figure out why there needed to be three? I put the categories in with the TXItems and related the two tables with a Transaction ID...Is there an advantage to using three tables as you described?

Well, the third table is basically just a value list of categories. However, it can have some advantages:

• Storing a CategoryID only allows you to change the category's label globally;

• Each category record can sum its own transactions for a quick on-screen summary.