7 Replies Latest reply on Feb 8, 2015 2:16 PM by disabled_jackrodgers

    New portal records not being included in calculation

    todd-

      Any help is greatly appreciated! I've spent many of hours trying to figure this out to no avail. : (

       

      A Layout showing records from Payments with a Portal showing records from Invoices.

       

      The portal shows records from Invoices. And Includes the PaymentDetail::Amt field.

       

      The idea is the user enters payment information (ex. ck amt & ck#) and invoices due are listed in the portal. When the user enters an amount to apply towards an invoice, they enter the amount in the portal row into the PaymentDetail::Amt field. This creates an new PaymentDetail record.

       

      I have a non-stored calculated field Payments::cSumOfPaymentDetailsAmt. The user is not allowed to save unless Payments:PayAmt = Payments::cSumOfPaymetDetailsAmt.

       

      So far all works as should, however when the user enters a new amount to apply to an Invoice in he portal it is not added to Payments::cSumOfPaymentDetailsAmt unless a Commit is done which defeats the whole purpose of not allowing the user to save the data until Payments:PayAmt = Payments::cSumOfPaymetDetailsAmt.

       

      My understanding is records in a transaction are created that can be seen but not fully saved until commited. And the new PaymentDetail record is their but the Payments table can not see it until the relationship re-queries the results.

       

      I have tried numerous combinations of calc fields, relationships, etc and even the following article which didn't seem to work ...

      Ditch those Flush Caches, Use Cartesian Join Instead!

      http://www.teamdf.com/weetbicks/ditch-those-flush-caches-use-cartesian-join-instead/42

       

      It seems this would be a common practice in solutions but I just can't figure out how to get the calcuated field to show the correct results from the new records in the portal.

       

      Relationship graph -

      Payments -> Invoices -> PaymentDetails

       

      Payments table

      Id

      CustId

      PayAmt

      PayDesc

       

      Invoices table

      Id

      AmtDue

      [etc]

       

      PaymentDetail

      Id

      PaymentId

      InvoiceId

      Amt (this is the part of a payment applied to an invoice)

        • 1. Re: New portal records not being included in calculation
          Stephen Huston

          Do not expect changes made in related portal records to be reflected in the parent record calcs until the record(s) get committed. Portal records are uncommited after edits until the parent record is committed, so the calcs won't update until then.

           

          You can script a commit using a script trigger, or just by exiting all fields in both the parent record and the portal rows.

           

          This has always been one of the biggest headaches of getting proper screen refreshes on edited portal rows.

          • 2. Re: New portal records not being included in calculation
            todd-

            Thanks for your reply, it's much appreciated! : )

             

            From your bio it sounds as though you should know what you are talking about and thus because Filemaker works that way I am discouraged. : (

             

            From the web link I listed it sounds like that should work and fix the problem but it did not for me. : (

             

            I would think this would be such a common task in any database and therefore there must be some method or apporach to such madness.  I'm not sure what else to do except create temp fields in the invoice file to hold amounts (parts of the payment to be applied) and then in a script create new records & move amounts.  But that could easly turn into a multiuser nightmare.  Or maybe (just thought of it) use a script trigger on field changes to add any amounts togehter in a global variable rather than rely on the Sum function in a field calculation.

             

            I'm just trying to work with how Filemaker works rather than work against it.

             

            Again thanks for your help and the saga continues .............

            • 3. Re: New portal records not being included in calculation
              Stephen Huston

              One of the things some developers do is include a Save or Update button in the portal row which runs a script to set a variable for the portal object name (if there are multiple portals on the layout) and the Row Number. Then the script goes to a non-portal object, commits the record, refreshes the window/screen, then returns to the portal and row saved in the variables.

               

              The user never sees anything but a moment's pause and they're right where they were.

               

              And that's really only necessary if you need calcs in the parent record to update based on live changes without appearing to leave the portal.

              • 4. Re: New portal records not being included in calculation
                todd-

                Thanks for your response! : )  Thought that does sound cumbersome and awkward for a user as well as losing the transaction ability of Filemaker.

                 

                I love how transactions work in Filemaker even though it can be a little bit more work implementing at times.

                 

                It's still a bit of a challenge gettting the billing part of our company application done but I will somehow get it figured out.

                 

                In many cases I've found on the Internet there is a payment and portal of which invoices are selected and added to.  However in my case each invoice is a service (and there are many services) so it would be a lot of work for the user to select a new service (invoice) to add to the portal.  Instead I would like to list all invoices/services and the user enter amounts that should be applied to invoices/services.  I can base the portal off the invoices/services and add a PaymentDetail::Amt to each portal record, which would create a new PaymentDetail record for any invoices Amt is entered for.  But what if a user didn't mean to enter an amount and blanks the amount out.  To late the PaymentDetail record has already been created.  The only why I knew to resolve this is to make sure a 0 was entered for blank amounts in PaymentDetail.  Then OnCommit have a script that goes to another layout to delete any records with 0.

                 

                Most things in life are best keep simple/stupid (as the old saying goes), Filemaker seems to work better with that idea.  Still trying to work with how Filemaker works.  There is so much to love about Filemaker and I'm trying to think simple to keep things simple.  Just having a hard time finding that method of with billing.

                 

                Again thanks and have a blessed Christmas season!

                • 5. Re: New portal records not being included in calculation
                  keywords

                  One method you might like to consider keeps the user from ever touching the live tables in your database—actual payments and invoices, etc. This method involves having a separate table for data entry. This table mirrors the live table but you only ever create temporary records. So, if the user wants to enter a payment, say, a new temporary record is created, all the details are entered, you can build it all to work with FM's standard features, etc. but from the user's point of view they would appear to be creating all the details as normal—BUT the big "Commit" button you put on the layout will run a script that gathers up all the data entered in this temporary table, checks everything for validity, sends the user back to fix errors, then if everything is OK creates the relevant records in the live tables, finally deleting the temporary records.

                   

                  I have successfully applied this technique in a Cash Book db where data integrity is high priority.

                  • 6. Re: New portal records not being included in calculation
                    todd-

                    Thanks for your help!  I had thought of that.  There are a number of directions to work towards to get this to work.  It's nice to hear how others get projects accomplished to help stimulate ideas and to know what has worked for others.  Thanks again.

                    • 7. Re: New portal records not being included in calculation

                      I believe what you are trying to do is take a payment and split it among invoices due but not apply more than the amount received.

                       

                      You also need to prevent error.

                       

                      So:

                       

                      $_payment_balance = check amount

                       

                      go to table layout and find the customers unpaid invoices, sort by oldest date.

                       

                      go to first record

                      loop

                      set variable $_invoice to invoice number

                      set variable $_amount to invoice balance

                      got to table layout for payment records

                      new record

                      set invoice number to $_invoice

                      if( $_amount < or = $_pament_balance)

                             set field amount paid to $_amount

                            set variable $_payment_balance to $_payment_balance - $_amount

                      else

                          set field amount paid to $_payment_balance

                            set variable $_payment_balance to 0

                      end if

                      go to record next exit if last

                      exit loop if $_payment_balance < = 0

                      end if

                       

                      I believe you can use commit records to save all of these records if OK or Revert to delete them.

                       

                      This method pays all invocies upto the amount due and will apply a partial payment if there is an unpaid balance or if funds are insufficient for a full payment.

                       

                      Please verify the script steps before implementing.