Creating records automatically 1

[andwye]

I manage lease contracts using Paradox 9.0 to track rentals received and future rentals due. At present I have to individually input each rental due, a laborious exercise if there are 84 records for each lease contract!
Can anyone think of a way to automatically produce records based on set criteria?
For example, the set criteria would be : Lease Number, Start Date, Number of Payments, Frequency of Payments (monthly or quarterly), Rental.
The table would then look like this:
1 01/01/2006 100.00
2 02/01/2006 100.00
3 03/01/2006 100.00 and so on for 84 records.
This then appears in an existing table where the primary key is an autoincrement which I use for a unique invoice number.
Andrew Wyeth

 

[TonyMcGuire]

AutoIncrement as a key is a REALLY BAD idea.

If the table becomes corrupted, and AutoIncrement is known to corrupt, you have a big job on your hands - the larger the table and more involved the app, the bigger the hassle.

Create your own, instead. The below, in a library, will handle virtually unlimited tables and create a key generation system for each.

============================
Unique invoice number

UniqueID.db -> tablename uniqueID

method getUnique(stTableName string, var liUnique longint, var stReturn string) logical
var
  tcUnique        tcursor
endvar
if not tcUnique.open(":aliasname:uniqueID.db") then
  stReturn=errormessage()
  errorclear()
  return False
endif
if not tcUnique.qlocate(upper(stTableName)) then
  tcUnique.edit()
  tcUnique.insertafterrecord()
  tcUnique."tablename"=upper(stTableName)
  tcUnique."uniqueID"=1000 ; default value, set as you wish
  tcUnique.unlockrecord()
  tcUnique.qlocate(upper(stTableName))
endif
tcUnique.edit()
if not tcUnique.lockrecord() then
  tcUnique.close()
  stReturn="Could not lock Program's record."
  return False
endif
liUnique=tcUnique."uniqueID"+1
tcUnique."uniqueID"=liUnique
if not tcUnique.unlockrecord() then
  stReturn=errormessage()
  tcUnique.canceledit()
  return False
else
  tcUnique.endedit()
endif
tcUnique.close()
return True
endMethod

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[TonyMcGuire]

As to adding those records otherwise, modify the below to suit your needs.

var
  cDate,
  sDate       date
  arDate      array[] date
  li          longint
endvar

sDate=(today()-day(today()))+1 ; starting point

for li from 1 to 84
  cDate=((sDate+35)-(day(sDate+35)))+1
  sDate=cDate
  arDate.addlast(sDate)
endfor

for li from 1 to arDate.size()
  tc.insertafterrecord() ; you need to already open() and edit() the tcursor
  tc."payDate"=arDate[li]
  tc."payment"=100
  if not tc.unlockrecord() then
    ; handle error
  endif
endfor

Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[andwye]

It's come as a bit of a shock to discover that autoincrement is hopelessly flawed. I'm in the process of backing up the database before altering one of my table structures. I imagine that changing the field from autoincrement to either alpha or numeric will wipe out the existing data. The last time I had to do this I exported the original file to excel and then imported it back again when the new structure was in place, not perfect because a number of field types disappeared in the export process. Do you have any suggestions on how to overcome this or am I worrying about nothing as the alteration will not affect the existing data?

 

[TonyMcGuire]

Changing AutoIncrement to LongInt preserves the current info.

Then, you can start the UniqueID numbering from where you left off (with AI).




Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[andwye]

Tony,

Thanks for the valuable advice. Without this forum I'd have long ago given up. As it is I've been able to build a solution that meets almost all of my business needs.

I'll work on your suggestions over the weekend and ask questions on Monday if I run into difficulties.

Andrew Wyeth

 

[TonyMcGuire]

For simple counter purposes, AutoIncrement can be just fine. You can usually recover from a failure fairly easy.

But using it as a primary key, or potentially even a link to a secondary, can be disastrous.

If your table becomes corrupted, and the AI loses track, it winds up trying to add a value already in the table to a new record. This can then create child table records with an incorrect key/link back to the master. Recovering from this can be nasty, as MANY have testified to.

Some use AI for non-critical stuff within a table.

I stopped using AI long ago, when the scenario above presented itself, and I became aware of the issues surrounding it.


Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[TonyMcGuire]

You can also add to the routine I posted.

Simply add a fieldname column to the uniqueID table.

Then add a parameter to the method, and pass both tablename and fieldname when executing.

Do a qlocate that includes both table and field names (key on both).

You then have a unique number generator for both table and multiple fields within the table.

Just in case someone needs this, and hadn't thought of it.




Tony McGuire

http://www.thedbcommunity.com

"It's not about having enough time. It's about priorities.

 

[andwye]

Tony, I've not used library code before. How do I get the table (within a form) to access the library to request the next invoice number (uniqueID). Some additional info: I have a customer record and linked to the customer record is the customer's payment record. The customer's payment record has an invoice number for each rental that is due. The next customer record is easy because I use a push button to create the next record using Cmax. But I can't figure out how to call up the UniqueID when inputing each new payment record on the table.
Andrew Wyeth