I am trying to create a pseudo-auto-incrementing field. This field would be one higher than the max of all previous values of the same field. To demonstrate...
A table has a project number field. A project number may or may not coincide with the record ID (often times records that are entered later might have an earlier project number). The project number will go up by one with each additional project added to the database, though a project number is not assigned at time of entry (hence the mismatch between project number and record ID). In our current database the logic is handled something like
Project Number = Right(Max([Buildings]![Project Number]),,"-") +1
Which looks for the max value in the Project Number field, increments it by 1, and assigns that value to the new record.
Any way to set that up so for a new record, the value of X field is based on the max value of X field for all records.
Make a new table and add 1 record to it. The table will be called Max Project #. The record ID of the record you add will be record ID = 1.
make a formula field in your projects table with a formula of 1 called [Link to Max project #], and then make a relationship so that 1 Max project record is related to many Projects by that link field.
Make a summary field of the max of the Project # field, and then do a lookup back down to the projects record called [Max Project # lookup].
Lastly, make a snapshot field of the lookup field called [Max project # snapshot]. The auto numbering can then be based off that snapshot field +1.
This code will query for the largest [Record ID#] that already exists, increment it and stuff it into the field with fid=6.
If you want to generate fields with this type of pattern AA-123 all you have to do is make one modification.
$("#_fid_6").val("AA-" + maxRid +1);
Not that this "computation" is only performed once at the time of data entry. It is not a QuickBase formula that re-evaluates itself with every access.
UPDATE: To accommodate your explanation of what AA means.
$("#_fid_6").val(new Date().getYear() - 100 + "-" + (maxRid +1));
then do a max on that field, and then you will have the suffix portion. Then by formula build back your project number.
Post back if you need help with that formula, gotta run now .....
(2) Create a user defined page named module.js and place the provided code in it. You will have to set up an apptoken and supply values for it and the dbid in the provided code.
(3) Create a text formula field in your table named [-] with the following definition:
[iol] & "module.js" & [/iol]
(4) In the form builder list the [-] as only showing up in add or edit froms. Also set the [-] field to have Alternate Label Text and leave the value empty. This will hide the field label from view. The [-] field value does not display any visual content. The purpose of this is to make the [-] form invisible on the page as it's only purpose is to load the user defined page module.js so you can get your script injected into the page and take control. After all you have worked hard all your life why should you not be in control of your QuickBase forms?
(5) Now when you add or edit a record the field with fid=6 will automatically be populated with the calculated value.
Here is a quick working example:
Bumpt It Up!
Have you tried using a formula based off of the record ID? That's the only true auto-number field in QB (to my knowledge) that will never duplicate.