APPEND and UPDATE. Get's a bit complicated ... at least to me.

[Eric Johnson]

I want to perform an operation(s) that will add or update a record in a table.

Here's the case. I have the health data of a breed of dogs for the past 17 years. It is in the form of quarterly tables so ... 68 tables. Some of the data represents a single result such as a hip x-ray which is graded and the grade is good for the life of the dog. Several other tests are the same. However, one test must be repeated every 15 months.

I thought I could take the oldest file and declare it to be the "Master file". Then I would take each successive quarterly table and use it as a "transaction table" to POST the Master table. However, the POST command doesn't seem to have the ability to "replace" a record in the Master table with a record from the Transaction table.

So, I've now hit on the idea of pre-processing the transaction table to split the table into two tables, one that contains records that will be APPENDed to the Master table and one that will UPDATE records. Then run the APPEND table against the Master table and the UPDATE table against the Master table, Then toss those 2 tables and pre-process the next quarterly table and use these to APPEND/UPDATE the Master.

However, this brings up one case. What can be done about the first instance of an eye exam, the exam that must be done at least every 15 months? The eye exam would normally be handled by the UPDATE process but the first time there's nothing to UPDATE. Do I need to run the UPDATE process first and trap those transaction table records that won't UPDATE to submit to the APPEND process?

Also, can all these by written as one process or do they need to be separate processes.

[Stan Mathews]

Re: APPEND and UPDATE. Get's a bit complicated ... at least to me.

The append operation has an option for unique only meaning it appends only records with unique linking field values. That takes care of first visit records. All your processes need to be separate although you can trigger them all with one button.

[Eric Johnson]

Re: APPEND and UPDATE. Get's a bit complicated ... at least to me.

Thanks Stan. I'm going to keep this as a Plan B. I've just realized that I can generate a search of the other site/database from a dog's record in my database and get the info >presto<. The rreal advantage is that we'll always be current rather than waiting until the next quarter. However, thanks again, I'll use it someday.

Eric

[DaveM]

Re: APPEND and UPDATE. Get's a bit complicated ... at least to me.

in operations, you have append that adds records to a table from another table.
You also have a post which will post records to a table from another table. You can choose which records you wish to post to and from. It is a lot faster than update in most cases and depending the use. I just did that where I had done it with update and used lookup() to get the values from another table that took 3.5 hours and using the post method took that to about 12 minutes. Numbers of records was over 100 k and 38 fields involved(some empty).

[Al Buchholz]

Re: APPEND and UPDATE. Get's a bit complicated ... at least to me.

I just did that where I had done it with update and used lookup() to get the values from another table that took 3.5 hours and using the post method took that to about 12 minutes. Numbers of records was over 100 k and 38 fields involved(some empty).

Dave

Your example of the update and lookup() that took 3.5 hours needs to get broken down to what caused the long execution.

The update was fast, the lookup() was what slowed it.

Using a set defined to make the update would make it much faster.

Each tool has it's place and lookups with an operation is a mixture for performance issues.

As Stan noted, Eric's situation is best served by the append - add unique, update existing model.

[DaveM]

Re: APPEND and UPDATE. Get's a bit complicated ... at least to me.

Al,

My situation was 37 empty fileds after an append. The other 37 needed to be filled from a 3rd table. This would have been simple if for my own use, but will be done 1500 miles from here on different computers.
I can't create a set on the companies old db for this purpose, so I have a partially filled table and another table with the records that need to go into it.

involves 3 tables tablea is final product. Tablec is the one contains the other field data I need. sever has original tables I need records from

append old data from server:
append to tablea from same on server
append tablec from same on server
post data to table a from tablec to tablea
Table a now has all "complete" records.

The original tablea on server only has, say, 30 fields.
The original tablec on server only has, say, 42 fields.


My tablea has about 70 fields when done
I first get the 30 from server to tablea and 42 from server to tablec, Then post 37 from tablec to tablea

I am done.

Appending(not unique) 65+ tables, doing some mods, updating a few and posting takes 41.00(approx.) minutes which is livable. 4 hours was a stretch.

The new and revised tables go to the new server completely empty and the reason for all the appends and work.
Client is ok to 1.5 hours for this.

I am actually combining two tables into one, but found the operations to do that(like join) were not solid enough. May have been me. This has to be exact when done.

The code for the post looks something like this:

a_tbl = table.open("tops")

post.t_db = "prop"
post.m_key = "File_Num"
post.t_key = "File_Num"
post.m_filter = ""
post.t_filter = ""
post.m_count = 37
post.m_field1 = "Address"
post.m_exp1 = "@PROPERTY->Address"
post.m_field2 = "Address2"
post.m_exp2 = "@PROPERTY->Address2"
post.m_field3 = "First_Name"
post.m_exp3 = "@PROPERTY->First_Name"
post.m_field4 = "Last_Name"
post.m_exp4 = "@PROPERTY->Last_Name"
post.m_field5 = "City"
post.m_exp5 = "@PROPERTY->City"
post.m_field6 = "Zip"
post.m_exp6 = "@PROPERTY->Zip"
post.m_field7 = "Province"