Forum Discussion

ChaitaliDamani's avatar
ChaitaliDamani
Qrew Member
12 months ago

Creating a Unique Identifier

Is it possible to create a unique identifier? For example, there is a field in the parent table called "Position Number" and a reference field in the child table. When someone adds a new record for that particular position number in the child table, which is a vacancy tracker, I want to create a unique identifier using a combination of the Position Number from the parent table (which is also a key field), +current year, and the number of records attached to that identifier in the child table on QuickBase.

 

If the Position Number is 12345, the unique identifier should be 12345-2023-01 for the first related record, 12345-2024-02 for the second related record, and so on.



------------------------------
Chaitali Damani
------------------------------
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    12 months ago

    Yes - the general idea is that you create a formula-text field in your child table that builds the presumptive identifier and then on the form you use a form rule that pushes the value of your formula into the key field. So your formula would be something like: 

    [Position Number] & "-" & Year(Today()) & "-" & ([Current Year Children] + 1)

    The missing part is that you'll need to create a summary field between your parent and child that counts the # of records created this year so far. You then add that back as a lookup field into your child. So lets say in 2023, you've created (2) children so far that exist in QB. When you make this formula then - the formula would have the value of 2 represented by [Current Year Children] - so add 1 to that in your string and you get 3. 

    Then in your form rule you essentially make a rule that says when Key Field does not equal your formula and Date Created is blank - change the value of Key field to the formula. Uncheck the box that says to fire only on change and it keeps changing if you reset the parent as a failsafe. You need the date created piece to make sure that it doesn't try resetting once you've saved. 



    ------------------------------
    Chayce Duncan
    ------------------------------