I am trying to write a code for taking an number entered in a Data Form and compare it to the part table to see if the number already exists. If it does I want to add 1 to it and test it again until it gets to a part number that doesn’t exist yet, and then assign the new part number to the CallContextBOMData.Character01 field for use later. I have been reading all the documentation and examples I can find and am a bit stuck. I have never written anything like this in ABL. What I have cobbled together so far is below. Am I on the right track? I have bolded the 2 parts where I am not sure what I need to do. Any ideas, help or places to find some help are appreciated
Thanks,
Melissa
Define Variable iCount AS Integer No-Undo Initial 1.
Define Variable NewPartNumber AS Integer
Outerloop:
FOR each NEWPARTFIELD:
If ttCallContextBPMData.Character01 = Part.PartNum Then NewPartNumber = (ttCallContextBPMData.Character01+iCount)
Innerloop:
REPEAT WHILE NewPartNumber = Part.PartNum :
(Not sure what I need here to make it do this step) NewPartNumber = (NewPartNumber + 1)
(Don’t know what I need here about stopping the Repeat)
Then assign NewPartNumber = ttCallContextBPMData.Character01.
END.
METHOD INTEGER ValidatePartNumber(iNPUT ipinNewPartNumber AS INTEGER):
DEFINE VARIABLE mlgFound AS LOGICAL NO-UNDO INITIAL TRUE.
DO WHILE mlgFound:
FIND Part WHERE Part.PartNumber EQ ipinNewPartNumber NO-ERROR.
mlgFound = AVAILABLE Part.
IF mlgFound THEN
ipinPartNumber = ipinPartNumber + 1.
END. /* DO */
RETURN ipinPartNumber.
END METHOD.
Anyhow, I think this is better for finding the next part number:
FOR EACH Part BY PartNumber DESCENDING:
NewPartNumber = Part.PartNumber + 1.
LEAVE.
END.
find last Part use-index PartNumber no-lock no-error. NewPartNumber = (if available Part then Part.PartNumber + 1 else 1).
requires an index on PartNumber but you should have that already
forgot to add no-lock no-error to the find and the forum does not allow updates to posts :-(
even worse, my post just disappeared
find last Part use-index PartNumber . NewPartNumber = (if available Part then Part.PartNumber + 1 else 1).
Sorry, but this is a terrible, terrible idea. What happens when more than one user runs this code at the same time ? They get the same part number.
You should generate things like this from sequences. If you are concerned about "gaps" (records not committed) then you should record / audit the reason why there is a gap. Or wait until the record is being created and then get the next sequence.
@jlmls I agree sequence are better if you don't mind the gaps, we always use them for internal id fields. But to protect against duplicates you need an unique index even with sequences.