Create Record based upon another - HELP!

[LaRetta]

I have a found set of records. I need to create new records based upon this set. These records are Service Billing LineItems. Each record needs to have another record created as an adjustment lineitem. For example:

Service Record:

Dept 4000

Debit GL 1202

Debit Amount $85.00

Credit GL 5003

Credit Amount $85.00

New adjustment record:

Dept 4000

Debit GL 9300

Debit Amount (ServiceRecord DebitAmount * .094118

Credit GL 1202

Credit Amount (same as debit amount)

Is there a way to loop through records and create a new record for each, inserting the proper Dept, Debit & Credit GL & Amounts?

Oh, I sure hope so! I guess another option would be to add a Debit/Credit/DebitAmount/CreditAmount to each lineitem in which to place this adjustment information, but I really need total GL figures by GL Account number. Also, when payments come in, they need to offset each lineitem. Ideas please

[BobWeaver]

I've done a few things like this recently. The thing to remember is that any new records you create in an unsorted set will always go to the end of the set. So, when you loop through, you have to remember which record your were on when you create a new one, so that you can go back to it to continue the loop. In this case, the global field gRecordNo is used to save the record number, and several other globals are used to hold the other data from the old record.

# Ensure that new records are created at the end of the found set.

Unsort

Go to record/request [first]

loop

# Get the data from the existing record to be used in the new one

Set Field [gNewDebitAmount, .094118 * DebitAmount]

Set Field [gDept, Dept]

Set Field [gDebitGL, DebitGL]

Set Field [gCreditGL, CreditGL]

# Save the current record number

Set Field [gRecordNo, Status(CurrentRecordNumber)]

# Now create the new record

New Record/Request

Set Field [DebitAmount, gNewDebitAmount]

Set Field [CreditAmount, gNewDebitAmount]

Set Field [Dept, gDept]

Set Field [DebitGL, gDebitGL]

Set Field [CreditGL, gCreditGL]

# Now go back to the old record to continue with the loop

Go to Record [gRecordNo][by Field value]

# Now go to the next one.

Go to Record/request [next][exit after last]

#Quit when you run into the adjustment records

Exit Loop If [DebitGL = "9300"]

End Loop

---

Make sure you back everything up before you run this.

[LaRetta]

Oh, Lord have mercy Bob! I'm terrible at scripts!! I'm printing it and will try to figure it out, thanks so much!! This is a deadline project for tomorrow ... like, when ISN'T something a deadline

OH NO ... MY PRINTER JUST RAN OUT OF INK! Well, I need to practice my printing anyway Geeeez!

[CobaltSky]

You may be able to get to the end point you want with a few less steps if you use the 'Duplicate' command to save you having to store values to carry between records, plus use 'Omit Record' to manage the movement around the found set, rather than tracking location with a temp field.

The resulting script would run along the lines of:

Go to Record/Request [First]

loop

Duplicate Record/Request

Set Field ["CreditAmount", "0.094118 * DebitAmount"]

Set Field ["DebitAmount", "CreditAmount"]

Set Field ["Credit GL", "Debit GL"]

Set Field ["Debit GL", "(not sure where this new number is coming from?)"]

Omit Record

Go to Record/Request [First]

Exit Loop If ["Status(CurrentFoundCount) = 1"]

Omit Record

End Loop

The only issue that this leaves unresolved is at step 7, because I was not able to ascertain from your original post whether the Debit GL on the new record is always going to be 9300, or if not, how the appropriate number is to be determined (or where it is to be sourced.

If you choose to use the approach Bob outlined, you will still need to resolve this question (in fact there is a slight issue in that Bob's syntax sets both credit and debit numbers on the new record the same as the old record, but then tests for '9300' to end the loop, which would possibly keep creating new records forever, or would stop early if there were any 9300s in the found set).

If the Debit GL for the new record is always going to be 9300, the solution will be quite straightforward, however, as you'll be able to enter it as a literal value at line 7 of the script.

[LaRetta]

Yes I see it! And 9300 Debit GL will be on all items! Thanks to both of you for your help. I'm backed up ... wish me luck!

IT WORKED!!! Thank God for ya'all on the Forum