Transaction Symmetry

[FestiveEmbalmer]

Ok, this one is a doozy.

As described in my background post I am developing a new app that among other things, must do financial accounting.

The new solution needs to keep track of budget items (or accounts)as well as banking transactions. Which means double entry transactions.

If a client brings in a utility bill it needs to be entered as a transaction under the utility account for that client. A payment from their bank account is going to be another transaction record but under their bank account. The payment will be a debit from the bank account but a credit to the expense/bill/creditor account.

I can't think of a way to make that payment transaction one record. If I can't do it than I can't do it, but we write 15-20,000 checks a month and having two records...well, that doubles the growth of the database which means its going to be "double-slower".

I was thinking about having separate keys for the origin and destination of a transaction but that would not allow me to view both deposits and withdrawals for an account in the same place.

I threw together a test where I have match fields in the Transactions table for the account being credited (_kf_AccountID_credit) and the account being debited (_kf_AccountID_debit). I created a field to enter the amount (later i will be creating records by setting this field from a user accessible global) that sets itself to its absolute value. I then created another field (Trans_Num_bysource) wherein I attempt to display a positive amount if being viewed from the account being credited and a negative if being viewed from the debited account. This calc is resulting in a negative value.

Perhaps I am just inexperienced in when calculations evaluate or (most likely) not understanding the context properly. Also, I could be trying to romance FM into doing something it is simply too classy to do with a guy like me!

Critiques, alternatives?

Matthew

TranSymmetry.zip

[Matthew F]

I suggest deleting all of the extraneous fields and calculations in your test database. Keep only the match fields and data fields that are relevant to your problem. By simplifying everything you might figure out what works and what is broken. It would also make it a lot easier for others to help out. Try telling us exactly what you're trying to do (e.g. "I enter $100 in field X but it doesnt show up in field Y....")

[FestiveEmbalmer]

You are absolutely right. I have removed all superfluous fields, tightened the layouts up for quicker perusal, and added notations that work through my logic.

Doing that helped me to better understand what isn't working, but not why.

It has to be my calculation. I just don't know if its the relational context of it or the calc (un)logic. I am very very new to these issues.

In short, if someone else has a better theory how to create a field in a record that allows a value to be different based on which table occurrence it is being viewed from, I would be eternally grateful. I realize that this is essentially trying to create two parent records through a multikey.

Using multikeys is in itself new territory and I could use some direction/resources on their proper use.

thanks,

Matt

Edited November 15, 2007 by Guest

[FestiveEmbalmer]

I am not going to type "HELP!" oops.

Seriously though, any direction at all would be greatly appreciated. It's not just a matter of technique, this is principle that I don't think I comprehend properly.

with intellectual prostration,

Matthew

TranSymmetry02a.zip

[Matthew F]

I'm sorry to see that my post yesterday disappeared somewhere in cyberspace. Here is goes again...

Trans_amount_bysource is the calculation... It is displaying a constant value regardless of the account it is viewed from.

It seems as though you're trying to get this calculated field to simultaneously display two different values. One value would appear if the calculated field is being viewed from the 'Account_Me' table from a related 'credit' type record. The negative value would be displayed if you were to simultaneously open another window of the same 'Account_Me' layout but browsed the related 'debit' type record.

I don't think Filemaker works this way. When the calculation says that it is being evaluated from the context of the 'Account_Me' table this refers to the relationship graph not the layout that you currently happen to be viewing. Imagine the confusion that would develop if calculations results would vary according to where, or by whom, they were being viewed, without any changes in the primary data.

The value displayed in the 'Trans_Amount_bysource' field is a function of the sort order for Transactions_Me::Account_Me as set in the relationship graph. Attached is your solution with a new portal and field added to the Transactions table which I think illustrates this point. Change the sort order in the relationship graph and you'll see the value change from positive to negative.

Hope that helps...

TranSymmetry02a.fp7.zip

[Matthew F]

On second thought calculations based on 'status' functions, like Get (LayoutName), vary according to the context in which they are being viewed but I don't think your relationships work this way.

[comment]

I don't think this can work, at least not the way you imagine.

A child of two parents cannot know which parent is currently looking at it. A calculation field in the child table referencing a parent record, is ALWAYS going to be evaluated using values from the FIRST related parent record.

There are many ways to do this, but I believe the only way you can get a "clean" chronological listing of all accounts transactions - in a portal or in a list view - with a running total, is by creating two separate records for each transaction.

Alternatively, you could have a recursive calculation in the parent table, looking at each transaction in turn and picking the correct values according to on which "side" the parent is. This could be viewed in a single text field or in a bunch of repeating fields (with scripted "scrolling").

However, the simplest solution would be to have two portals, side-by-side, on the Account layout.

[FestiveEmbalmer]

Mfero, Comment:

Thank you so much for your help. I value your input and time immensely and I am determined to squeeze every Util I possibly can out of your advice.

