| Subject | Re: autoincrement problem success? |
| From | Mervyn Bick <invalid@invalid.invalid> |
| Date | Fri, 20 Oct 2023 09:56:59 +0200 |
| Newsgroups | dbase.getting-started |
On 2023/10/20 08:04, Charlie wrote: > Hi Mervyn......I have this on open with a memory dbf... > > form.category1.rowset.locateOptions = 3 > FLTR = FORM.MEMO1.ROWSET.fields["mem"].value > form.category1.rowset.applyLocate( "cat = '" + fltr + "'" ) > form.category1.rowset.save() > > Is this what you are talking about? Yes. There are duplicate values in the SKU field in the coins table you supplied. If you save the value from the SKU field to the MEM field of your memo table you can't guarantee that your code will set the coins table rowpointer to the correct record. As you are not writing any data to the category table here, the save() is redundant. If you are going to use the SKU field as the primary key (after you've removed the duplicates :-) ) you should also change the locateOptions value to 0 i.e match case and length. As it stands, if you save BN1914P to the MEM field your code could (depending if an index on the field is active or not) actually find the record containing BN1914PX. The problem does not arise if you use the autoinc value as a search value as it is guaranteed to be unique and the setting for locateOptions will have no effect. > > I wrote a little program for trains that I converted to coins that is supposed to find duplicates in a field. Seems like it missed the ones you pointed out. I tried to run your form, but it looks like the duplicate results are already there. How does that work? The example program was intended to show that there were duplicate SKU values but using the autoinc value dBASE would go to a specific record. To check for duplicates I used a localSQL SELECT statement. Used in this manner the SELECT statement opens the rowset in a workarea the same way that the XDML command USE tablename does. USE on its own closes the workarea. select sku,count(sku) from coins group by sku having count(sku) > 1 list off all use Once I had identified the duplicates I was able to craft another localSQL SELECT statement which gave me the actual records containing the duplicates. select sku,lr from coins where sku = 'LC1909P' or sku = 'LC1909S' or sku = 'LC1922D' order by sku list off all use The two SELECT statements are a "quick and dirty" way of finding duplicates in a table. Now that you know where the duplicates are you can make the necessary changes if you want to continue using the SKU field as the primary key for the coins table. To prevent duplicates from being added later you could create a DISTINCT index on the field. In as much as all indexes are kept up to date in the production .MDX file if any data in a key field is changed this should work even if the DISTINCT index isn't active. While a DISTINCT index should prevent duplicates it will only recognise them at the end of a transaction when dBASE actually tries to write to the table. I would prefer to prefer to look for a duplicate using applyLocate() the moment a new value for the SKU field is entered. Mervyn | |