6 Replies Latest reply on Jun 5, 2010 9:56 PM by MDownes

    Going from dynamic to fixed value in a portal

    MDownes

      Title

      Going from dynamic to fixed value in a portal

      Post

      Hi all,


      I'm designing a database with a billing section, where the main billing is done in a portal that uses a dynamic calculation (GetNthRecord function) to get the previous balance from the last bill date.  This works fine, except that I also need to generate a bill based only on certain dates (such as a monthly bill), and I'm using another, filtered portal (with user-selected date ranges) to generate the bill.  What happens though is that my dynamically-calculated previous balance disappears because the calc thinks that the first date in the filtered portal is the first record ever, and so assumes that there is no previous balance.

       

      How can I grab the previous balance amount from the non-filtered portal and make it a "fixed" value?  I've thought of copy and pasting into a separate field, but I wouldn't want to do that for all bill dates in a client's record because it might take a long time to do it each time (as sometimes there would be the option to go back in and change payments in previous bill dates, etc., so I would want the ability to just generate the previous balance for the first date in the filtered portal each time I create a bill).  


      Make sense?  My convoluted grammar is indicative of the muddiness of my thinking around this in general...

       

      I'm sure there's a very obvious solution to this, but my head's swimming in confusion at this point.

       

      Thanks ever so...

        • 1. Re: Going from dynamic to fixed value in a portal
          philmodjunk

          I can't fully follow your description. Try posting an example where you spell out the numbers and values where this goes wrong.

           

          Have you tried using a summary field set up as a running balance for this in place of a Get Nth Record calculation?

          • 2. Re: Going from dynamic to fixed value in a portal
            MDownes

            I did try working with a summary field, but for various reasons (way too long to go into now) it didn't give me the results I was looking for.

             

            Hmm, let's see.  I guess the best way to describe it is to give the code for the Get Nth Record calc:

             

            Case (IsValid (GetNthRecord ( Billing to Billing by Client Reversed::Client Balance Due Reverse Order ; Get ( RecordNumber ) - 1)); 

            GetNthRecord ( Billing to Billing by Client Reversed::Client Balance Due Reverse Order ; Get ( RecordNumber ) - 1))

             


            Where Client Balance Due is the balance after the previous balance due has been added to the current charges and any payments have been subtracted.  The reason for the Case / IsValid part is so that when there is not previous record (when the current record is the first bill that's been generated) it will return a zero value.  

             

            This works great for everything else, but when I create a filtered portal based on the same data that calculation thinks that there is no previous bill beyond the first date in the now filtered portal and throws everything off.  What I need is a way to grab the previous balance value in the unfiltered portal and make a non-dynamic value out of it.

             

            Is that making any more sense?

             

            --Myles

            • 3. Re: Going from dynamic to fixed value in a portal
              fitch

              Maybe you could use a second relationship to get the previous balance. Or using the existing relationship, you could store the starting filter date while your script runs, change the starting filter date to get the previous balance, then restore the starting date.

               

              The tricky part (maybe) is: what date do we change it to? If I get what you're saying, we want to get the value from one date earlier than the selected starting date. If the billing is done on a regular interval (monthly, weekly, etc) then it's not that tricky, but if you don't know how far back you need to go it might be.

               

              Here's another idea: each time you do billing, store the ID of the previous balance record in the current balance record. Make a relationship (pastID::currentID) and you'll always be able to grab the info you need.

               

              FWIW, in my experience with fiscal systems, it's generally better to not rely on unstored calcs. For one thing, generating reports in a large data set can become painfully slow. The corollary is that your methods of updating the stored balances must be rock-solid.

              • 4. Re: Going from dynamic to fixed value in a portal
                MDownes

                Ok, I'm giving up and going in a different direction.  I'll use my current calc as part of a script when creating a billing record (one-time, not dynamic),  but I need to know how to fix a record so it can't be altered after the original data entry.  In other words, create the billing record, enter whatever data needs to be entered, then have the user run another script that eliminates the ability to further alter the record.

                 

                Any ideas on how to do that?

                 

                Thanks again.

                 

                --Myles

                • 5. Re: Going from dynamic to fixed value in a portal
                  fitch

                  Search these forums for "lock" -- you'll find plenty of ideas.