I don't quite see the need for a property ledger table at all. Why not make the entries directly in the general ledger and link the record to the appropriate Property Record? GL entries that are not linked to a specific property would leave the _fkPropertyID field empty.
To recreate the "look" of your property ledger, you can perform a find or use Go to related records to pull up a found set of GL records for just that one property or you can sort your GL records to show all transactions for each property in groups arranged in chronological order with a sub total balance for each.
Thanks for your answer Phil!
I get your point, but we want the general ledger to recreate our bank transactions, and what if one transaction is for multiple properties? like the example I displayed above? how would you breakdown that transaction to multiple properties and be able to see what the total balance is per property?
maybe I'm missing something but I think I need the property ledger...
How do you show one transaction for multiple properties in the GL? That's definitely a detail that I did not spot in your posts, but I'm quite puzzled as to what kind of transaction that might be. The sale of a group of properties? Rent paid by a tenant that rents multiple properties and makes a single payment for all of them or ???
Oh, sorry, I thought i explained that in my first post with one transaction of 5k for two properties?
You're right, those examples are exactly some of the examples we're dealing with.
Do you have any clue on how to get that checking calculation working? and how about my setup as explained above? You think that's a good approach?
Sorry for missing those details. What had me confused is that you are attempting to link a single 5K GL entry to (apparently) a single property ID when you need to link it to multiple properties and the total must be correctly apportioned to each property specific entry in the property ledger.
I don't see a reason for linking the Property table directly to the General Ledger.
I'd link it this way:
Properties::__pkPropertyID = PropertyLedger::_fkPropertyID
GeneralLedger::__pkGLID = ProperyLedger::_fkGLID
So if you receive a payment (or bill) that applies to more than one property, you would create a new GL record to log that payment or expense. You'd use a portal to PropertyLedger (in FileMaker 13, this portal could be placed inside a Popover) to document which portion of the bill or payment applies to which property, by entering an amount and selecting a property for each portal record.
Sum ( PropertyLedger::Income ) would compute the total income entries in the property ledger for comparison with the the income amount that you enter into the associated GL record. You can even set up a conditional format to do a color change when the two values do not match.
No problem! Again, it's awesome you're thinking with me on this!
I already made a start today and have the tables linked the way you confirmed to me in your last post.
I'd already setup the general ledger like this
And setup a property ledger inside the property layout like this
A PL (property ledger) can contain multiple transactions from multiple GL entries.
according to your last reply I understand that your way would be to work the GL in 'form view', while mine is in 'list view'?
Let me try to re-explain what you said just so I'm sure I understand your way of thinking.
- To create a new GL entry I would add a new record in a form layout (just one record in display), enter the info needed like amount etc.
- I would add a portal to the layout from step 1 based on the PL table and add a popup field like 'address' so I can 'target' the amount to a property, and then fill out the rest of the portal record.
In order to do a check between the sum value of PL and the value of GL, I could create a field named 'GL PL Comparison' with a calculation like this:
if( General Ledger Amount = Property Ledger Sum; "Correct!"; "Not Equal!")
And then I could keep my GL layout from my screenshot additionally as an 'overview' layout and keep my PL portal inside my property layout for overview purposes as well.
Sounds good to me! am I right?
I created the stuff I mentioned above but am stuck on how to 'select' the property in the property ledger portal other then trough the property ID.
I would rather use the 'Address" field from the 'properties' table in a pop-up list to select the property. How can I do this?
The Address field is a calculation of:
AdressStreet# & " " & Streets::Street & ", " & Cities::City