Hey @Marc_Dillon ! Yeah, I started a style guide a few years ago but never finished it. Here’s what I have if anyone else wants to take it over.
Intro
SQL-Friendly Style Guide for AppSheet
AppSheet’s naming restrictions for columns and tables are much more relaxed than SQL databases, and will allow using reserved words, spaces, and even special characters. This speeds up development by eliminating the need for separate Column Names and Display Names, in most cases.
However, migrating an app from Google Sheets to an SQL database becomes much more difficult when tables and columns have to be renamed, templates updated, etc. That can be very time consuming in larger apps, which tend to be the ones that need SQL databases. Furthermore, these larger apps are more likely to have multiple developers.
Purpose
The purpose of this Style Guide is to:
- avoid table & column names that would cause SQL migration issues, and
- define a consistent standard for naming all elements, which will
- ease future maintenance in multi-developer environments.
Smaller, single developer apps that will never need an SQL database would probably benefit from using more relaxed naming conventions, instead, to avoid needing separate Display-Names.
General
Best Practices for naming all elements
==Consistency is the key!==
UpperCamelCase, lowerCamelCase, snake_case, etc., are style choices that are SQL-compatible. Just pick a standard and stick to it.
Do
- use short (preferably 1-2 word) but descriptive names
- ensure the name is unique and does not exist as a reserved keyword
- keep the length to a maximum of 30 characters
- begin names with a letter
- only use letters, numbers and underscores in names
- use underscores where you would naturally include a space in the name
- ‘first name’ becomes
[first_name]
- avoid abbreviations and if you have to use them make sure they are commonly understood
- use plural form for List/EnumList
[related_invoices], or [selected_options]
Don’t
- use SQL Reserved Words (alone)
- use
[approved_date] vs [date]
- use plurals except for lists/EnumLists:
- instead, use the more natural, collective-term where possible
- i.e.
[staff] vs [employees][people] vs [individuals]
-
- When a collective term does not exist, use singular table names
[User] vs [Users]- use a table name as a column name (except Ref’s), & vice versa
invoice[invoice_num]
- end names with an underscore_
Data
Special care should be taken when selecting table and column names for apps that may one day be migrated to an SQL database. Other sections of this guide are purely for style/ consistency, but the Data section is critical for future SQL compatibility.
Tables
Do
- Name join-tables using terms that describe the relationship
[registration] vs [student_class]
Don’t
- name join-tables by combining table-names
Columns
Do
- ** Use Uniform suffixes:** __time _date, _at, _on, _by (created/edited) _
- Name all Ref-type columns with the same name as the table- not the key.
[client].[name] vs [client_id].[name]- Consistently name all keys in all tables:
[id], [key], or {table}_id
Don’t
- attempt to use sequential keys (unless table is read-only for all users)
- use App Formulas for sheet columns when Initial Value + Reset on Edit would be safer.
- include table names in column names
invoice[invoice_number]
Slices
Do
- include the table name AND reason for the slice
-
Don’t
UX
General
All top-level parent tables should have matching icons/format rules applied to:
- the label (whatever that happens to be, name, Invoice#, etc), and-
- primary views for that table,
- actions associated with that table, where appropriate (LINKTO*)
For App-Users, this provides a visual link between rows, views, buttons, etc.- to clearly indicate they are all part of the same table. And for developers, the consistent visual cues will aid in navigating the editor.
Views
Do
- Follow AppSheet’s system view naming convention:
{table/slice_name}_{view_type}Customer_Form, Product_Detail, etc- Avoid spaces & special characters
- Select an appropriate icon for all views (instead of default)
- add display names to all system ‘Detail’ views, to indicate ‘Table/Slice Name’
Don’t
- Tip: By using the format {table/slice_name}_{view_type}, the table/slice name can be determined with INDEX(SPLIT(CONEXT(View),"_"),1)
Format Rules
Do
- Include table/slice-name and reason for rule (color, status, etc)
- Rule Name: Active Client Red
- Add an icon to the label for all ‘parent tables’
customer[name], ?invoice[number]- Use that same icon for the views. Be consistent!
Don’t
- ‘stack’ rules with matching conditions and rely on ordering for the intended result. Be more specific with the rule conditions so that only one is true.
Behavior
General
Do
- All names should consist of at least two words in the format Verb-Noun:
SAVE FILE, OPEN URL, DELETE USER- Always select appropriate icon, even for hidden Actions, Views, etc
- Use Different names for the Workflow/Report/Process, and their individual Actions/Tasks.
(Analogous to using a table-name for a column-name)
Don’t
Actions & Tasks
Do
- Indicate the Action/Task type (by name, or at least implied)
- Send Email, View Site (Go to website-implied), Set Status* (Change Data-implied)*
- always select an appropriate action icon, even for hidden actions
Don’t
Workflows, Reports & Processes
Do
For Workflows, Reports & Processes that contain a single Action/Task:
- Include the Action/Task name, but don’t copy it exactly
- i.e. WF: ‘Send Email: Welcome Letter’, Action: ‘Send Email’
For multiple Actions/Tasks
- use a name that describes the entire set of Tasks/Actions
- i.e. WF: ‘New User Onboarding’, Actions: ‘Add User’, ‘Send Email’
Don’t
- use a workflow where an action, grouped action, form submit action, etc- would work, and not require a network connection to sync
- asdf
Expressions
General
Expression formatting does not affect SQL compatibility. The guidelines in this section are intended to aid in readability, future maintenance and consistency.
Do
- Use ALL CAPS for all AppSheet function names
USERSETTINGS(), SELECT(), [_THISROW], etc- Use UpperCamelCase for function with a fixed list of AppSheet defined parameters (Quotes optional, because these never have spaces):
CONTEXT(ViewType), CONTEXT("AppName"),- Use double quotes on all other strings, even when not required
LINKTOVIEW("User_Form")- Add Line Returns and Tabs for readability
SELECT(staff[name],
AND(
[department]="Operations",
[status] ="Active",
[shift] ="Days"
)
)
Don’t
- use a single line for expressions with multiple functions or conditions
- re-use the same SELECT()/FILTER() expression in multiple places
(make a Slice, instead)
- add ‘expensive’ Virtual Columns just to display a total, average, etc.- unless realtime data is needed (consider a Report, instead)
- nest multiple versions of the same sub-expression just to change one input.
Tip: create a separate Virtual Column or Slice for the sub-expression, then reference it in the main expression for easier readability.
