How to base a new field value off an old field value.

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

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.

Photo of Adam

Adam

  • 0 Points

Posted 5 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
no problem.

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.
Photo of Adam

Adam

  • 0 Points
Slight Hiccup, Record ID is a number, and project number is a string.  Project numbers are in the format AA-123.
Photo of Adam

Adam

  • 0 Points
[deleted]
Photo of Adam

Adam

  • 0 Points
I can't make the summary field because Project number is a string, not a number.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
Uggg.  So what is the sequence of the project numbers?  What project # comes after AA-001.  Do all projects start with AA- where the AA is actually and "A" and an "A", or are you saying a project number could be BX-247.  If you are counting up from AA-001 through AA-999 before you get to AB-001, then you have a seriously large number of projects.
Photo of Adam

Adam

  • 0 Points
AA is a two digit representation of the year the project was created.  So a real project number would look like 97-058 or 14-878.  So whatever comes after 14-878 could either be 14-879 or 15-879, depending on when the project is started.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
Use the image onload technique:

Pastie Database

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=282


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));
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
OK, so that is hugely helpful.  Gotta run now but I will post back later tonight.  but basically we just need to have a field called [Numeric portion of Project #] with the formula
ToNumber(left([project #],3))

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 .....
Photo of Adam

Adam

  • 0 Points
This sounds horribly hacky IMHO.  I guess that's just the limitation of QuickBase as a platform?
Photo of Adam

Adam

  • 0 Points
So where exactly would that bit of code go?  I'm absolutely new to QuickBase so I'm not sure exactly how everything plays together.  It looks to be an api call.  So does that have to embedded in a separate webpage or can i put it in the formula field?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
Yes,
A summary field for min Max average or std deviation can only be fine on numeric values.

Let me know if you any more help getting my solution to work.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
(1) You create two user defined variables named "iol" and "/iol" as show in the attached screenshot. Copy the text of the two definitions out of the pastie listed at the end as the characters must be exact.

(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.

Pastie Database

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=283


Here is a quick working example:

Bumpt It Up!

https://haversineconsulting.quickbase.com/db/bi3wfknpq?a=nwr
Photo of Adam

Adam

  • 0 Points
Thanks for the help!  I got busy suddenly so I will have to come back to this in a little bit.  I'll let you know if I can implement this successfully.  Thanks again
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
OK, you were probably swamped with the two directions that you can go to solve tis problem.  Please do post back or contact me offline via my profile if you need help.
Photo of Ryan

Ryan

  • 88 Points 75 badge 2x thumb
This is an interesting solution that I have used for counting backwards to indicate a "budget remaining" for related fields, but I do see a limitation with this approach in this situation.  Namely, what happens when a project is deleted?  Your summary field will be decremented by 1 and possibly cause a duplicate project number.  I.e. you create 10 projects (14-001 through 14-010), then a week later you delete project number 14-003.  The next project created will be project 14-010 (which already exists).

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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,894 Points 50k badge 2x thumb
To use this solution with the snapshots  and have it work 100%, you would to remove anyone's ability to delete the Parent record.  Instead you would make a checkbox field for "Soft Delete" and filter those off reports or even simply prevent all user Roles (expect perhaps the admin role) from seeing Parents which were deleted.