add method/procedure

[Denis Jobin]

When using the following code:

----------------------------------------------------------------
loAppend = False
loUpdate = True

add(":priv:Answer", ":Charity:Donors", loAppend, loUpdate)
----------------------------------------------------------------

I get a Changed.db table which contains all the records form the
":Charity:Donors" table.

For each record in the Answer table, there is a record in the Donors table.
In fact the Answer table is more recent version of the Donors table, and it
has only a few records at a time which are different.

It looks like that even though all records but one or a few in the Answer
table are identical to the records in the Donors table, an entry is created
in the Changed table for all records in the Donors table, even if there has
been no change.

To me it would make more sense that only a few records would be inseted in
the Changed.db table, because there is only a few records different.

Any comments?

--
Denis Jobin

Toronto, Ontario, Canada
----------------------------------------
denis...@sympatico.ca

[Liz]

Denis,

I expect it doesn't look to see whether anything has changed
or not. It simply finds the corresponding record and
updates it. This would be _much_ faster than looking to see
whether it needs updating.

Liz

[Dennis Santoro]

Dennis,

>From the help:

When True, update compares records in both tables, and where key values match,
replaces the data in the destination table.

So I would expect you to get a large changed table since you have update to
true since by your setup you HAVE changed all the matching records.

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits since
1982

[Denis Jobin]

Dennis and Liz,

Thank you both for your comments. This is what I suspected. I will have to
figure out a way to only update the relevant records with some OPAL codes.

I guess I could have a TimeStamp field in both tables, scan the tables et
compare the value of TimeStamp to determine which records have changed.

I was hoping to have an easy way to do it by using the add procedure.

---------------------------------------------

Note to Dennis:

My name is spelled with one 'n' only, Denis. I am french. Don't worry I am
used to grtting the double 'n' and I am used to correct people. In fact,
when talking on the phone, I normally say that my name is spelled like
Dennis, but with only one n.

[Dennis Santoro]

Denis,

Re: the Dennis, I know that and usually don't make that mistake. But I am also
VERY used to typing it with 2 n's ;-)

Now, to the question: Why do you need to know which ones have ACTUALLY
changed? And if you really do, you can do so with a query that matches all
records in the target table against the ones in the source table. But it will
slow things down. If you need to tell us more.

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits
since 1982

[Denis Jobin]

Dennis,

Roughly, here is the situtation.

There are two databases which share the same list of names and addresses.
The two databases are not located on the same premises and in fact, for some
private and very good reasons, requested by the two clients using thoses
databases, cannot in any case be at the same location.
All data entry and maintenance of names and addresses is done in, DB1.
There is financial information which is processed and controlled by a
TransactionID created in DB1.
When one or more transactions are processed I update the DB2, through the
use of a Zip Disk that I carry with me.
I then use the Zip disk to update the DB2 located at the Client2 site.
The DB2 has some reporting needs which obviouly demand the acurate
information for the need to have any changes in addresses from DB1.

I know I do not need to, but just in case Client2 would want a list of any
changes in addresses in case of discripencies in the data information. Again
the client has not asked for it. I just want to be prepared if it is asked.
This is why I wanted a simple way of keeping track of changes without
spending hours on programming something that might not be even used.

I do not know which items have changed, and in terms of data, using the
add() procedure works fine, in the sense that it does update the records
each time correctly. And this is all that counts really.

I guess my best bet would be to keep track of changes in DB1 and then have
that file copy over whit the DB2 data. This way I would have the
information, if needed.

All of this to say I was just curious about trying to understand and explain
something I observed.

Denis Jobin

[Dennis Santoro]

Denis,

Given that you could do the change tracking from DB1 to BD2 with a combo of a
delete query and a not in query. Basically, if you match on all fields and do a
delete, what will be left is what changed. If you also do a not in query, your
result will be what was not already in (new) the dataset. And it could certainly
be automated.

I think you can work out the details but if you want help let us know.

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits since
1982

[Denis Jobin]

Dennis,

> Given that you could do the change tracking from DB1 to BD2 with a combo
of a
> delete query and a not in query. Basically, if you match on all fields and
do a
> delete, what will be left is what changed. If you also do a not in query,
your
> result will be what was not already in (new) the dataset. And it could
certainly
> be automated.
>

It makes sense. I will try it.

I guess I could do it all with a delete query, since the records left would
include both new and changed records.

Then I would just execute the add(table1, table 2, True, True)

Thank you for the advice.

[Dennis Santoro]

Always glad to help a fellow Denis (even if you are short an n!) ;-)

Denn Santoro
President
Resource Development Associates
http://www.RDAWorldWide.Com
Offices in the United States and Germany
Providing solutions to health care, business, governments and non-profits since
1982