The version I am working with is VAM/LAN 310, probably from around 1998. All of the major keys are just on the column name, e.g. cvendno, ccustno, citemno. There are no filters. I don't know if they changed the index definitions after that. They are my only VAM lan client, the rest are VAM/SQL. In any event, I am in the process of converting them to VAM/SQL, I am doing this keychange first, so I won't have to be concerned with it once I make the conversion.
Thanks for the heads-up. I will look into it.
>You may want to check with VAM on this and see that you have the most recent version. We ran accross this as a bug about 1997 or so and I believe they subesequently fixed it. (don't remember the exact field but believe it was the customer id.)
>
>>Jayesh,
>>
>>Thanks. I realized that you weren't specifically referring to SET DELETED, but more generally to the principal that the uniqueness violation will occur even based on the existence of a deleted record. I realized that you were referring to adding a filtered index. However, as I said in my post, I want to avoid changing the index expression. I may try it just as an exercise to see if it works - I'm sure it will - I can let you know.
>>
>>But in my case, I can accomplish the same effect by SETTING DELETED OFF, and then testing separately for the deleted status.
>>
>>Regardless, you pointed me in the right direction and helped me solve the problem, which I am very grateful for. So thanks again.
>>
>>>I am not referring to SET DELETED command. As you might know that when record is deleted from dbf, it not *physically* removed, it is marked as "Deleted". Unique Index doesn't consider this by default. So, you have specify "Filter" condition with that index. You can do this with Table Designer, Click on the Filter button for the unique index and put "!DELETED()" and see if it works.
>>>
>>>
>>>
>>>
>>>
>>>>Jayesh,
>>>>
>>>>No I don't. The index is a primary index on just citemno.
>>>>
>>>>I'm fairly certain that you have found my problem for me. I didn't realize that attempting to insert a value that was already in the table, but marked as deleted, would cause the error. After I got your post I tested it and realized that VFP will generate the error, regardless of the setting of SET DELETED. I assume this is what you were getting at?
>>>>
>>>>I don't think it is feasible to change the index expression. This code is for a change to Accountmate, a large accounting system with lots of routines that use that index. I don't know what the consequence would be of changing the index.
>>>>
>>>>So I think that I will just SET DELETED OFF while running this routine, then do my seek, and if the seek is successful, then test if the value is deleted, if it is, then I might just rename the deleted key value to something unique, or, not allow the change. Now that I understand the issue I have to think about how to handle it.
>>>>
>>>>
>>>>Thanks for your help!!!
>>>>
>>>>
>>>>
>>>>>Do you have !DELETED() filter on the unique index?
>>>>>
>>>>>>On the code below, which ran against 600 rows in the curresult table, for one entry, I received the error: Uniqueness of index xitemno will be violated
>>>>>>I can't see how this error could happen in this context. Maybe I am overlooking something simple, or there is some simple logic error (I was up late last night!), but I can't see how it could happen unless the seek failed erroneously. This code was running on my test machine, in a standalone environment. I can see how it would happen in a multiuser environment, but not here.
>>>>>>(See notes in code below)
>>>>>>
>>>>>>Thanks in advance for any suggestions.
>>>>>>
>>>>>>
>>>>>>
>>>>>>Case alltrim(curResult.tablename)=='icitem'
>>>>>> Select 0
>>>>>> Use (curResult.tablename)
>>>>>>
>>>>>> ** find the original item
>>>>>> If seek(lcolditemno,'icitem', 'xitemno')
>>>>>>
>>>>>> lnrecpointer=recno('icitem')
>>>>>> ** see if the new item exists already
>>>>>> If not seek(lcnewitemno,'icitem', 'xitemno')
>>>>>> ** if the new item doesn't exist already,
>>>>>> *go back to the original, and
>>>>>> **change the old to the new
>>>>>> Goto lnrecpointer in icitem
>>>>>>
>>>>>> *** got error message here on this replace. I have
>>>>>> *** looked at this code a million times, and
>>>>>> ** can not figure out how it could fail
>>>>>> ** unless the seek failed when it really should not
>>>>>> **have
>>>>>> Replace icitem.citemno with lcnewitemno in icitem
>>>>>> Else
>>>>>> Wait wind 'Item ' + lcnewitemno + ;
>>>>>> ' already exists in icitem ' + chr(13) + ;
>>>>>> 'Item ' + lcolditemno + ' will be ' + ;
>>>>>> ' replaced with ' + lcnewitemno + ;
>>>>>> ' in transaction detail lines, and ' +
>>>>>> lcolditemno + ;
>>>>>> ' will be deleted from the icitem
>>>>>> table ' nowait
>>>>>> Goto lnrecpointer in icitem
>>>>>> Delete in icitem
>>>>>> Endif
>>>>>> Else
>>>>>> Wait wind 'Error: original item not found in change list -
>>>>>> skipping ' nowait
>>>>>> Endif
>>>>>> Use in icitem
>>>>>>
>>>>>>
>>>>>>
>>>>>>
