Solved

Prevent duplicate project number

Posted on 2007-11-16
7
194 Views
Last Modified: 2013-12-18
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?
0
Comment
Question by:kali958
  • 3
  • 2
  • 2
7 Comments
 
LVL 63

Expert Comment

by:SysExpert
ID: 20299920
SUre, just do a DB lookup on the view sorted by project numbers and see if it already exists.

I hope this helps !
0
 

Author Comment

by:kali958
ID: 20299991
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
 
LVL 63

Assisted Solution

by:SysExpert
SysExpert earned 150 total points
ID: 20301308
In the validation I would think. If it exists already, popup a message.


0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

Author Comment

by:kali958
ID: 20301358
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
 
LVL 31

Accepted Solution

by:
qwaletee earned 350 total points
ID: 20301453
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
 

Author Comment

by:kali958
ID: 20301496
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
 
LVL 31

Expert Comment

by:qwaletee
ID: 20315010
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

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question