Allocations, Expenses and Normalising Tables, Oh My

[MLink]

Hello all - had a look through the last few pages of this subsection and couldn't find anybody else with a similar problem. This relates somewhat to this thread.

Let us imagine that we have a table called PROJECTS, uniquely identified by the Project Code. There are two other tables - EXPENSES and ALLOCATIONS.

An Allocation represents a collection of similar expenses against a Project. eg, $400 is allocated to Fuel Costs. All Allocations MUST have a Project Code and a unique name.

An Expense is an expense against a Project. This may relate to a previous allocation or it may not. In other words, if a new Expense was created and tagged as "Fuel Costs" it would go against that Allocation. If it was not tagged with an Allocation it would just go against the Project as normal. All Expenses MUST have a Project Code and a date and amount and are uniquely identified by an Expense Number.

So basically:

An Allocation is a sum of related Expenses, but;

An Expense is not always related to an Allocation.

So to get around this many-to-many relationship I made a normalising table called ALLOCATEDEXPENSES. This matches an Expense Number, Allocation Name and Project Code together as a way of tracking what Expenses are part of Allocations.

I'm using two Portals on the PROJECTS form to show Allocations and Expenses against that Project.

The Allocations show up fine. I can see every Allocation which matches that Project Code and I can see that the Allocations are gathering information from the ALLOCATEDEXPENSES table and using that to check off expenses against that allocation.

The problem is that in the Expenses portal it is only showing Expenses that have been flagged in the ALLOCATEDEXPENSES table as being part of an allocation. Unallocated Expenses are not showing up even though they have been clearly given a Project Code and there is a direct relationship between the PROJECTS table and the EXPENSES table.

Somewhere behind the scenes it is obviously making a strange AND'ing query and only returning expenses that match the Project Code AND are in the Allocated Expenses table. But I don't want that, I want to show all Expenses related to a Project, regardless of if its been allocated or not.

Can anyone help me with this? I'm sure it's as simple as ticking a box or two on the relationship graph. I just don't know of any other examples of this to reference.

I've attached the .jpg relationship diagram and the database as a .zip archive. Thanks for reading that long description everyone, all comments and thoughts are appreciated.

Project_Database_Prototype_Public.zip

[MLink]

Huh. I just played around with it then by connecting the EXPENSES table directly to the PROJECTS table, and connecting the ALLOCATIONS table to the cloned PROJECTS table.

Now it finds all the Expenses relating to a Project regardless of if they're allocated or not, but it only finds Allocations that related to a Project if they have an Expense against them...

Why is it doing this? Why is the table that links to the cloned version of the PROJECTS table in the graph somehow crippled? And why can't I do a circular relationship like in every other database system.

Confusing to say the least.

[Søren Dyhr]

And why can't I do a circular relationship like in every other database system

Because the RG isn't an ERD, rush to re-read about it in the:

http://www.filemaker.com/downloads/pdf/techbrief_fm8_migrtn_found.pdf

But even better, this:

http://www.digfm.org/ref/FM7_key_concepts.pdf

What you should pay attention to in Harris words is the distinction between a table and a table occurance, then will the answer be straight forward - you should use enough TO's to facilitate a functionality!

--sd

[Vaughan]

Søren wrote: "you should use enough TO's to facilitate a functionality"

Ahhh, that's a quote for my best practices document. Nicely put Søren. :)

[Søren Dyhr]

Pure and simply nicked from Petrowsky's video, I can't take credit for it!!

--sd

[MLink]

Hmm, thanks for that link Søren - I understand a bit better now and have got it working though I am still having issues with the value lists. Cheers.

EDIT: Actually, I'm not having that problem anymore. Awesome!

Thanks all.

Edited September 5, 2007 by Guest