Create and Break Down a New Table from another Table

[Rudy16]

Hi folks,
Been quite a while since I used this forum however I could use a little guideance on this issue.

I have a table (Table A) with 5 fields, Numb_1 through Numb_5. On the maintenance form for this table I'd like to have a button, when pressed, create a new table (Table B) which has 1 field called Numb. So, for each record from Table A, create 5 records for Table B.

I the want to analyize Table B and come up with a report that shows how many times a number occurs, such as Number 1 occured 50 times throught Table A, Number 2 occurs 80 times,, and so forth.

Thanks,
Rudy

[Stan Mathews]

Re: Create and Break Down a New Table from another Table

Try this button code.

fields = <<%a%
numb,n,12,2
%a%
create_table("c:\temp\newtable.dbf", fields)
tbl_dest = table.open("c:\temp\newtable.dbf")
tbl = table.current()
tbl.fetch_first()
while .not tbl.fetch_eof()
for i = 1 to 5
tbl_dest.enter_begin()
tbl_dest.numb = eval("tbl.numb_"+allltrim(str(i)))
tbl_dest.enter_end(.T.)
next i
tbl.fetch_next() 'moved this line
end while
tbl.close()
tbl_dest.close()

[Rudy16]

Re: Create and Break Down a New Table from another Table

Hi Stan,
Modified code to try and suite my objective but kept getting "Not an Open Table". The only way to break out of it was it Task Manager.

Can you take a quick look at the situation. I've attached the app which I just started.

Thanks,
Rudy

[Stan Mathews]

Re: Create and Break Down a New Table from another Table

Remove the line

tbl.close()


It is trying to close the table upon which the form is based. That's what I get for trying to to code in the forum window rather than in Alpha.

Here's my original attempt that will work with your sample table

parentform.commit()
fields = <<%a%
numb,n,12,2
%a%
create_table("c:\temp\newtable.dbf", fields)
tbl_dest = table.open("c:\temp\newtable.dbf")
tbl = table.current()
tbl.fetch_first()
while .not. tbl.fetch_eof()
for i = 1 to 6
tbl_dest.enter_begin()
tbl_dest.numb = eval("tbl.numb_"+alltrim(str(i)))
tbl_dest.enter_end(.T.)
next i
tbl.fetch_next() 'moved this line
end while
tbl_dest.close()

[Rudy16]

Re: Create and Break Down a New Table from another Table

Thanks Stan.

Script almost working. Even though Create is used, it appends the data if I run it more than once instead of clearing the DB and re-creating it. Then at job end I get Invalid Field Name however it does not tell me a line number in the script.

Attached is the latest script used.

[Stan Mathews]

Re: Create and Break Down a New Table from another Table

If you specify a field as numeric you must specify the decimal places. If you want none, you still must specify that.

Fields = <<%a%
numb,n,2[COLOR="Red"],0[/COLOR]
'%a%

If you want to remove the old table, just delete it if it exists. (Put this code at the very start of the button code.)

if file.exists(a5.Get_Path()+chr(92)+"single_numb_tbl.dbf")
TABLE.ERASE(a5.Get_Path()+chr(92)+"single_numb_tbl.dbf", .t. )
end if

[Rudy16]

Re: Create and Break Down a New Table from another Table

Thanks again for your suggestion and time on this issue. I decided to use A5_Zap_Table() instead of TABLE.ERASE. Worked like a charm.

[Rudy16]

Re: Create and Break Down a New Table from another Table

Hey Stan,

I spoke too soon. I still get the Invalid Field Name. I beleive it's the ' from the '%a% line. If I remove the ' and make it just %a%, it wipes out my reports. Step-2 on the form executes the code.

Can you take a quick look.

Thanks,
Rudy

[Stan Mathews]

Re: Create and Break Down a New Table from another Table

The ' is incorrect as is

tbl_dest = table.close()

should be

tbl_dest.close()

I don't see the report being wiped out when I make those corrections.

Then again, since we have created the single_numb_tbl once, we don't need to create it again.

The code

Fields = <<%a%
numbx,n,2,0
%a%
create_table("c:\NJLotteryDB\single_numb_tbl.dbf", Fields)

can be removed from the script entirely since we know it exists and has been zapped..

[Rudy16]

Re: Create and Break Down a New Table from another Table

The ' is incorrect as is

tbl_dest = table.close()

should be

tbl_dest.close()

I don't see the report being wiped out when I make those corrections.

Then again, since we have created the single_numb_tbl once, we don't need to create it again.

The code

Fields = <<%a%
numbx,n,2,0
%a%
create_table("c:\NJLotteryDB\single_numb_tbl.dbf", Fields)

can be removed from the script entirely since we know it exists and has been zapped..

[Rudy16]

Re: Create and Break Down a New Table from another Table

I removed that code and the report did not disappear when I ran Step-2. Thanks again for your wisdom.

Rudy