11 Replies Latest reply on Apr 16, 2013 3:50 PM by philmodjunk

    Problems with Portals

    SamBeardsley

      Title

      Problems with Portals

      Post

            

           Table A is ‘Institutions’. BofA, TDAmeritrade, Teacher’s Credit Union, His Employer, Her Employer etc

           Fields are: Key, Name

           Table B is ‘Transactions’

           Fields are: Key, Date, Foreign Key (to Table A) for ‘debit’ institution, Foreign Key (again, to Table A) for ‘credit’ institutions, Amount (always a positive number)

            

           What I want:

           A layout that lets me scroll through the records of Table A (I can do this) but with a portal for each of the record that shows a basic double entry system i.e. When the layout is showing the ‘Credit Union’ institution the portal will show ledger entries with a credit column (possibly from His Employer) OR a debit column (possibly to BofA) and a balance column so I can check it against the credit union statement.

            

           I’m having a really hard time doing this.  I’ve tried using TOs and many to manys but it’s just not coming together for me.

           Any help will be appreciated.

           Sam

        • 1. Re: Problems with Portals
          philmodjunk

               For a given record in Institutions, can their be both Debit and Credit transactions? And you want both in the same portal?

               If that's what you want, define a calculation field,cMultiKey, in Transactions as:

               List ( _fkDebitID ; _fkCreditID ) // the two foreing keys to your debit and credit institutions.

               Select Text as the result type.

               Then these relationships can serve to make your portal happen:

               Instiutions-----<Transactions>--------DebitInstitutions
                                             V
                                              |
                                       CreditInstitutions

               Institutions::__pkInstitutionID = Transactions::cMultikey

               To get a balance calculation, you'd need a summary field or sum function that totals this calculation field:

               If ( Institutions::__pkINstitutionID = _fkDebitID ; Amount ; -1 * Amount ) //assumes debits are postive values. Reverse result terms to make credits positve.

          • 2. Re: Problems with Portals
            SamBeardsley

                 PhilModJunk

                  

                 Thanks so much for your reply:

                 Yes, any given record in INSTITUTIONS is allowed to be a debit or credit.  I'm ignorant of MultiKey but am going to read a bit about it. I sounds as if it's similar to the use of the pilcrow symbol (¶) in FMP, is it?

                 I'm not exactly sure what your schematic is showing but I'm assuming that DebtInstitutions and CreditInstitutions are TOs of Institutions and that there is a relationship between all three and Transactions.  Is this the case?

                 Again, thanks,

                 Sam

                  

                  

            • 3. Re: Problems with Portals
              philmodjunk

                   Sorry, left out this part of my post: For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   

                        I sounds as if it's similar to the use of the pilcrow symbol (¶) in FMP, is it?

                   Take out the word similar. That's EXACTLY what a multikey field is.

                   I used List ( field1 ; field2 )

                   but

                   Field1 & ¶ & Field2

              produces exactly the same result.

              • 4. Re: Problems with Portals
                SamBeardsley

                     Again, thanks.  I'm in the process of making this a many-many.

                      

                     Sam

                • 5. Re: Problems with Portals
                  SamBeardsley

                       SO Close...

                       I'm getting something that works... for the most part.

                       Attached is an image of a 'bench work' portal. Please ignore col: DspCR, DspDB. They will be used for displays later.

                       KillList is a MultiKey using 'List'

                       ToFrom is a MultiKey using a pilcrow.  As you can see, there seems to be consistency between the two MultiKeys

                       'marker' is PKinstitutions which is a result of KillList; one of two fields (FKinstCredit, FKinstDebt)

                       PROBLEM (other that having too much in the checking account):

                       As you can see, when 'marker' is not the same as the portal's key the math logic reverses itself for the balances.

                       Is there any formula that is able to compare to the currently displayed portal's id field or is this the wrong tack?

                       Thanks again.

                       Sam

                  • 6. Re: Problems with Portals
                    philmodjunk

                         As you can see, there seems to be consistency between the two MultiKeys

                         As I said before, both calculation methods produce identical results.

                         I can't really follow the details of what you have set up. What are the relationships that you are using.

                    • 7. Re: Problems with Portals
                      SamBeardsley

                           Phil,

                           Thanks SO much for sticking with me...

                           Here are two screen shots that will hopefully help.  Some of the fields I set up were just for diagnostics...

                           Again, thanks

                           Sam

                            

                      • 8. Re: Problems with Portals
                        SamBeardsley
                        /files/1911533534/Screen_Shot_2013-04-14_at_3.30.38_PM.png 819x453
                        • 9. Re: Problems with Portals
                          philmodjunk

                               Does this mean that you have a layout based on TBL_Institution_121107? (See Layout setup | Show Records from)

                               And your portal is to TBL_Transactions?

                                

                          • 10. Re: Problems with Portals
                            SamBeardsley

                                 Yes, to both of your questions.  You can see one of my portal pages on my 4/13 post.

                                 It's interesting; as you can see, the math goes 'bad' when the PK of the transaction is less-than the PK of the institution.  I can sense that I'm close; I'm just ignorant as to what formula/calculation to use to accomodate the math 'switch'.

                                 Again, thanks for sticking with me.

                                 Sam

                            • 11. Re: Problems with Portals
                              philmodjunk

                                   Does the multi-key have one or two values in the field in any of the records of transactions?

                                   If there is two values in the multi-key, it matches to two recorrds and when you put:

                                   TBL_Institution_121107::PKInstitution

                                   Your relationship matches to two records, but only the value of the field from the "first" related record is returned to the calculation. If there is no sort order specified in the relationship, the "first" related record will be the first one to have been created in the table.