Intersections

[eric.p]

I can't find a satisfactory way to arrive at an intersection of two sets of records, related to the source record via different relationship paths.

I've been developing for a little over a year; it's something I keep running into. I've attached a (very) simplified file showing the latest instance of it, in an simple accounting database.

and here's an outline of the basic table/field structure therein:

accounts

-ID

-name

transactions

-ID

-date

transaction line items (TLIs)

-ID

-account ID

-transaction ID

-amount

Now let's say I want to put a calc field in my transaction line items records that shows the resulting balance of the account that the transaction line item affects. To do this, I need to sum all of the transaction line items previous to this one, which also apply to this account.

The problem is that creating a relationship that limits by the date of the TLI requires a trip through the TLI's transaction, where that date is stored, but that transaction knows nothing about any of it's TLIs' accounts.

Now, getting previous TLIs that apply to all accounts, and getting all TLIs ever for just this account are fairly straightforward:

all previous TLIs.

TLI's_transaction's_previous transactions'_TLIs

all TLIs to this account

TLI's_account's_TLIs

So: how to intersect these sets of records?

My go-to solution thusfar has been, in the TLI, to set up a pair of calc fields that use the List() function to list all of the IDs for each of these sets of records.

calc:allPreviousTLIs

List(TLI's_transaction's_previous transactions'_TLIs::ID)

calc:allAccountTLIs

List(TLI's_account's_TLIs::ID)

and then set up another relationship, from these (potentially huge) calculated fields to another TLI table occurrence, wherein the TLI ID has to match both listed ID mutli-key calc fields, making sure it's in both lists. Let's call this TLI occurrence:

TLI's_TLIs#previousToThisAccount

and then you create the desired calc field as:

Sum(TLI's_TLIs#previousToThisAccount) + amount

And boom. You're done.

This works. But, when working with thousands of records, its awfully slow, and even with just a few records to list and sum, the resulting calc field (relying on a crufty and rickety seeming set of other calc fields and multi criteria relationships and multi-key match fields as it does) updates with infuriating inconsistency. If the underlying data changes (say, if we change the TLI's account), the only repeatable-with-certianty way I've found to get the calc field to re-update is to close and reopen the database.

So: is there a better way? There must be. Value lists? Custom calculations? Triggered scripted finds?

example.fp7.zip

[Raybaudi]

What about relating the "accounts" table ?

[eric.p]

how exactly? can you be more specific?

also I feel like I should note that while there are probably lots of less than ideal things about this particular example,and I'm all ears for ways to improve those (for instance, it occurs to me that there should be a way to just look at the previous TLI's resulting balance and add our amount to that rather than looking at the whole history of every TLI every time), my primary concern is the more general problem of getting an intersection of a couple of different related sets.

thanks,

—eric