It seems as though you're trying to get this calculated field to simultaneously display two different values. One value would appear if the calculated field is being viewed from the 'Account_Me' table from a related 'credit' type record. The negative value would be displayed if you were to simultaneously open another window of the same 'Account_Me' layout but browsed the related 'debit' type record.

That is exactly what I was going for- Except I was also hoping to be able to view different sum values in the same window through a portal on a grandparent TO layout.

I don't think Filemaker works this way. When the calculation says that it is being evaluated from the context of the 'Account_Me' table this refers to the relationship graph not the layout that you currently happen to be viewing.

I understand but I do not comprehend! I understand that there is a difference between the calculation context and the context of the viewer, which is precisely why I suspected that my calc might work. You seem to understand my reasoning but I am going to baby step through it so the faulty part can become more apparent (hopefully, to myself).

On a TRANSACTION record the user enters a value in the Transaction Amount field which through an Auto-Enter calc is rendered to its absolute and therefore positive value. There are two foreign key fields, one for the credit/deposit/positive side of the transaction and one for the the debit/withdrawal/negative side. The user inputs ACCOUNT primary keys. A calculation field (Trans_Amount_bysource), checks to see if the active ACCOUNT record-- [which I assume is by definition the record being viewed by the user] -- ...if the active ACCOUNT record's serial key matches the debit or the credit key field on the TRANSACTION record and then adopts the absolute value amount of the Transaction, positive or negative according to which field is matched.

Reading my own drivel, I can see several places where my assumptions could be incorrect, ruining my life forever - or at least this weekend.

I'm just not sure which one(s) are wrong.

Imagine the confusion that would develop if calculations results would vary according to where, or by whom, they were being viewed, without any changes in the primary data.

The value displayed in the 'Trans_Amount_bysource' field is a function of the sort order for Transactions_Me::Account_Me as set in the relationship graph. Attached is your solution with a new portal and field added to the Transactions table which I think illustrates this point. Change the sort order in the relationship graph and you'll see the value change from positive to negative.

[emphasis added - FE]

So if I am understanding this correctly, there are two flaws:

• The calculation evaluates only when related data has changed and changing the User Context/View/Layout does not trigger an evaluation, unless there is a Get function related to the change.
• The calculation can evaluate a field in a related record but if there are multiple related records (even if it is a parent record through a multi line key) then the value returned is the first one- based on the sort order of the relationship.

Hope that helps...

understatement of the year, friend.

A child of two parents cannot know which parent is currently looking at it. A calculation field in the child table referencing a parent record, is ALWAYS going to be evaluated using values from the FIRST related parent record.

That is going straight into my sacred texts.

There are many ways to do this, but I believe the only way you can get a "clean" chronological listing of all accounts transactions - in a portal or in a list view - with a running total, is by creating two separate records for each transaction.

It seems that would be prudent. I am just trying to prolong the lifespan of the solution. The difference between 15-20k and 30-40k records per month over the course of years seems...hm...worthy of consideration. I wonder if there is a clean way of auto-archiving them into an identical database every few years where the records would still be available but not affecting the speed of the current database.

Alternatively, you could have a recursive calculation in the parent table, looking at each transaction in turn and picking the correct values according to on which "side" the parent is. This could be viewed in a single text field or in a bunch of repeating fields (with scripted "scrolling").

I am intrigued and frightened. Repeating fields? I thought they ate bad little newbies who don't lurk FMForums enough.

Could you post an example, if you have time?

However, the simplest solution would be to have two portals, side-by-side, on the Account layout.

I considered that, and there will be a layout where the debits and credits need to be segregate, but for the register functionality I will need them together (think Quicken).

I am going to try a few more ideas before I give into twin-record transactions. I am still open to alternative methods for achieving dual transaction records.

Muchas gracias,

Matthew

[David Jondreau]

Look into Conditional formatting. That allow you to set the text color to Red or Black depending, though you won't get the negative sign.

[comment]

for the register functionality I will need them together (think Quicken).

I have never used Quicken, so it's difficult for me to think it. I am no accountant either, so I may be wrong on this, but it seems odd to me to have such requirement in a double-entry system. It seems more consistent with another type of "accounts", such as a banking account, for example. But in this type of solution, when a customer withdraws cash, their account is debited and that's it.

I am intrigued and frightened. Repeating fields? I thought they ate bad little newbies who don't lurk FMForums enough.

Could you post an example, if you have time?

I'm afraid that would take significantly longer than I can afford at the moment. However, I can suggest another alternative that is not too difficult to implement. Note however, that this requires tight control over navigation in the Acounts table - if you decide to put the portal on a layout of this table.

DoubleEntryRunning.fp7.zip

[FestiveEmbalmer]

Ok, that is pretty much awesome.

I'm going to take my time dissecting your file to figure out why you were able to do precisely what I was just now convinced could not be done. Especially since it looks so close to what I was doing, just obviously better, since it works!

Many thanks,

Matthew

[comment]

It IS very close, except it uses a global field to indicate which account is "current". That way a child CAN tell who's looking at it - but changing the "current" parent requires a script to change the global as well. And it only works for one account at a time, so a report is not possible using this method.