Looping Counter

[Fred in Thailand]

I have a DB that I would like to use a script to create new records in. It is used to calculate and display a payment schedule for a contract.

Fields are:

Sale Price

Deposit

Number Of Payments

Payment Date

Payment Amount

Date Paid

Payment Amount is calculated from (Sale Price) Minus (Deposit) divided by (Number of Payments)

I need The script To create new records in the table = to the (Number of Payments)

The Number of Payments comes from a table ContractInfo as does Sales Price and Deposit.

The Payment Date needs to be calculated from PaymentStartDate also in the ContractInfo Table

In other words... Number of payments = 12

First new record = Jan 1, 2008 $1587

Second New Record = Feb 1, 2008 $1587

and so on through 12 new records

Where I am having problems is with the structure of the Loop in the script

[mr_vodka]

It would seem like a straight loop to me. The date could be created as part of the loop.

Set Variable [$sd; ContractInfo::PaymentStartDate ]

Set Variable [$n; Number of payments ]

Set Variable [$k; 0 ]

Loop

New Record

Set Field [ Amt; PaymentAmt ]

Set Field [ PayDate; Date ( Month ( $sd ) + $k; Day ($sd); year ($sd) ) ]

Set Variable [ $k; $k + 1 ]

Get to Record [Next; Exit after last]

Exit Loop If [ $k > $n ]

Commit Record []

BTW check the syntax etc since this was off the top of my head and I am in a rush right now.

Edited March 9, 2008 by Guest
Dont need that step

[Fred in Thailand]

Thanks again. As usual you hit it out of the park.

It would seem like a straight loop to me. The date could be created as part of the loop.

Set Variable [$sd; ContractInfo::PaymentStar tDate ]

Set Variable [$n; Number of payments ]

Set Variable [$k; 0 ]

Loop

New Record

Set Field [ Amt; PaymentAmt ]

Set Field [ PayDate; Date ( Month ( $sd ) + $k; Day ($sd); year ($sd) ) ][color:red]Never would have thought of this.

Set Variable [ $k; $k + 1 ]

Get to Record [Next; Exit after last]

Exit Loop If [ $k > $n ][color:red]Changed > to > As it was creating one to many records

Commit Record []

As usual I sit as a student at your knees.

Someday maybe I will finally have some knowledge I can impart to others. As for now, I just drink from the cup of knowledge.

I owe you a bottle of "Chopin"

Edited March 8, 2008 by Guest

[comment]

It's good practice to have the exit condition as the first thing in the loop - it prevents anything happening before it's checked. For example, if someone hits the button while the number of payments is still empty, your script will create a record first, ask questions later.

[Fred in Thailand]

Comment,

How can you have the exit first thing?

It would seem like a straight loop to me. The date could be created as part of the loop.

Set Variable [$sd; ContractInfo::PaymentStar tDate ]

Set Variable [$n; Number of payments ]

Set Variable [$k; 0 ]

Loop

New Record

Set Field [ Amt; PaymentAmt ]

Set Field [ PayDate; Date ( Month ( $sd ) + $k; Day ($sd); year ($sd) ) ][color:red]Never would have thought of this.

Set Variable [ $k; $k + 1 ]

Get to Record [Next; Exit after last][color:green]Also I did not use this. Would not loop with it.

Exit Loop If [ $k > $n ][color:red]Changed > to > [color:red]As it was creating one to many records

Commit Record []

Edited March 9, 2008 by Guest

[mr_vodka]

Ok now that I have more time and am able to actually think more about it, here is the script that you could use.

Set Variable [$sd; ContractInfo::PaymentStartDate ]

Set Variable [$n; Number of payments ]

Set Variable [$k; 0 ]

If [$n]

  Loop

  Exit Loop If [ $k >= $n ]

  New Record / Request

  Set Field [ Date ( Month ( $sd ) + $k; Day ($sd); year ($sd) ]

  Set Variable [ $k; $k + 1 ]

  End Loop

  Commit Record []

Else

  Show Custom Dialog [ "Enter Number"; "No number of payments entered" ]

End If

[comment]

You could also use $n directly as the counter - if you don't mind creating the records from the bottom up. However, that would only work if all payments are equal, as we seem to be assuming here.

Such assumption may be overly optimistic: for example, dividing a total amount of $100.- into 3 installments should really result in one payment of $33.34 and two payments of $33.33 each. I think I would prefer to set a variable $p to:

33.34

33.33

33.33

then use this both as the counter as well as the source for setting the payment's Amount field.

[Fred in Thailand]

John,

Thanks again. I have it working now and it makes absolute sense to put the exit loop step first. Unfortunately I cannot use the If step as this loop is at the end of a script which also creates a new customer record and a new Contract info record. The payments schedule is the final step.

Comment,

You are right to assume that the payments are equal. (Not really but they are for this situation.)

As far as the counter is concerned, are you saying that using $n as the counter we would count down say from 12 to 0?

[comment]

Yes.

[Fred in Thailand]

Got It...

[color:red]Ah HA Moment