Creating a (sub)table from a form

[blue waters]

Hi all,

I have a form which allows a user select the range of records to be used in several calculations. The records are selected only from one main table. The filter query with the record ranges is saved in a variable called "query.filter_summary". I want to create a new table based on the record ranges selected by the user. Basically, I want to duplicate the main table but only with the user selected record range. Any suggestions?

Thanks,

Inanc

[Stan Mathews]

Re: Creating a (sub)table from a form

Probably easiest to duplicate the current table and then append the records you want. Append operations accept a filter.

[blue waters]

Re: Creating a (sub)table from a form

Probably easiest to duplicate the current table and then append the records you want. Append operations accept a filter.

Thank you very much Stan! You are saving my day here!

Inspired by your advice, I will clone the main table structure without any records and then append to the new table with the records I want. Here is the part of the code. I am just trying to create a new table and then fill it with records (no filtering yet) :

dim tbl as P
dim tbl_main as P
dim filename as C
filename = "C:\A5\new_table.dbf" 

tbl_main = table.current() 
tbl_main.duplicate(filename,3)

tbl=table.open(filename)
tbl.close()

append.t_db = tbl_main
append.m_key = "" 
append.t_key = "" 
append.m_filter = "" 
append.t_filter = "" 
append.type = "All" 
append.t_count = 0 
tbl.append()

However, I am getting an error:

tbl.append()
Type mismatch: variable append.t_db is of type Property variable, expected type Character

Any suggestions? Thanks!

Inanc

[Tbaker]

Re: Creating a (sub)table from a form

Try this

dim tbl as P
dim tbl_main as P
dim filename as C
dim tname as c
filename = "C:\a5\new_table.dbf"  'table to be created


tbl_main=table.current()
tname=a5.get_path()+"/"+dbf() 'gets the table path and the name of the table in table.current() and coverts it to a character path

tbl_main.duplicate(filename,3)  'duplicate the current table without records but with layouts/operations, field rules and indexes


tbl=table.open(filename)  'open the new table for append


append.t_db = tbl_main  'transaction table is table.current
append.m_key = "" 
append.t_key = "" 
append.m_filter = "" 
append.t_filter = "" 
append.type = "All" 
append.t_count = 0 
tbl.append(tname)  '  tname is the source of the append - in this case the current table


tbl.close()

Adding the source or transaction table's name was the only way I could get it to append to the new table.

FWIW

Tom

[blue waters]

Re: Creating a (sub)table from a form

Try this

.............

Adding the source or transaction table's name was the only way I could get it to append to the new table.

FWIW

Tom

Tom, thank you very much, it worked! Now I am at the next step, trying to append only the records that are filtered. I already have a filter expression working for the Report Preview which is called from a Form:

if Report_Summary= .T. then 
	:Report.Preview("Summary_Linked2",query.filter_summary,query.order)
end if

I am trying to use the same query filter (query.filter_summary) in this append operation. I used various combination such as

append.m_key = "" 
append.t_key = "" 
append.m_filter = query.filter_summary
append.t_filter = "" 
append.type = "Unique Only"

But every time, I just get a clone of the original table. Any suggestions for how to format the filtering part of the append function?

Thanks,

Inanc

[Stan Mathews]

Re: Creating a (sub)table from a form

append.m_key = "" 
append.t_key = "" 
append.m_filter = ""
append.t_filter = query.filter_summary
append.type = "Unique Only"

You need to filter which records from the transaction table will be appended. The master table is the destination and having no records originally can't be filtered.

[blue waters]

Re: Creating a (sub)table from a form

append.m_key = "" 
append.t_key = "" 
append.m_filter = ""
append.t_filter = query.filter_summary
append.type = "Unique Only"

You need to filter which records from the transaction table will be appended. The master table is the destination and having no records originally can't be filtered.

But Stan aren't we trying to bring in records into an empty table by filtering records from the transaction table? I am confused...

[Stan Mathews]

Re: Creating a (sub)table from a form

Yes, but your sample code had the filter on the master table....

append.m_key = "" 
append.t_key = "" 
append.[COLOR="Red"][B]m_filter [/B][/COLOR]= query.filter_summary
append.t_filter = "" 
append.type = "Unique Only"

