6 Replies Latest reply on Apr 20, 2015 12:45 AM by BorisKamp

    Relationship between tables issue

    BorisKamp

      Title

      Relationship between tables issue

      Post

      Hi guys

      I have these tables:

      - Properties

      - Annual NOI Ledger

      - General Ledger

      The Annual NOI ledger appears as a portal on the properties layout.

      Whenever a NOI wire is made, it is entered in the general ledger and we'd need to connect it to a property, to do this, I make a record in the Annual NOI portal.

      However, the relationship __pkGeneralLedgerID - _fkGeneralLedgerID is not working. even if I use a TO as seen in the screenshot. What am I missing here?

      Thanks!

      Screenshot_2015-04-13_12.18.38.png

        • 1. Re: Relationship between tables issue
          philmodjunk

          There's no screen shot.

          To link a record in the General Ledger table to a property, I would think that you would need this relationship:

          General Legder::_fkPropertyID = Properties::__pkPropertyID

          • 2. Re: Relationship between tables issue
            BorisKamp

            Hi Phill,

            Does the link not work? I attached it to the post itself, you should see it now.

            I think the General Ledger should not be linked to the properties table. Let me know if you have questions after seeing the screenshot.

            Thanks!

            • 3. Re: Relationship between tables issue
              bvondeylen

              Can you post a screen shot of the relationship detail (double click the = sign between the two TO).

              It appears from quick glance that you may have _pkPropertyID = _fkGeneralLedgerID AND _fkPropertyID

              Or is that what you intended?

              • 4. Re: Relationship between tables issue
                BorisKamp

                Hi Bryan,

                You're right, it looks like it, but it's just _pkPropertyID = _fkPropertyID

                please see the attached screenshot.

                 

                Thanks!

                • 5. Re: Relationship between tables issue
                  philmodjunk

                  There's still a lot that I don't know about your system and your business model that you need to support with it.

                  a) what is an "annual NOI Ledger" and what purpose does it serve in your system

                  b) "to do this, I make a record in the Annual NOI portal." From a layout based on which table occurrence? Properties?

                  c) "However, the relationship __pkGeneralLedgerID - _fkGeneralLedgerID is not working. even if I use a TO as seen in the screenshot." and which TO would that be?

                  Guessing at a few details and ignoring others, I can hazard this guess.

                  You are on a layout based on Properties with a portal to "Annual NOI Ledger". You want to create a new record here--which will automatically link it to properties, but you also need to link it to a specific record in "General Ledger". Simplest approach would be to format the Annual NOI Ledger::_fkGeneralLedgerID with a value list of IDs from the general ledger ID so that you can select the correct record in General Ledger to which to link your new entry. But the fact that this entry is for a specific property and for a particular type of item (a "wire"), might be sufficient data to use to make this linkage an automatic look up of the needed ID, but I can't tell if that is the case and your current set of table occurrences and relationships would need modification in order to make that happen.

                  • 6. Re: Relationship between tables issue
                    BorisKamp

                    Hi Phil,

                    Thanks for you help on this:

                    a) This ledger is one of multiple needed in order to make our bookkeeping clear and foolproof with automatic checks to connect it to a property etc. More specific, some of our properties are rented, in order to yearly book this income in our system, we need to book it on our general ledger (amount per 12 months, yearly). In order to make the 'check' on our general ledger we need to see if GeneralLedger::amount = AnnualNOILedger::amount. for the annual noi ledger to know what general ledger record to use, it needs to be matched by the GeneralLedger::GeneralLedgerID. In the meantime, the Annual NOI Ledger record must be linked to a property as well.

                    b) yes, based on the properties table

                    c) The TO I was talking about, is the 'General Ledger 2' which can be seen above the origina (source table) 'General Ledger' table

                     

                    About your guess, you're right. I had already setup AnnualNOILedger::_fkGenralLedgerID to use a value list of general ledger ID's but it's still not working. My 'checked' field in the general ledger table looks like this:

                    Case(
                    Amount = Property Ledger::Total Amount; "Checked";
                    Amount = Internal Wires Ledger::Total Amount; "Checked";
                    Amount = AWF I Ledger::Total Amount; "Checked";
                    Amount = AWF II Ledger::Total Amount; "Checked";
                    Amount = Annual NOI Ledger::Total Amount; "Checked";
                    "Not Equal!"
                    )

                    note it uses 'Total Amount' because it can be a sum of multiple properties or annual ledger records. e.g. a 1 mil general ledger entry can be split up in 5 $200k amounts across multiple properties.

                    Please let me know if you need more info.

                    Thank you very much so far!