Can't append copy of data

[Ted Giles]

Hi, More append troubles.

I need to append data to a dataset with exactly the same format. It is a direct copy of the dataset.

When I save data from the main database with a different dbf name, I cannot append it without getting the error;

a_tbl.append()
FIELD-AND EXPRESSION TYPES MUST MATCH

If I append a dataset with the same name, it all works fine.

Short of renaming the dataset, I haven't a clue.

Script follows, and any guidance would be appreciated.

Ted Giles
>>>>>>>>>>>>

a_tbl = table.open("c:program filesa5v4database marketingdatabase data.dbf")
table_to_append = ui_get_file("Open Database to Import","Table(*.DBF)","","X")
IF table_to_append="" THEN
END
END IF

append.t_db = table_to_append
append.m_key = ""
append.t_key = ""
append.m_filter = ""
append.t_filter = ""
append.type = "All"
append.m_count = 26
append.m_field1 = "Title"
append.m_exp1 = "Database_Data1->Title"
append.m_field2 = "First_Name"
append.m_exp2 = "Database_Data1->First_Name"
append.m_field3 = "Initial"
append.m_exp3 = "Database_Data1->Initial"
append.m_field4 = "Surname"
append.m_exp4 = "Database_Data1->Surname"
append.m_field5 = "Job_Title"
append.m_exp5 = "Database_Data1->Job_Title"
append.m_field6 = "Company_Name"
append.m_exp6 = "Database_Data1->Company_Name"
append.m_field7 = "Address_Line_1"
append.m_exp7 = "Database_Data1->Address_Line_1"
append.m_field8 = "Address_Line_2"
append.m_exp8 = "Database_Data1->Address_Line_2"
append.m_field9 = "Address_Line_3"
append.m_exp9 = "Database_Data1->Address_Line_3"
append.m_field10 = "Town_City"
append.m_exp10 = "Database_Data1->Town_City"
append.m_field11 = "County"
append.m_exp11 = "Database_Data1->County"
append.m_field12 = "Postcode"
append.m_exp12 = "Database_Data1->Postcode"
append.m_field13 = "Country"
append.m_exp13 = "Database_Data1->Country"
append.m_field14 = "Tel_No_Dial_Code"
append.m_exp14 = "Database_Data1->Tel_No_Dial_Code"
append.m_field15 = "Tel_No_Number"
append.m_exp15 = "Database_Data1->Tel_No_Number"
append.m_field16 = "Fax_No_Dial_Code"
append.m_exp16 = "Database_Data1->Fax_No_Dial_Code"
append.m_field17 = "Fax_No_Number"
append.m_exp17 = "Database_Data1->Fax_No_Number"
append.m_field18 = "E_Mail"
append.m_exp18 = "Database_Data1->E_Mail"
append.m_field19 = "Mobile_Dial_Code"
append.m_exp19 = "Database_Data1->Mobile_Dial_Code"
append.m_field20 = "Mobile_Number"
append.m_exp20 = "Database_Data1->Mobile_Number"
append.m_field21 = "Web_Page"
append.m_exp21 = "Database_Data1->Web_Page"
append.m_field22 = "Exhibition_Name"
append.m_exp22 = "Database_Data1->Exhibition_Name"
append.m_field23 = "Exhibition_Date"
append.m_exp23 = "Database_Data1->Exhibition_Date"
append.m_field24 = "List_Number"
append.m_exp24 = "Database_Data1->List_Number"
append.m_field25 = "Short_Pcode"
append.m_exp25 = "Database_Data1->Short_Pcode"
append.m_field26 = "Dummy"
append.m_exp26 = "Database_Data1->Dummy"
append.t_count = 0
a_tbl.append()
ui_msg_box("Operation Complete","Records Added")

[Stan Mathews]

RE: Can't append copy of data

Ted,

The best I can tell you are "changing horses" in your script. You refer to "database data.dbf" in your table.open() command but change this to "Database_Data1" in your expression statements. This is not allowed in an append.

"When I save data from the main database with a different dbf name, I cannot append it without getting the error;"
Saving data from the main database with a different .dbf name seems more like a copy operation than an append.

[Ted Giles]

RE: Can't append copy of data

Hi Stan,
It's two operations.

The first is to copy data from one database, ( to a diskette say ), and the next is to append that data to the main database, thereby increasing the size of the database.

The plan was (is) to be able to reference the name through UI prompt.

You have a good point thought, that I have not changed the table references throughout the script to identlfy the UI table name.

Regards, Ted.

[Finian Lennon]

RE: Can't append copy of data

If the databases have identical formats you can avoid the append entirely by using the record_data_get and record_data_set methods.

