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?
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
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.
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.
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 ) )
pkMonth uses auto serial number or is there a better alternative to that ? and thanks again
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.