[blue waters]

Re: Creating a (sub)table from a form

Thanks Stan. I missed what you changed in your code first time... However, I'm still having problems with filtering.

I changed the format little bit. When I created new tables, I was also getting new forms, reports etc... I am not sure why. So I decided to have a "fixed" sub table, but have its records get deleted when the form runs initially and then append the appropriate records with the filter.

dim tbl_sub as P
dim tbl_main as P
dim main_table as C
dim sub_table as C

main_table=table.actual_filename_get("research_table1") 'get the full filename for the main table
sub_table=table.actual_filename_get("query_table1") 'get the full filename for the sub table

tbl_main=table.current()
tbl=table.open(sub_table)
tbl.zap(.T.)   ' delte the records in the sub table

append.t_db = tbl_main  ' Set the main table as the transaction table = table.current
append.m_key = "" 
append.t_key = "" 
append.m_filter = ""
append.t_filter = query.filter_summary
append.type="Unique Only" 
append.t_count = 0 
tbl.append(main_table) 
tbl.close()

Stan, I made the changes you suggested for the filtering part. And also tried simpler filters but it has no effect on the append. It's as if filtering has no effect...

As an example in its simplest form for the query,

query.filter_summary= "RTS='rts1'"

This format works well in the following:

if Report_Summary= .T. then 
	:Report.Preview("Summary_Linked2",query.filter_summary,query.order)
end if

I am not sure what I am missing here!!!

Thanks,

Inanc

[Stan Mathews]

Re: Creating a (sub)table from a form

When I created new tables, I was also getting new forms, reports etc... I am not sure why.

tbl_main.duplicate(filename,3) 'duplicate the current table without records but with layouts/operations, field rules and indexes

tbl_main.duplicate(filename,9) 'duplicate table, field rules, and indexes

tbl.zap(.T.)   ' delte the records in the sub table
[COLOR="Red"]debug(1)[/COLOR]
append.t_db = tbl_main  ' Set the main table as the transaction table = table.current
append.m_key = "" 
append.t_key = "" 
append.m_filter = ""
append.t_filter = query.filter_summary

When the debugger opens type

query.filter_summary

into the example box, press Tab, the current value will show in the Value box. We can find out if the query.filter_summary still has the value we expect.

[blue waters]

Re: Creating a (sub)table from a form

...

When the debugger opens type

query.filter_summary

into the example box, press Tab, the current value will show in the Value box. We can find out if the query.filter_summary still has the value we expect.

Thanks Stan, now I got on the table duplication. Indeed, I was also copying operations too...

Regarding the debugger, I ran it and the filter value is still what I expected. Actually, right after the append operation a report preview opens up and I can see the values that are filtered. So the filter logic/syntax is working fine (in the report preview).

Am I missing something in the filter settings for the append operation?

Thanks,

Inanc

[Stan Mathews]

Re: Creating a (sub)table from a form

Shouldn't be any difference.

I think this

append.t_db = tbl_main

should be

append.t_db = main_table

I'd be glad to look at a sample database if you can create one.

[blue waters]

Re: Creating a (sub)table from a form

Shouldn't be any difference.

I think this

append.t_db = tbl_main

should be

append.t_db = main_table

I'd be glad to look at a sample database if you can create one.

Stan, thank you very much. I created a sample database with a similar form I use. Please look at the code in the form with the Code Explorer. This is giving me a different error this time. But I think you'll clearly see what I am trying to do... Thanks....

[Stan Mathews]

Re: Creating a (sub)table from a form

I find it easiest to design such an operation in the genie, capture the xbasic, then modify it.

[blue waters]

Re: Creating a (sub)table from a form

I find it easiest to design such an operation in the genie, capture the xbasic, then modify it.

STAN, THANK YOU VERY MUCH!!! It worked in my main application :) Thanks for the tip too. It sounds like similar to recording a macro in Excel and modifying the VBA...

Last question for you this time, is it possible to change the font size used in a table? I'm trying to increase the font size. I couldn't find a way of doing that.

Thanks.