9 Replies Latest reply on Jan 31, 2014 3:46 PM by RedL

    Calculation field error in portal rows except first row

    RedL

      Title

      Calculation field error in portal rows except first row

      Post

      My three calculation fields(Marked by A on the attached pdf) (UnpaidAmtThisMonth, UnpaidAmtLastMonth, and UnpaidAmtRange) are correct on the first portal row(marked B) and blank/incorrect on the other portal rows(marked C).  These three fields' relationship includes date range(marked D).  The relationship and date selection should be correct, because the first portal row calculation information is correct.

            
           The other calculation field(marked by E) (UnpaidAmt) is correct on all portal rows(marked F) which does not have date range.
            
           The portal row information will be correct if I remove data range relationship. For example, on the attached "Portal Row(without date range on This month)"(Mark G) portal information, I remove date range from BK_UnpaidThisMonth which mean the relationship is exact same as BK_Unpaid.
            
           The portal rows are incorrect if I change the all data range relationship from larger/equal and smaller/equal to "x".
            
           I use FMPV11.

      1.jpg

        • 1. Re: Calculation field error in portal rows except first row
          philmodjunk

               In order to understand what you have set up here, some key details need to be filled in:

               each of the "boxes" in your table relationship diagram are called "table occurrences. In your first screen shot of a portal, which table occurrence was selected for:

               The layout   ("Show Records From" in Layout Setup...)

               The portal ("Show Related Records From" in Portal Setup...)

               The fields you placed in the portal ( The name to the left of :: in "Display data from" in the inspector's data tab after you click each field while in layout mode.)

          • 2. Re: Calculation field error in portal rows except first row
            RedL

            Layout: Show Record from BK_Bookeeping

                      Portal: Show related record from BK_BKAccount
                       
                      BK_Bookkeeping linked with BK_Account by constant_1 on both sides.  It is global storage constant 1 field in BK_Bookkeeping and number field by auto enter 1 on BK_BKAccount.  The linkage is fine and all BK_BKAccount records show on portal rows.
                       
                      Portal fields:
                      Unpaid total: Display Date from BK_BKAccount::UnpaidAmt
                      This Month: Display Date from BK_BKAccount::UnpaidAmtThisMonth
                      Last Month: Display Date from BK_BKAccount::UnpaidAmtLastMonth
                      Date range: Display Date from BK_BKAccount::UnpaidAmtRange
                       
                       
                      BookkeepingAccount table fields:
                       
                      UnpaidAmt: Unsorted, from BK_BKAccount, = Sum ( BK_Unpaid::TotalAmtOrg ) - Sum ( BK_Unpaid::PaidAmountOrg )
                      UnpaidAmtThisMonth: Unsorted, from BK_BKAccount, = Sum ( BK_UnpaidThisMonth::TotalAmtOrg ) - Sum ( BK_UnpaidThisMonth::PaidAmountOrg )
                      UnpaidAmtLastMonth: Unsorted, from BK_BKAccount, = Sum ( BK_UnpaidLastMonth::TotalAmtOrg ) - Sum ( BK_UnpaidLastMonth::PaidAmountOrg )
                      UnpaidAmtRange: Unsorted, from BK_BKAccount, =  Sum ( BK_UnpaidRange::TotalAmtOrg ) - Sum ( BK_UnpaidRange::PaidAmountOrg )

                  

            • 3. Re: Calculation field error in portal rows except first row
              RedL

                   PhilModJunk,

                    

                   Do you have chance to review my additional posted info?  Thanks.

              • 4. Re: Calculation field error in portal rows except first row
                philmodjunk

                     Sorry, but spent yesterday transporting my wife to a medical specialist (2 hour drive one way) and spent today ill.

                     I am assuming that you are using FileMaker 12 or newer:

                     Try this test: Enter layout mode. drag the portal a small distance.Do these calculation fields move with the portal?

                     I suspect that they do not--indicating that theses fields are not correctly "owned" by the enclosing portal. This can happen if you use the Inspector's alignment tools to move the fields from outside the portal into the portal row.

                     The "fix" for this is to drag the fields outside the portal, release the mouse button and then drag them back. once the fields are fully enclosed within the portal row and move when you move the portal, you can use the arrow keys or the alignment tools to "fine tune" their position within the portal row.

                • 5. Re: Calculation field error in portal rows except first row
                  RedL

                       Phil, thank you for your comments during your busy time.  2 hours for one way a a long drive.

                       I am using V11, not V12 or newer.

                       I create another portal and result is same.

                       As I mentioned, if I remove the date range linkage, for example BK_UnpaidAmtThisMonth, the data will show up correctly.  It implies the field positions are correct and it seems the data range linkage is the root cause.

                  • 6. Re: Calculation field error in portal rows except first row
                    philmodjunk

                         Then you should double check the values in these data fields in the portal's records and see if they are really in the range that you are specifiying.

                    • 7. Re: Calculation field error in portal rows except first row
                      RedL

                           I will double check it later again.

                            

                           But, the first portal row data is calculated correctly by date range.  It implies date fields and date range linkage are correct.

                            

                           All information of calculation fields on first portal row are correct while the second and below are incorrect.

                      • 8. Re: Calculation field error in portal rows except first row
                        philmodjunk

                             Yes, but what you have shown is not simply that they are incorrect, but missing altogether.

                        • 9. Re: Calculation field error in portal rows except first row
                          RedL

                               Phil, thank you so much for your help.  Finally, I find the issue.  It is my negligence.  The fields of date range are not Global which have date on first record only.  That's why the first record has correct calculation while the rest rows are empty.  Thank you again.

                                

                               One small negligence took me a while to debug.