t1=table.open("append_from_table")
t2=table.open("append_to_table")

t1.fetch_first()
while .not. t1.fetch_eof()
data=t1.record_data_get()
t2.enter_begin()
t2.record_data_set(data)
t2.enter_end(.T.)
t1.fetch_next()
end while

t1.close()
t2.close()

which is easier to write or edit from the script recorder. If the tables are big you could use a batch_begin/batch_end to speed things up.

Finian

[Stan Mathews]

RE: Can't append copy of data

Cool.

[Ted Giles]

RE: Can't append copy of data

Sounds good to me, many thanks, will try today

[Ted Giles]

RE: Can't append copy of data

Hi Finian,

Used the suggested method and it works fine now.

That is what I've delivered.

Problem is that even with batch begin and end, it works so much more slowly than a simple append.

Any alternatives you can think of?

Regards, Ted.

[Finian Lennon]

RE: Can't append copy of data

First I'd check to make sure that you have the batch_begin and batch_end commands in the proper places. You may need two sets of commands, one for each table open.

If you tried that already, and it's painfully slow, then you're back to modifying the append to do what you want. Since you are creating new files etc, you need to parse the append lines properly with the correct path and filename variables inserted. I may have some examples somewhere I could send you.

Finian

[Ted Giles]

RE: Can't append copy of data

I'll try more batch commands first, but I'd appreciate help with the problem of opening a file and then appending it. The files are exactly the same, as one system exports the data set, and the other appends it.

It all goes fine until you add the file.get function, then pear shaped.

Regards, Ted

[Finian Lennon]

RE: Can't append copy of data

Ted:

Your initial attempt at the append didn't work because you didn't have the basic append syntax correct. The append field expression contains the "@" symbol which you didn't have .... I didn't look much more closely than that.

Here's a script that I've used to get a table name to append to the current table; it happens that the file is located in a sub-folder of the main database folder called "OSOMBATCH" so you'll have to modify the script for your path situation.

The other thing about this script is that, in order to create the proper string for the transaction table expression (since it's located in a sub-folder) I came up with the awkward expression for the "Fileprefix" variable. There's probably an easier way to put the string together but this one does work. I think it should work for the curreent folder also.

I chopped out the repetitive lines and you'll want to correct the line endings etc. but it may help you do what you want.

Finian

'======
dim app_path as c
app_path=:A5.Get_Master_Path()+chr(92)
response=ui_msg_box("RETRIEVE A SAVED BATCH?", "If you proceed, the current batch will be deleted and the saved copy you select will become the current batch.",49)
if response=2 then
end
end if
path=app_path+"OSOMBATCH*.dbf"
filename = ui_get_file("Retrieve From:","Tables (*.DBF)",path,"X")
IF filename = "" THEN
END
END IF
hourglass_cursor(.T.)
fileprefix="@"+substr(alltrim(filename),at(CHR(92),filename,-1)+1,
(len(alltrim(filename))-4)-(at(CHR(92),filename,-1)))+"->"

t=table.current()
t.index_primary_put()
t.fetch_first()
while .not. t.fetch_eof()
t.change_begin()
deletion_field=t.field_get(0) 'get pointer to deletion field of the table
deletion_field.value_put("*") 'deletes the record
t.change_end(.T.)
t.fetch_first()
end while

power_tbl=table.current()
append.t_db = filename
append.m_key = ""
append.t_key = ""
append.m_filter = ""
append.t_filter = ""
append.type = "All"
append.m_count = 95
append.m_field1 = "PROVIDER"
append.m_exp1 = ""+fileprefix+"PROVIDER"
append.m_field2 = "PROVIDER_NUMBER"
append.m_exp2 = ""+fileprefix+"PROVIDER_NUMBER"
append.m_field3 = "ICD1"
append.m_exp3 = ""+fileprefix+"ICD1"
... ...
... ...
append.m_field95 = "PROCTNUMSONLY"
append.m_exp95 = ""+fileprefix+"PROCTNUMSONLY"
append.t_count = 0
power_tbl.append()

power_tbl.index_primary_put("patname")
power_tbl.fetch_first()
hourglass_cursor(.F.)
parentform:browse1.refresh()
parentform.resynch()
'======

[Ted Giles]

RE: Can't append copy of data

Hi Finian,

Cracked it with your suggestion of putting more than one batch_begin/end.

Thanks for the trouble you went to with the attached script.

Anything you need to know about restoring old cars, motor bikes or welding; please contact me!!

[email protected]

[Finian Lennon]

RE: Can't append copy of data

I thought the second set of batch commands might do it. I'm running other appends this way and they are lightning fast.

Now, I have this old Toyota ......

Good luck!

Finian