Table fields

[Wright4D]

Hi All, new here and just a small company needing help, we are building a database which controls documents and drawings from a revision status and their issue to control whether issued to clients, received from suppliers and then where in the approval process they are at any time. we use a combination of identifiers to generate a single document number. I am trying to use a form to have several combo boxes to generate each final number element e.g. job number, doc type code, supplier number, supplier sequential number. I then have a field which concatenates all these individual boxes together to end up with the final document number which I then want to store back to a documentation log to then begin the tracking of revision numbers etc. can anyone help with how to store this final field back into the correct table.

Thanks in advance

 

[Gasman]

If you can generate it each time, no need to store it?
Though in your description, as long as it will never change, it might be quicker to find, rather than looking at four different fields.
You could use a calculated field I suppose? I never have, but it seems ideal for a situation like this?

I will let the experts confirm that though.

 

[GaP42]

Presumably your table for tracking document revisions is dependent on a table tracking the document? ie you do want to see the stages it has progressed through. For the document table you need a primary key that is not the composite of the values you outlined in the OP, however you do want these values in the document table. Your table should store each value in a separate dedicated column. As @Gasman suggests, the value presented in the form as the document identity can be the concatenation of the separate values - a calculated field. Searching for the value to locate a record, or filtering to find records can be done on the separate values or the concatenated value.
Don't store the concatenated value in your table - just calculate and show it as needed in forms or reports. The other issue you may need to address is to prevent editing of the values used in concatenation once the document record has been initialised, and archiving rather than deletion of these records.
Your form will need to use validation checks to ensure your rules are followed for entry of the document identity values in the before update event of the form - not just is not null; you can automatically apply the next sequential number applicable for a supplier. How are job numbers assigned?

 

[LarryE]

Can you please take a screenshot of your table and relationship structure so we can see what the correct table might be. Once we know which tables you have now then we can advise which table you should store the value in or in a new table. Then you can use:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

SQL statement to insert your new values into the correct table. Because this is a user-concatenated value, I understand why you need to store it for later searching and filtering purposes. But right now, we can't determine what table might be.

 

[mike60smart]

Hi All, new here and just a small company needing help, we are building a database which controls documents and drawings from a revision status and their issue to control whether issued to clients, received from suppliers and then where in the approval process they are at any time. we use a combination of identifiers to generate a single document number. I am trying to use a form to have several combo boxes to generate each final number element e.g. job number, doc type code, supplier number, supplier sequential number. I then have a field which concatenates all these individual boxes together to end up with the final document number which I then want to store back to a documentation log to then begin the tracking of revision numbers etc. can anyone help with how to store this final field back into the correct table.

Thanks in advance

Can you upload a copy of the database?

 

[plog]

I've played this game before many times on this site, but willing to do it again for you: I strongly suggest you use autonumbers and forget this ultimately useless amalgamation you think is the right solution.

Here's where you say--"But we need all that information crammed into a string somewhere on the document."

No you don't, life will be so much easier if you just slap a computer generated number on it. You're just tracking documents right? So...

1. That document will be viewed in context. Either it will be in a folder along with other documents of that job so the user will know the meta data about that document already. Or your users will be using the database in coordination with that document so that can get much more information about the job while they view that document. You don't need all that other stuff on it.

2. No data is immutable/ humans gonna human. What happens if you are 2 months and 50 documents into a job and part of the data you used as the name changes? You gonna white-out that number from 50 documents? People also make typos and select wrong drop downs that might get corrected later as well. You slap a number that means nothing on a document and you never have to change it if things upstream from it changes.

3. Only the database can tell you which is the "final" document. Even if the last part of the document number is the revision number people will still have to go to the database to see if the document they have is the "final" one. Slapping a version number on a document doesn't let you know if its the last one. This concatenated versioning naming system doesn't really do much for you.

Make life easy on yourself and abandon this. Use autonumbers to track your documents.

 

[LarryE]

You could also just add a Date and Time stamp field whenever a change is made to the document. That's an easy way to keep track of which document change is the last one. You could then search for or filter dates/times or between dates/times. I would use a separate field for the date and a separate field for the time. Format the Date field on the form in Short Date format and format the Time field as Medium Time (10:15 AM).