ok, so you want to take three different numbers and make them one. First create four fields: agency id, product Id , duration.( all numeric, with ranges or value lists ) fourth(Catalogue id) is a calculation.
So it should look something like this:Catalogue id=agency id;product Id;duration.
then also check box unique value,under validation, the program will prompt an error message when you have entered one that is already in use. I am just not sure if you can use / I think it is an operator you may need a different symbol. Good luck
everyone was a newbie at some point!
and I am a big believer in jumpin in with both feet, it is so liberating. Go for it.
Since your posting only describe the goal, and not how you have attempted to achieve the goal, I will make some assumptions about how you have set up your file based on how I would try it. This solution illustrates the idea of a self-generating Serial number.
I would create three data entry text fields, the serial number field, and a calculation field that strings these four fields together adding the forward slash separators:
Agency_ID = Text, always 3 characters
Product_ID = Text, always 4 characters
Duration = Text, always 3 characters
Serial = Number
Code = Calculation, Text [= Agency_ID & "/" & Product_ID & Serial & "/" & Duration]
If the first 12 characters of the Code must be a unique string, then the 3 digit Serial number must be tied to the combination of the Agency_ID_Text and Product_ID_Text. For instance you might want the following Code values to be generated when the user enters the Agency_ID, Product_ID, and Duration:
Even though many of these ads are for the same product (BBBB), they each have a unique string preceeding the duration.
Suppose your data table is named Ad_1. Set up a new occurence of your data table, Ad_2, and relate the two occurences so that Ad_1::Agency_ID = Ad_2::Agency_ID and Ad_1::Product_ID = Ad_2::Product_ID.
Go back to the definition of the Serial field and specify an auto-enter calculation:
= Case( IsEmpty( Ad_2::Serial) or Max( Ad_2::Serial) = 999; 1; Max( Ad_2::Serial) + 1)
Uncheck the "Do not replace existing valu for field (if any)" box.
Go back to the definition of the Code field and alter the calculation slightly:
= Agency_ID & "/" & Product_ID & Right( "000" & Serial; 3) & "/" & Duration
Now when you enter any Agency_ID and Product_ID combination, the next Serial for the combination is auto-entered and the correct Code is generated!
Let me know if you have any questions or comments -- Mark
thanks both for the replies, i'll try them out as soon as possible.
One question Markstar - and forgive me if it sounds dumb......whilst i think your approach with the numbers is the way forward on this project can i just check that because the current clock numbers are less well ordered (ie. done using Excel and so the 3 digit number is always 1 higher than the previous, but there is no numerical procession for each product code...dchp027, scdo028,drwh029, etc), and as i will have to enter over a thousand existing codes, will the auto generate system be able to "fill in the blanks"? So it starts at the 001 and looks for the next free number/product code combination?
having sat down to try out the suggestions, i realise now that i can understand the reasons behind the calculations but find the terminology surrounding the rest of the description a tad confusing.
How do i set up a new occurence of a data table (and what does it actually mean)?
I have figured out what you meant...and created a test. It looks good and i think is almost what i need...just another question:-
I tried to enter a new serial manually (because i will need to do this with the pre-existing data) and that allowed me to jump ahead of the sequence. The next record after that one continues the numerical sequence from the preceeding record...is there anyway to get the sequence to go back to the earliest free spot in the sequence.
Example - I entered record 1-3 using auto, then entered a record with serial 7 manually. Next record automatically shows 8 but i would like it to go back to 4 and continue the seq until it has to jump over 7. Do you know if this is possible?
I was hoping you could help me with a similar problem.
I need help with my database for assigning serial job numbers in relational tables.
I have a Clients table that assigns a client code to each client. (Example: John M. Doe's code is JMD and Tom S. Thumb's code is TST.)
Then I have a Projects table that I keep track of jobs for ALL my clients. Right now it is just generating serial numbers in order of jobs as I receive them, with a looked-up client code in front (example: JMD-001, TST-002, RSQ-003, MGS-004). I do not like this format.
Instead, from this point on, I would like to start organizing my projects to be ordered by a generated serial number for EACH client.
(Example: John Doe's projects would be ordered JMD-001, JMD-002, JMD-003....
and Tom Thumb's projects would be ordered TST-001, TST-002, TST-003....)
But I don't want to have a separate table for each client's projects. Is it possible to have all the jobs together with unique codes in the same table?
Right now I have the following IDs, with Client Name defining the relationship.
Client code (a unique value I assign, a 3 letter value)
Client code (looked up value )
Project Number (text; calculation; [=Client_Code & "-" & Serial])
I am not sure where to go from here. How would I set this up?
I appreciate the help! Thank you.