Optimizing Quickbase Admin Console Connected (Sync) Tables for Effective Governance
Approximately 5-minute read In the ever-evolving landscape of data management,Quickbase's Admin Console Connected (ACC) tables (known also as Sync tables) stand out as a pivotal tool for administrators. These tables are more than just a feature - they represent a methodology for streamlined data governance and management. This blog post delves into the strategic setup and utilization of ACC tables, guiding you through a journey of efficient administration and governance of your Quickbase realm. The Governance Starter Kit To establish a comprehensive governance framework, realm admins need to create three core applications. These three apps lay a solid foundation, which allows for easy expansion and subsequent modifications. Step 1 - Create an app called Admin Console Sync Hub: This is the centralized location for all ACC tables and is crucial for warehousing data points that the realm admin will use in the Realm Insights app. Less is more in this app. It is not advised to add relationships, reports, roles, users, etc. This is essentially a data repository that updates according to the app manager's specifications. ACC Tables currently include Users, User Access, and Applications. It is important to create this foundation to prepare for future tables that will be added. Step 2 - Create an app called Realm Insights: This is where the Realm Admin will slice and dice all realm data to get a holistic understanding of the realm and its overall health. It shows how Apps and Users are being utilized and what they interact with in the Realm. It will also help develop, enforce, and maintain policy with Quickbase and ensure alignment with your company IT (Information Technology) strategy. Step 3 - Create an app called Realm Logger: An advanced audit log that tracks specific attributes with a defined granularity. Some examples of data tracked would be App Deletions, User token Additions, User token changes, User Access additions/removals App Manager changes, etc. Through the utilization of Pipelines, this app creates logs from changes in data in the Admin Console Sync Hub app and the Realm Insights app. Although some of these attributes may be tracked in Audit Logs, this methodology allows flexibility and granularity based on the need of the client’s organizations. Admin Console Sync Hub Overview It is important to note that each ACC table should come with a Pipeline. The Pipeline has multiple purposes. It copies data from the Admin Console Sync Hub into Realm Insights. It also logs its results in Realm Logger and changes/additions/removals of the data that was interacted with. ACC Table Pipeline Process to log a Deleted App ACC Table of Applications updates once a day. When the table updates (or at a scheduled time), the Apps Pipeline fires. It updates the Realm Insights Applications Table with the updated data. The Pipeline then compares the updated data and removes all records (apps) that are no longer in the Apps Table of the Admin Console Sync Hub. These records are then added to the Realm Logger Apps Deleted Table. Now the Realm Admin can see all App deleted in the Realm and the attributes they choose to maintain. Separation for Simplification A common question is why this solution keeps the Admin Console Sync Hub and its ACC tables separate from Realm Insights. While integrating directly into Realm Insights is possible (and often necessary for certain API integrations), maintaining separate entities simplifies app management. This separation prevents the need for re-architecting Realm Insights with each new ACC table addition (read about our expansion plans below), enabling a more streamlined update process through pipelines. This method has proven easier to scale and allows the client to build the solution as they see fit. There are a wide range of building skillsets, and technically, you may not want a pipeline per ACC Table. You may want to use one pipeline or twenty. The point is to prepare for scale and keep applications limited in roles and scope so that a “Franken-App" does not occur. User Token Added as ACC after a Few Months Example: A Realm Admin imports a User Token file from Admin Console on a weekly basis in a Table labelled “User Token” in Realm Insights. In a few months, the Realm Admin has been told a new User Token ACC Table is going to be made available. There are many reports, workflows, and relationships associated with the User Token table in Realm Insights. Once the User Token ACC Table is made available, the Realm Admin can create a pipeline to copy the data into the Realm Insights app. NOTE: All Quickbase plans include audit logging, which is accessed from the Admin Console. If you decide to set up your own audit table in the Realm Logger app, this can be a great tool to facilitate custom reporting and notifications. However, any audit records saved to a Quickbase app can be modified or deleted if the roles in the app allow this. That means the Realm Logger may not meet your needs if you plan to use it for compliance purposes. To determine the right approach in that case, please make sure you consult your legal team first. Use Cases Here are some examples of some of the data insights you can gain by just leveraging the three ACC tables that exist now: Users – Users in the Realm How many non-company emails are being used in the realm Anytime a user changes permissions: Can Create Apps Realm Approved Realm Admin Super User Pipeline create permissions App Admin Can Create User Tokens Non-Realm Approved Employees Users to Deny (based on length of time not accessed) Users that have Never Accessed an app, but still have Access Access – Users and their app access # of App Access per User App Level Permissions per User per App # of Apps Accessed per specific timeframe Apps – Apps in the Realm How many applications have not been accessed in 90/180/240/360 days # of Apps per App Manager Apps Created per Year Everyone on the Internet Apps Apps with or without Vendor Access Anticipating Future Expansions Quickbase's commitment to growth is evident with the planned introduction of new Admin Console connected tables, encompassing User Tokens, Pipelines, Groups, Tables, Pipeline Access, and Solutions. These additions, expected throughout 2024, highlight the need for a robust foundation to facilitate easy scaling. Conclusion: The ACC tables are more than just a single feature. They are a cornerstone of effective Quickbase governance. By understanding and implementing these strategic practices, you can transform the way you manage your realm, laying a foundation for growth and efficiency. As more data points become available, scaling becomes much easier as you mature with the product and the product adds additional ACC Tables and APIs. Explore more about connecting ACC tables in our help center article. For in-depth information and if you need further assistance, don’t hesitate to reach out to your sales or service support contacts.
Cross-Table Report Formulas
I am using QB in our manufacturing facility and am trying to get an order-by-order bill of materials. Specifically, I have a table for "Raw Goods" (the raw materials we make into parts), "Parts" (the things we sell), "Assigned Goods" (an intermediate table creating a many-to-many relationship between Raw Goods and Parts), and an "Orders" table (where individual orders for parts are entered). Each part record has a bill of materials that specifics how many pounds of of each raw good is needed to make one part. When I enter an order in the Orders table and enter the quantity of parts ordered, I need the Order record to multiply the quantity of each raw good by the number of parts ordered. I can figure out how to the get the original bill of materials to show up on the Order record (by looking up the embedded bill of materials report from the related Part record), but how do I then multiply the quantity of parts ordered in the Order record by the individual raw good quantities? In the example below, I would need a column multiplying each value by 15,000. Any suggestions are much appreciated! ------------------------------ Kiel Berry ------------------------------
Quickbase Actions
I am trying to make a Quickbase action... When a certain action takes place, I would like for a field to be edited, rather than having a new record created. However, the option for a field to be edited is blurred out. I need the field in another table to be edited? What am I missing?
How to use formula or summary field to get latest record data?
I have a table sales and a child table of products. I am trying to find the latest related child (products) field "price". I can create a summary field on the parent to get the latest date but it will not get me the "price" field. How do I use the formula field to get the latest date record and return the "price" value of that record? Thanks!
Changing the primary key field in a table that already has relationships
When I created my QuickBase application, I didn't have the full set of data (no primary keys). So I imported the data into QuickBase, allowing it to create it's own Primary Key. There are now 2 tables with relationships linked to those temporary Primary Keys. Today, I got the full set of data, including their internal primary keys (just a sequential number). So what I'd like to do is update the temporary key with with company's key. I tried using Quickbase's internal tool, but if I import the company's key and set that as the key in the Field section, it won't automatically set primary keys for new records. I tried overwriting the temporary keys values, but Quickbase makes them un-editable. I tried importing in new values for the temporary keys, but I get an error saying, "Cannot find all key numbers" or "Can't update two columns at the same time". There must be a way to do this? Or do I literally need to make a new table without data and re-create all my relationships manually? But even if I do that, will Quickbase automatically increment the assigned internal primary key, or will I have to update it? Thanks!Filter dropdown selection
I want to filter records in a dropdown based on whether a related field is true. Specifically, a project dropdown should only reflect projects with a "funded" status and hide "completed" and "cancelled" Conditional dropdowns doesn't seem to apply, since one reference field is involved.
Is it possible to create a report link that drives to a specific report?
Hi, I have two tables: Billing Month & Forecasts that are not related but I use a report link to connect the two based on a date field. I'd like for the Report Link on Table #1 to drive to a filtered report on Table # 2. Is this possible? Below is an example of the exact scenario I need to accomplish: I have a report that lists out all of the Billing Months (ex: Dec. 2018). When I click on the Report Link for the Dec 2018 Billing Month record it drives to a report showing all Forecast Records available for December 2018. However, it would be helpful to take it one step further to only show Sold December 2018 Forecast Records. (Sold being indicated by a field on the Forecast table). Not sure if there is a way to add additional criteria to a Report Link or if this could be accomplished through a Formula URL button but any ideas to solve for this would be greatly appreciated. Thanks!Relationships functional question
Hello! Intermediate-level user here and very much enjoyed the former QB forum - hoping to see some familiar faces on here. I am dipping my toes in the waters of fixing a Quickbase app that I didn't actually build - which is great! Something I'm thinking I want to learn more about/perhaps pursue from here. But also feels like the litmus test here - fixing my own Quickbase that I built is one thing, but now getting to know the ins-and-outs of someone else's is - wow. Anyway. One ask from the team I'm refreshing this for, I'm struggling with because I'm not sure this is possible. They want to see on the Table view, a field that populates a date from the related table of the last record. Perhaps there's a formula that I can pull to make this a field. So -- in the simplest terms, I feel like what this field needs to be is: Parent Table owns the field, and that field is points to the Record Date of the newest Child Table record. How can I do this? Is there perhaps a formula field to build? Also - I see my predecessor attempted this, and it is reporting out on several fields..... I don't quite know how to make sure my fix populates everywhere, other than editing the specific existing field. Could I just copy that field, and it'll still report on those records? I know if I "change type" of field, you can often only do that once, and it won't let you change it a second time.
