Writing Code in Runtime?

You can create and run code ('prg' files) in run time based on existing data or user input by building the code programmatically within one or more text fields in a table and then saving it as a prg file.

For example:

Task: Sum or Average two numerical fields in a table for each record depending on user input into a form.

Table:

ID Balance Payment Bill BalFlag PayFlag BillFlag
01 $100.00 $ 10.10 $89.90
02 $ 50.00 $ 7.50 $42.50
03 $ 3.28 $ 0.01 $ 3.27

User input via form selects to create a summary of Balance and Bill using Check Boxes. Thus our table now looks like this:

ID Balance Payment Bill BalFlag PayFlag BillFlag
01 $100.00 $ 10.10 $89.90 1 1
02 $ 50.00 $ 7.50 $42.50
03 $ 3.28 $ 0.01 $ 3.27 1 1


We can now write our runtime code:
************************************************************
alter table tablename add column programtxt c (200)

replace programtxt with "select distinct id,sum(balance);
as BalSum, avg(balance) as BalAvg, sum(bill) as BillSum,;
avg(bill) as BillAvg group by 1 from paytable into;
table temp"+alltrim(id) for balflag=1 and billflag=1

replace programtxt with "select distinct id,sum(balance);
as BalSum, avg(balance) as BalAvg, sum(payment) as;
PmtSum, avg(payment) as PmtAvg group by 1 from paytable;
into table temp"+alltrim(id) for balflag=1 and pmtflag=1


*****
*note that the SQL statement must fit within the field
*unless you want to populate into two or more fields and
*concatenate them in the last step using the command:
*copy fields programtxt1,programtxt2 to runtimecode.prg type sdf.
*****

copy field programtxt to runtimecode.prg type sdf for;
programtxt#" "

alter table tablename drop column programtxt

compile runtimecode

do runtimecode

************************************************************
You can also write multiple line code to manipulate or report on each record using the same method but additionally copying each program line 'column' to a temporary table, assigning a line order (e.g. "1,4,7" for a "use table" command, a "2,5,8" for a "count(*)" and a "3,6,9" for a "close table"), appending all of the temporary tables together and then sorting them before creating, compiling and running the prg.

I want to apologize for not following any particular programming conventions. As a business user who writes programs (i.e. not a programmer), my focus is to get things done, not to do them prettily. The basic premise in this FAQ can be used for some pretty useful stuff. For example, I have used it to create self-formatting Excel output with multiple columns, over thirty different user defined formats that can be assigned on a cell level, user defined headings and data sources and multiple summary operations.

I hope that this will make someone's life a little easier