Prevent duplicate project number

I have a database where the associates can look up to see what the last project number was on a project - this is per their specs. They wanted to be able to type in the number rather than have the database gen a UNID.  So is there a way that I can somehow have the database check to make sure that they did not type in a number that is already in use on another project?
kali958Asked:
Who is Participating?
 
qwaleteeConnect With a Mentor Commented:
check := @DbLookup("";"";"ALL_NUMBERS";Number);
@If(@IsError(check); @Success; @IsNewDoc; @Failure("Duplicate project ID #" + @Text(Number); @Success);

This assumed the project number field is named NUMBER
0
 
SysExpertCommented:
SUre, just do a DB lookup on the view sorted by project numbers and see if it already exists.

I hope this helps !
0
 
kali958Author Commented:
Okay, dblookup for the field. Does that mean on the editable field where they type in the project number then in the validation have the lookup?  I just need some more clarity on this if DBlookup provides the solution.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
SysExpertConnect With a Mentor Commented:
In the validation I would think. If it exists already, popup a message.


0
 
kali958Author Commented:
Okay either it is cuz it is Friday or I just need a vacation, but here is an example of what I think you are suggesting:

Create a field, NUMBER for the tracking number in the document,

then in the validation put in the dblookup code something like @DbLookup("";"";"ALL_NUMBERS";Number;not sure what my key would be yet)

But my issue is how to I tell it to compare what is typed in the NUMBER field and what to lookup in the @DbLookup.
and then if they match there could be something like @If (Number = FoundNumber;@Failure("These Match, try again";@Success).

I am just lost on how to do the lookup and compare.
0
 
kali958Author Commented:
This is what I came up with

NotUnique := (@IsNewDoc & @IsMember(@Text(NUMBER);@DbColumn("":"NoCache";"":""; "ProjectNumbers"; 1)));

BlankField := Combo = NULL;

@If(NotUnique | BlankField;@Failure("The Project Number you supplied is not valid or blank. Please confirm that the field contains a unique value.");@Success)
0
 
qwaleteeCommented:
Hmmm. If that works, great.  But it is more costly than @DbLookup, since @DbColkumn has to retrieve every row of the view.

Alos, there is a 64k limit to what @Db functions can return.  If you expect a LOT of documents in the view, you will blow the limit.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.