12 Replies Latest reply on May 1, 2013 8:22 AM by philmodjunk

    Portal Problems II

    SamBeardsley

      Title

      Portal Problems II

      Post

            

           Hello Friends,

           Using FMP-12

           Still having problems with a Portal project.  I need to know if there is a Function in FMP that returns a selected field value from the ‘current’ parent table of a portal relationship.  I need that value for a calculation within one of the child fields.

           TIA – Any support will be appreciated.

           Sam

        • 1. Re: Portal Problems II
          philmodjunk

               What do you mean by "a selected field value"?

               and in what context? A conditional format expression? A script step? A calculation field?

               In a calculation field, you can directly reference any field from the parent record simply by adding that field to the calculation much like you would a field in the portal's table, but with the parent table occurrence included in the field reference. This can be as simple as finding the field in the top "box" of the specify fields dialog and double clicking it to add it to your calculation.

          • 2. Re: Portal Problems II
            SamBeardsley

                 Same issue I was having before with my orignal post "Problems with Portals 4/8)

                 Tbl A

                 Tbl A1 is a T.O. of Tbl A

                 Tbl A2 is also a T.O. of Tbl A

                 Tbl B

                 ==============

                 Tbl B is a child AND a join Table in a many-many relationship with parent tables A1 and A2

                 Tbl A is the Portal parent of child Tbl B but because the relationship with Tbl B is by way of a 'LIST' function (related to two of Tbl B's fields) it ias an 'OR' selection (filter?) to the portal child.

                 One of my field calculations of the portal child, Tbl B, is dependent on the parent Primary Key value.  Because of the 'OR' nature of the 'LIST' function, that PK is not always the exclusive determinant as to what records from the child (Tbl B) are displayed in a portal.

                 The bottom line is that I need some indicator that lets me know if it's the Portal parent that caused the display of the child record or the 'NOT' state.

            • 3. Re: Portal Problems II
              philmodjunk

                   Using letters instead of actual names just makes it harder to comprehend what you are doing.

                   

                        Tbl B is a child AND a join Table in a many-many relationship with parent tables A1 and A2

                   Which would mean you have this: A1----<B>-----A2

                   

                        Tbl A is the Portal parent of child Tbl B but because the relationship with Tbl B is by way of a 'LIST' function (related to two of Tbl B's fields) it ias an 'OR' selection (filter?) to the portal child.

                   Sorry, but that doesn't match the relationships you initinally described for A1, A2 and B. And are you referring to Table A1 or Table A2 when you talk about this portal setup?

              • 4. Re: Portal Problems II
                SamBeardsley

                      

                     OK, I’ll try again…

                     First, thank you again for sticking with me on this project.

                      

                     There’s actually three “Table A’s”.  I’ll employ your suggestion and resort back to their original names:

                     INSTITUTIONS

                     INSTITUTIONS_TO_DEBIT (A Table Occurrence of INSTITUTIONS)

                     INSTITUTIONS_TO_CREDIT (Another Table Occurrence of INSTITUTIONS)

                      

                     Table “B” is TRANSACTIONS

                      

                     Again;

                     INSTITUTIONS is the Portal parent of child TRANSACTIONS, but because that relationship with TRANSACTIONS is by way of a 'LIST' function (related to two of TRANACTIONS’ fields) it is an 'OR' selection to the portal child.

                     One of my field calculations of the portal child, TRANSACTIONS, is dependent on the parent Primary Key value.  Because of the 'OR' nature of the 'LIST' function, that PK is not always the exclusive determinant as to what records from TRANSACTIONS (the child) are displayed in a portal.

                     The bottom line is that I need some indicator that lets me know if it's the Portal parent that caused the display of the child record or the 'NOT' state.

                • 5. Re: Portal Problems II
                  schamblee

                       You only have two tables Table A Institutions and Table B Transactions.   Table Occurrences are not tables they are Occurrences.   Your bottom line statement makes no sense, all records that are displayed in a portal is based on the relationship being true.   If it doesn't match the relationship then it is not displayed. 

                  • 6. Re: Portal Problems II
                    philmodjunk

                         To recreate the relationships we discussed eariler: (but I've shortened your names to save typing)

                         Institutions------<Transactions>------Institution|Debit
                                                              v
                                                               |
                                                        Institution|Credit

                         Institutions::__pkInstitutionID = Transactions::_fkMultiKey
                         Institutions|Debit::__pkInstitutionID = Transactions::_fkDebitInstID
                         Institutions|Credit::__pkInstitutionID = Transactions::_fkCreditInstID

                         Let me know if any of that is incorrect.

                         Like S. Chamblee, I am puzzled by this statement:

                         

                              The bottom line is that I need some indicator that lets me know if it's the Portal parent that caused the display of the child record or the 'NOT' state.

                         The portal parent ALWAYS causes the transaction record to appear. It's only a case of whether it matches to the DebitID or the CreditID so I am unclear about the exact details of the issue.

                          

                    • 7. Re: Portal Problems II
                      SamBeardsley

                            

                           PhilModJunk & S. Chamblee,

                            

                           You’re right, I wasn’t clear on the last sentence…

                           and yes, Phil, your diagram is correct.

                           It may work best to post a screen shot just to illustrate my situation.  It’s supposed to be a simple accounting ledger.  One column shows the transaction amount if it’s a credit amount; the next column shows the transaction amount if it’s a debit amount and the last column shows the balance for that portal’s parent.

                           Something is going ‘wrong’ with the math.

                           If you look at records 1556, 1776, 1775 (and others), they work right; strange thing is, that when FKInstCR < FKInstDB AND FKInstCR < the Portal parent’s PK the math goes bad (see record 1771).

                           Any suggestions that you may have as to how to write a formula that will place the amount in the proper column and keep the math straight would be appreciated.

                      • 8. Re: Portal Problems II
                        philmodjunk

                             We have to go over this one carefully, nailing down every single detail. This is a layout based on Institution with a portal to transactions?

                             And looking at transaction 1771, I see a debit of $635.00 The running total on the previous record is 152,607.76.

                             I would assume that debit + previous rec running total = new running total. And when I punch these numbers in I get 153,242.76--exactly the total shown.

                             So what error am I not seeing?

                        • 9. Re: Portal Problems II
                          SamBeardsley

                                

                               You’ve identified it perfectly.  1771 is supposed to be a DEBIT from the institution called FT Checking.  The FKInstDB (22) is the same as the PKinstitution (22) but my formula is placing the quantity in the wrong column.  See new screen shot.

                               Like I said though, the strange thing is, that when key value (not the transaction amount) of FKInstCR < FKInstDB AND FKInstCR < the Portal parent’s PK the math goes bad.

                               I’m completely open to the idea that I’ve created a bad formula but I’m looking for a way to construct one that works.

                          • 10. Re: Portal Problems II
                            philmodjunk

                                 It took me a while to trace this through but now I see the issue.

                                 Set up the OnRecordLoad trigger on your Institutiions layout to do this:

                                 Set Variable [$$CurrentInstitution ; value: TBL_Institution_121107::PKInstitution ]
                                 Refresh Window []

                                 Modify your  c_Inst_Credit and c_Inst_Debit calculations to compare _fK field to this global variable instead of the PK field from Institutions.

                                  

                            • 11. Re: Portal Problems II
                              SamBeardsley

                                   Sweet success!!

                                   Phil, thanks SO much!  You've introduced me to the world of Global Variables.

                                   (Where is the list of these variables stored?)

                                   Anyway, this part is working very well.  Thanks again!

                                   Sam

                              • 12. Re: Portal Problems II
                                philmodjunk

                                     You put your finger on the key shortcoming of global variables. There is no central list. If you have FileMaker Advanced you can see them appear in the data viewer when they first get a value, but other than that, you have to set up your own means for keeping track of them.