AdrianLloyd
Quickbase Staff
Joined 6 years ago
User Profile
User Widgets
Contributions
Creating Sequential Numbering Unique to your Business Powered by Formula Queries
Have you ever wanted to be able to create a custom numbering sequence for records in your app (see [PO Number])? Common use cases where this comes up include creating purchase orders, invoices, tasks, budgets, etc. Quickbase has had a variety of complex and multi-step solutions to create these in the past (some combination of summary fields, lookup fields, snapshot fields, and Pipelines), but with formula queries, we are able to drastically simplify the process. In the article below we talk about how you can use formula queries to implement a system of your own. Use Case: Jesse is responsible for managing inventory for a wholesaler at one of their large warehouses. Jesse does a weekly audit of the on-hand quantity of the different items they sell, and she is responsible for placing purchase orders to restock. The company has a specific numbering process for their POs; each PO should be sequential based on the number of POs for that year for that vendor. For example, Jesse has already placed 3 orders with Vendor A for the year, so the next PO should automatically adjust to be number four for that Vendor. Jesse's format looks like this, Vendor A – 4 – 21. Currently, Jesse is manually creating these PO Numbers. Let's help Jesse remove her manual process and create a formula to automate the custom numbering. Jesse's app has five tables: Vendors, Items, Vendor Items, POs, and PO Items. For the sake of this solution, we will be focusing on the PO table specifically. We know Jesse's end goal is to create a format that lists the vendor's name followed by the sequential PO number and then the last two digits of the year. Vendor Name – Sequential Number – Last two digits of the year On the POs table we have: Field Name Field Use Field ID (needed for the query) [PO Number] 1. The formula field N/A [Vendor Name] 1. Used in the PO Number Formula N/A [Order Date] 1. Used in the query to identify records in the same year 2. Used in [PO Number] to identify the last two digits of the year 11 [Related Vendor] 1. Used in the query to identify records from the same vendor 9 [Record ID#] 1. Used in the query to identify the order in which a record was created 3 Step 1: Write the query variable to identify the number of records that precede the PO being entered where the [Related Vendor] is the same, and the year of the [Order Date] is the same. Note: We are using variables to make the final formula easier to read Let's break this down. var number ponumbertrue "var" tells Quickbase you are starting a variable "number" establishes the result type of the variable (numeric result) "ponumbertrue" is the name we will use to reference the variable later in the formula (this value can be anything you want it to be) Size() A new function available in the Formula Queries Beta, which counts the number of values in a recordlist, textlist, or userlist data output, gives us the number of records returned in the query – always a numeric result GetRecords() A new function for using Formula Queries creates a list of records (recordlist) of all the records returned within the query The Query (to learn more about Query structure and basics click here – each PO should be sequential based on the number of POs for that year for that vendor "{9.EX.'"&[Related Vendor]&"'}AND{11.CT.'"& Year([Order Date])&"'}"&If(not IsNull([Record ID#]),"AND{3.LTE.'"&[Record ID#]&"'}") "{9.EX.'"&[Related Vendor]&"'} AND {11.CT.'"&Year([Order Date])&"'}"& AND If(not isnull([Record ID#]), "AND{3.LTE.'"&[Record ID#]&"'}") Note: The colors align with the descriptions below First Query String: "{9.EX.'"&[Related Vendor]&"'}… We want records that have the same [Related Vendor] as the record on which the formula is evaluating Find records where field ID 9 ([Related Vendor) is exactly equal to this PO's [Related Vendor] AND where… Second Query String: "{11.CT.'"&Year([Order Date])&"'}&… We want records that are from the same year as the record the formula is evaluating on Find records where field ID 11 ([Order Date]) contains the same year as this PO's own [Order Date] Third Query String (conditional on a record ID# existing): If(not isnull([Record ID#]),"AND{3.LTE.'"&[Record ID#]&"'}") AND where… We want the records that precede or are, the record on which the formula is evaluating Find records where field ID 3 ([Record ID#]) is less than or equal to this PO's [Record ID#] Note: the third query sting is conditional because the formula won't correctly evaluate until a record ID# exists. Making it conditional allows us to forecast what the PO Number will be prior to saving by querying just on a year and related vendor. Step 2: Concatenate the rest of the formula to make Jesse's custom numbering system. Let's remind ourselves of the requirements: Vendor Name, the sequential number, and the last two digits of the order date's year [Vendor Name] &"-"& $ponumbertrue &"-"& Right(ToText(Year([Order Date])),2) We call the formula query variable using the "$" and the variable name Then we need to take the last two digits of the order date's year using the Right() function which requires us to convert the Year([Order Date]) to text since it outputs a numeric value Step 3: Enjoy the result. [PO Number] is our formula, and you can see how the number in the middle increases sequentially based on the order the POs were entered ([Record ID#] shows you the order they were entered). Note: Please be aware that if you delete a record, the formula will adjust to the new number of records found by the query. An easy solution is to create a checkbox or multi-select text field that users can select to "delete" or "archive" a record that removes it from their permissions. Now you can experiment with building your own custom numbering formula with this as your guide!Job Aid - Audit logging with automations
Audit log active changes Jamie is a project manager who has been trying to understand why tasks are slipping. She wants to keep a record of the original start and end dates. She wants to be able to see how many times they change and what the new dates are. She is not sure how to do this and she is asking for your help. You tell her that you know how to do it in Quick Base and will build it for her. First you will create a table to use for an audit log and then create the automation to add the data to the audit log. You will also create a relationship between the Tasks table and the Audit Log table so that you can see the number of times the dates have changed. Let’s look at the steps that you go through to build this for her. Step 1: Create the table and fields Start by creating the table to hold the audit log data. Click New Table Select From scratch Enter Audit Log for the table name Enter Log for the record name Choose an icon Enter the description: This table tracks the date changes from the tasks table. On the new table, create the fields you wish to track. Create a field for both the changed and original values. The field types should be the same type as the original ones on the source table. Create the fields. Enter the field labels Select the field types Click Add Field Label Type Old Start Date Date New Start Date Date Old End Date Date New End Date Date Step 3: Create your automation Step 2: Create the relationship Next create a relationship between the audit log and the source table which in this example is the task table. This will allow Jamie to see the number of times each task has been changed and all associated audit records more easily. The automation will copy both the original data and the changes to that data into your new Audit Log table. We want to create a new audit log record every time the trigger happens to keep track of all changes. Create the automation to trigger when data changes. Click When data changes to set the trigger Enter a name for your automation: Audit Log Enter a comment for your automation: Every time the start or end date change create a new record in the audit log table Configure the automation trigger to audit for when a record is modified and the start date or end date changes. Deselect adds a record because we only want it to trigger when data is modified Select modifies a record because we want it to trigger every time data is modified Select the Tasks table because it is the table that we want to audit Click Additional Changes Select Any of the following fields change Add the fields Start Date and End Date as these are the fields we want to audit Create the action to add a record to the Audit Log table. Click Add an action Click Add a record Click Next – will say Finish if you don’t have any relationships Choose the Audit Log If you did not create the relationship, but the trigger table is a parent, then you will have to navigate to the Audit Log table. Click Finish If you have a relationship, you will see the grey faded text, letting you know that Quick Base will automatically add a record to the Audit Log table. If you don’t have a relationship, Quick Base will prompt you to choose an app and a table. Configure the action to add data into fields in a new record. The important thing is to choose between the old and new values accordingly. In the field dropdown, select Old Start Date. This is where you want the data to go. In the Source dropdown, select Copy value from another record. For audit logs you will normally choose this. In the Value dropdown, click the arrow next to Tasks records from trigger (new values) to close the new values Click the arrow next to Tasks records from trigger (old values) to see the old values fields Select Start Date Click the + icon to add new rows to add other fields Repeat steps to add all your fields. Make sure to assign the new and old values to the correct fields Click Save & close Great job! You have created the Audit Log for Jamie. Looking to learn more about how you can use Automations? Task Templating with Automations (Job Aid) About Quick Base Automations (Help) Working with Automations (University Lesson) Complex Workflow and Automations (University Lesson)
