8 Replies Latest reply on Jan 20, 2015 2:41 AM by BorisKamp

    Ledger system relationship and checking question

    BorisKamp

      Title

      Ledger system relationship and checking question

      Post

      Hi!

      I have three tables that are relevant to this question out of 25 in total:

             
      1. Properties (with __pkProperty ID)
      2.      
      3. General Ledger (with __pkGeneralLedger ID)
      4.      
      5. Property Ledger (with _fkProperty ID & __fkGeneralLedger ID)

      Table 3 should be a breakdown of an entry in the general ledger but also connected to a property so I think I need this relationship: 

             
      • Table 1 & 2 have NO relationship
      •      
      • Table 3's '_fkProperty ID' is connected to table 1's '__pkProperty ID'
      •      
      • Table 3's '__fkGeneralLedger ID' is connected to table 2's '__pkGeneralLedger ID'

      is this correct?

      one more question:

             
      1. Imagine a general ledger entry of $500.000
      2.      
      3. this transaction is payment for two properties, Property A for 3K and Property B for 2K
      4.      
      5. In the Property A property ledger we should add a 3K entry and pick the '__pkGeneralLedger ID' from a pop-up menu field named 'related general ledger ID'
      6.      
      7. In the Property B property ledger we should add a 2K entry and pick the '__pkGeneralLedger ID' from a pop-up menu field named 'related general ledger ID'

      Now we know what the related general ledger entry is, now I need a checking system in the general ledger that says something like 'Checked' after a calculation like this:

      If( general ledger amount = total of(amounts of property ledger entries with this ID) )

      And if NOT say something like 'No'

      I can't seem to figure out that calculation! how can I achieve that?

       

      Thanks guys!

       

        • 1. Re: Ledger system relationship and checking question
          philmodjunk

          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.

          • 2. Re: Ledger system relationship and checking question
            BorisKamp

            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...

            • 3. Re: Ledger system relationship and checking question
              philmodjunk

              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 ???

              • 4. Re: Ledger system relationship and checking question
                BorisKamp

                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.

                • 5. Re: Ledger system relationship and checking question
                  BorisKamp

                  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?

                  • 6. Re: Ledger system relationship and checking question
                    philmodjunk

                    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-----<PropertyLedger>------GeneralLedger

                    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.

                    • 7. Re: Ledger system relationship and checking question
                      BorisKamp

                      Hi Phil,

                      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.

                             
                      1. 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.
                      2.      
                      3. 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!")

                      right?

                      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?

                       

                      Thanks Phil!

                       

                      • 8. Re: Ledger system relationship and checking question
                        BorisKamp

                        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