Thanks for quick responses.
@Martin
1>Please suggest on how to do this "Also, magic numbers such as 47 should always be avoided."
2>Does the whole code runs inside a transaction?
Yes
3>What's the value of CacheLookup property of the table?
None
4>Don't you want to add a unique index with Category and SubCategory fields?
I want this , please check response for 5> for further details
5>Try Using exist()?
wrote this method in my table & then it started working REVISON , just noticed it does not work , i was running the job via batch & after few execution it did inserted rows in the table :-(.
public boolean existsRecId(str 100 _char1,str 100 _char2, str 100 _char3 )
{
Mytable mytable,mytable1;
mytable.clear();
mytable1.clear();
select firstonly RecId from mytable
where mytable.Category == _char1 && mytable.SubCategory == _char2;
if (mytable.RecId == 0)
{
select firstonly RecId from mytable1
where mytable1.Category == _char3 && mytable1.SubCategory == _char2;
if(mytable1.recid == 0)
{
return false;
}
else
return true;
}
else
return true;
}
Note When trying to add index of not allowing duplicates for subcategory field of myTable along with cachelookup value "none in TTS" , the solution does not work & exhibits the same issue
i.e. inserts 1st attempt into table, avoid insertion in 2nd attempt & 3rd or consecutive random attempts again inserts into the table.
@Gunjan & Niklaos
Yes i have debugged it several time the value of charholder1 & charholder 2 shows up as apple & fruit while debugging.
For 2nd attemp it finds the apple & fruit in myTable & skips inserting the record which is expected .
However for random attempts after 2nd attempt even when debgigger coninue to shows up apple & fruit for charholder1 & charholder2, it for some unknown reason is unable to find a recid in mytable unlike attempt2 & hence initiates the record insertion code block.
