If you are building an app where it's required for you to create a unique ID for every new record, an option we always recommend is referencing the Frontly ID that populates into your Sheet data automatically. However, we understand if you want to format your IDs in a manner that's just more than just a whole number that increments +1 each time.
In this guide, we show you how to auto-generate the following type of ID formats that can be included into your row data and used to uniquely identify your values:
Numeric: A simple numeric identifier. Example: 000001
Alphanumeric: An identifier combining prefix and numbers. Example: PRJ002 or SRE-192
Preparation
Setting Up Your Spreadsheet
First and foremost, you will need to create the column in your Google Sheet that will be the new ID number. If this is a sheet that's already been imported into your account and this is a column you've just added, be sure to click the "Refresh Data and Headers" button to ensure it gets updated into your Frontly application as well.
Click here to learn more about setting up your Sheet data if haven't done so yet!
Decide on an ID Format
Of the options mentioned at the beginning of this article, decide how you want your IDs to be formatted. Do you already need to follow a system that exists?
Pick a format that makes the most sense for your use case!
Creating a Form and Hidden Form Field(s)
At Frontly, the likeliest place you or your users will be adding new records to your spreadsheet is through a Create Form block. After you've created this block and imported your spreadsheet, select and edit your designated ID field type to 'Hidden'.
Hidden field types will prevent users from creating an ID of their own and allow the filled 'Hidden Value' to auto-populate for each submission. This is where you'll be entering your format function.
Click here to learn more about Adjusting Field Types
Click here to learn more about Hidden Form Fields.
Entering Your Formatting Functions into Frontly
Due to the nature of Frontly's set-up with Google Sheets, any new rows or cells of data uploaded by the platform will overwrite the auto-updating functions in your sheet for new rows. In other words, any data submitted through a Frontly Form block will unfortunately not have your Sheet configured functions applied to them automatically.
Continue reading below for a simple workaround and solution to this!
Any type of function or task that needs to be carried out by your Google Sheets will need to be entered through a field into Frontly first. As mentioned, this is usually the Hidden Value of a column.
Below are the functions you could enter, an explanation of the function's parts, and lastly the output.
For Numeric IDs
Example Format 1
Function:
=TEXT(({X{row_count}}-1),"000000")Explanation:
= - The equal sign (=) in a formula in Google Sheet is always used to indicate the beginning of a formula. When you start a field entry with an equal sign, you're telling the spreadsheet that you're entering a formula rather than just plain text. In Frontly, this functionality at its core, remains unchanged when input data is submitted from your Frontly app into your Google Sheets
TEXT - The TEXT function is used to format the numeric part of the ID with leading zeros.
(X{{row_count}}-1) - X represents the column you're referencing for the formula. Using the {{row_count}} dynamic variable, Frontly will identify where the row data exists in the Sheet. The "-1" expression will subtract 1 from the row count given that the first row of any spreadsheet data is typically the column headers.
"000000" - The format string "000000" ensures that the number has at least six digits with leading zeros.
Output:
{First row ID = "000001", Second row ID = "000002".... }
Example Format 2
Function:
=TEXT((31+{X{row_count}}),"000")Explanation:
This is function is very similar to Example 1 but it has three digits instead of six. Additionally instead of numerical ID sequence beginning at 0, it begins at the number 31 instead.
Output:
{First row ID = "032", Second row ID = "033".... }
For Alphanumeric IDs
Example Format 3
Function:
="PRJ-"&TEXT((X{{row_count}}-1),"000")Explanation:
= - The equal sign (=) in a formula in Google Sheet is always used to indicate the beginning of a formula. When you start a field entry with an equal sign, you're telling the spreadsheet that you're entering a formula rather than just plain text. In Frontly, this functionality at its core, remains unchanged when input data is submitted from your Frontly app into your Google Sheets
"PRJ-" - This is a constant string prefix that will be concatenated with each number. This used as a common approach to create a unique identifier or reference code for items in a list or project.
TEXT - The TEXT function is used to format the numeric part of the ID with leading zeros.
(X{{row_count}}-1) - X represents the column you're referencing for the formula. Using the {{row_count}} dynamic variable, Frontly will identify where the row data exists in the Sheet. The "-1" expression will subtract 1 from the row count given that the first row of any spreadsheet data is typically the column headers.
"000" - The format string "000" ensures that the number has at least three digits with leading zeros.
Output:
{First row ID = "PRJ-001", Second row ID = "PRJ-002".... }
Additional Tips:
Always ensure that your function is applied consistently across your platform. Copying and pasting functions can be the best way to help mitigate errors and ensure consistency.
Test the function in different scenarios to validate the effectiveness of your formulas.
Consider incorporating additional elements into your IDs for increased customization. IDs can be formulated to be increasingly more complex but are not detailed in this guide. Additional options can include but are not limited to:
Increasing the ID sequencing of row data by more than 1.
Partially or fully randomizing the alpha or numeric portion of the ID or both.
Generating an ID prefix from the initial letters of the specific label.