Sorry... I did not read what precedes, but wouldn't the "right" way to do this be a (business) transaction whereby the quantity on hand gets subracted from 1234A and added to 1234?




>>>Hi,
>>>
>>>I am considering a feature for my app that would allow user combining two records into one. For example, say in the parts table they have two pars "1234" and "1234A" and at some point (after years of use) they realize that this is one and the same part. They can't rename one into the other because another record of unique entry already exists. They can't delete one because it has child records (transactions, P.O.s etc.). So I am thinking of writing a routine that would do it in code. Does it make sense? What I mean is, do others (as other developers on this message board) have routines that allow combining two records with different values in unique field into one? TIA.
>>
>>
>>Why don't you introduce a new table of merged parts, which holds previous part number combinations that were merged into a single part number? That way the user can merge the old parts into one, and then have a button that says "Show Merges" to see if there were prior histories. A recursive search to exhaust the trail back to the point of no prior merges would reveal a list of all merges which took place from any future part number.
>>
>>In this way you maintain current system integrity, and provide the ability to look back at the time it's needed ... during research.
>
>Thank you for your suggestion, Rick. I will consider this approach. However, I find that people (at least in my customer base) do not want to spend more than a minute of extra time to review before executing. The trend is to get things done as quickly as possible with a minimum thinking <g>. For example, for the case I described above (in the initial message) the user simply wants to get rid of one part number record and if there are any histories of this part, "move" them to another part. So the way I will probably do it is as following:
>1. Once user indicates that he/she wants to merge all history of part "1234A" into history of part "1234" my code will UPDATE every table where the part is used with the following command:
>

>update <TableName> set PartNo = '1234' where PartNo = '1234A'
>

>2. The part number '1234A' is not deleted but then user (if he/she wants) can delete this record. Since there will be no child records of this part the program will allow that.
>Of course, there will be a record added to the Audit Trail table of the UPDATEs done one tables. And if user wants to delete the resulting record of part '1234A' a record goes to the audit trail table too.