Creating Table from Script

[Peter Lear]

Hi

I am trying to generate a script to record the history of a number of different amounts paid on different dates.

I want to be able to add a field to the table at each run of the script, and I want the field name to be the date of the payment. I am perplexed by this - I can use the table.create routines. But i cannot see how to add to it next time round. And i can't see how to get the field title to be derived from ui_get_text() function.

Am I asking for the impossible here?

Rev Peter Lear

[Stan Mathews]

RE: Creating Table from Script

To add a field you will need to create a new table each time, add all fields in the present table, add your new field, append all records from the old table, then add your new data.


I believe you would be advised to try to rethink your process and add records to a standard history table rather than modify the table each time. The capacity to add records far exceeds the capacity to add fields.

[nick marodis]

RE: Creating Table from Script

Like usual Stan's advice is correct there is a limit to the number of fields something like 1024, and only a 2Gig limit for file size. As Stan suggessted you may want to rethink the design of your app. However if you want info on how you way add a field to a table using xbasic go to Dr. Waynes web site for an article on the subject.

Regards
Nick

[Peter Lear]

RE: Creating Table from Script

Thanks to both

I have heard of 'history tables' but not yet got into one. I was thinking like a spreadsheet, where I add a new column headed 'date of payment' and the date is put there too, and the amounts are entered down the column.

Back to the drawing board - sigh

Peter

[Stan Mathews]

RE: Creating Table from Script

Think down, not across.

You would have table fields (columns) named for the data you wish to store. One field should/would identify the payor, another the date of payment, another the amount.

As you receive payments you add new records (rows) to the table and fill in the "standard" columns with data.

If you are concerned with totaling "payments made on a certain date", this is what filters are designed to do. You have several options as to where to derive/input the desired date. Then you filter the report/form/browse to only use records where the payment date matches what you have input.

This also allows you to define operations to copy records (rows) to other tables, for example you might want to copy all history records for 1995 to a "hist1995" table, then remove them from the main history table - when you no longer need to refer to them as you might 1999 history records.