TOPIC:

We use our licensed Flowheater Designer very successfully on a daily basis to insertinto and update our database (currently 5.1 million plus rows).The updates are transaction driven and in two of these - NAMECHANGES and ADDRESS CHANGES – it would be very useful to be able to capture the
old data in two other tables immediately before the insert process.  Is this possible with Flowheater and, if so, whatwould be the recommended approach?  Volumes are modest (Names about 300 daily and Addresses 3,000 daily outof total update rows of 60K – 100K) so using LOOKUP seems like a sledgehammer
to open a nut; the input source is Excel (where we refine the raw input ready
for insert).Were we doing this work manually from the front end, then forNAMES CHANGES we would be using something along the lines of
$tblEvents = $dal->Table("Old_Name"); Thistable would have new Auto Increment PRIMARY KEY
$tblEvents->Value["Comp_Number"]=$oldvalues["Comp_Number"];(VARCHAR and original table PRIMARY KEY)
$tblEvents->Value["Comp_Name"]=$oldvalues["Comp_Name"];(VARCHAR)
$tblEvents->Value["Postcode"]=$oldvalues["Postcode"];
$tblEvents->Add();
but manual process times prevent this.  The ADDRESS CHANGES has more fields but isthe principles are identical.

Hi Frank,

Two solution are possible.

Fast solution)
In case of your database supports database triggers, you could define a “before update trigger”. In this trigger function, you copy the current content to another table before FlowHeater overrides this data with the new data.

Slow solution, with FlowHeater)
Because FlowHeater does not read the content of the current record before updating, you have the read the desired content in separate or tow processing step and check whether update or insert needed for this record.

Step 1) Read just the primary keys of your import/update source into a InMemory table .

Step 2) Read the Table content you wants to update and check whether the primary key exit in the InMemory table .  For this you have can use the SQL , Lookup or the String Replace Heater . Note this check is very fast! In case, the primary key doesn´t exits just filter out this record. In case you have a match write/insert the desired content into your new table.

Step 3) Update your content as usual

Many thanks for your thoughts which are very helpful.  Our preferred route is to use FlowHeater to we will set up and try the Slow solution when time permits later this month.  We will let you know how it works out.

Thanks again for your help.