7 Replies Latest reply on Nov 29, 2013 8:20 AM by philmodjunk

    Set variable and Set field while looping



      Set variable and Set field while looping


           i want to perform a find on a table

           loop and

           duplicate the found set

           set variable on a field  called   Balance ( in the duplicated set only) . the variable name is $oldbalance

           while looping

           setfield  this field  PREV_Bal    as   $oldbalance for each record in ( in the duplicated set only)

           but it only set the first variable for all the duplicate


           i know something is missing somewhere   . Any assistance please


           this is my script that performs find and duplicate the found set correctly but the set variables and set field for each duplicate dont work


        • 1. Re: Set variable and Set field while looping

               Did you try running this with Script Debugger and Data Viewer?

               If this is the entire script, seems to me the fourth line of your script is wrong.  You're Set Field should be Set Variable.

               It also appears, in your loop, you duplicate the record (remember now you have 2), omit one of the duplicates, then go back into the loop and re-duplicate the same record.  Maybe you use Omit Record twice.  Like I said at the top, I would use Script Debugger and Data Viewer to watch this process step by step.

               Looking at your post again.....you set a variable $newTermID, then set a field $termID.  Are these suppose to be the same?

          • 2. Re: Set variable and Set field while looping

                 thanks  for your response

                 Here is the result of the script which is not what i expected

                 what i am expecting is to transfer your  balance to previousbalance if the record is duplicated but only the first records balance is transferred to all records which is wrong and thanks  for your response again

            • 3. Re: Set variable and Set field while looping

                   You might want to use a set up that does not need a script for this in order to show the previous balance.

                   Define a field called "cBal" as a calculation field with Amt.Due - Paid as its calculation.

                   Define a summary field, sBalance as the "Total of" cBal and specify the running total option.

                   You can then put sBalance on your layout in place of Balance to show the running balance for these transactions.

              • 4. Re: Set variable and Set field while looping

                     I think I have tried both suggestions with no success due to my novice status but what I want to implement is similar to balance brought forward or opening balance.

                     For example if I have database that keep record of clients payment which  needs to be closed at the end of every month. I have  four  tables Months , Clients ,monthlyclent and ClientPayment.  Month is the parent layout , monthlyclient and clientpayment are portals on the parent layout(month). Monthlyclient is a link table between Month and clients. 

                     Monthlyclients has the following fields


                     fkMonthID  -------------------- linking Month and Monthlyclient

                     fkClientID    -------------------- drop down list(clientID) from client table

                     BalanceBF  ----------------------  Balance from previous month (WHICH I’M STILL STRUGGLING WITH)


                     AmountPaid = clientpayment::SUM_DEPOSIT

                     Balance ---------------   AmountDue  –  AmountPaid

                     SUM_DEPOSIT  is a summary field in the  clientpayment table which total all the paid amounts by each client every month

                     Now if some clients have not fully settled their amount due in January and a new month (February)  is created in Months table , how will I move those debtors to February and showing their previous balance as the new BalanceBF in February?

                      and thanks for the previous replies.

                • 5. Re: Set variable and Set field while looping

                       There are several ways to manage the balance forward amount. One way is to set up a self join to all transactions for the same client but from all previous months. The sum of all preceding debits and credits (this would be a sum of cBal from my previous post), can then be included with the running total summary field amount to show the adjusted running total.

                       What kind of values are you using for pkMonth in your months table? (this could be a date, serial number or something else such as get (UUID ) )

                  • 6. Re: Set variable and Set field while looping
                    pkMonth uses auto serial number  or is there  a better alternative to that ? and thanks again
                    • 7. Re: Set variable and Set field while looping

                           For what I have in mind, it's the best alternative. But just fyi, many developers use a calculation field that returns the date for the first day of the month as a way to give all fields of the same month an identical matching value for use in reports, relationships and calculations...

                           Since you are using a serial number, all records with a given Client ID and a fkMonth less than the current record will be data from preceding months.

                           Thus, this relationship, a self join to a second occurrence of your table of transactions, will match to all transactions prior to the current month for the same client:

                           MonthlyClient::fkMonthID > MonthlyClient|Previous::fkMonthID AND
                           MonthlyClient::fkClientID = MonthlyClient|Previous::fkClientID

                           Note, but given the limited info that I have, you may need this kind of info for your payments table. I'm not clear on how you are currently using your table.

                           Once you have the relationship in place. The cBal calculation that I mentioned in my first post might be modified to include the balance forward if the record is the first record in your portal or found set:

                           amt Due - Paid + If ( Get ( RecordNumber ) = 1 ; Sum ( MonthlyClient|Previous::Amt Due ) - Sum ( MonthlyClient|Previous::Paid )  )

                           There are also ways to use this relationship with a script to capture the needed balance forward total and update a single field in your table of transactions and to speed recalculations, some system use a script to store the preceding month's balance in a number field in Clients instead of recalculating the value on the fly as you move from record to record.