During our April 28 webinar, we explained how to create apps that use multiple tables and had connections between them. We had a very active group of app creators who asked questions about giving more power to their apps using references, Inline views, and the Slideshow view. Continue reading for details about the call and a video recording of the webinar!
Creating apps with multiple tables
You can create apps that connect multiple spreadsheets, this will deliver a richer user experience and will allow you to create more powerful apps. Here are 5 tips for creating apps with multiple tables:
- Break the app in two sections: Dynamic and static data. Create one table with the static data and a separate table for data collection sing references. This way you achieve faster data entry
- Treat static data as an information source. Add read-only data with information that will be used as a library: Lists of customers, project details, lists of constructions sites, students, etc.
- Connect tables using columns of REF Type. Make sure you have a column that would read the primary key of your reference table; it will become a drop-down in the app!
- Reorganize the columns. At the details level, you can re-organize all the columns in your app and show related data as different types of views: Charts, Deck, Table, etc.
- Add multiple data sources: Add data from other cloud services like Dropbox, Google Drive, Office 365, and Smartsheet for richer apps.
Questions from the webinar:
We couldn't answer all of the questions during the webinar. You can check our Q&A session in the recording or read some selected questions and answers below:
Can you choose only those Columns from the referenced table that you want?
Answer: Yes, you can use slices to filter out columns or rows. When you create refs, you can point to those slices instead of full tables.
Can we have inline views for charts that have drill-down?
Answer: We don't support drill-down in the inline view but when you tap on the view button, you'll be able to drill down on the data.
If you have one table reffed to 2 or 3 others ... can each of them have a different ref view?
Answer: You define the type of view for the end table itself. If the same table has the related data for other 3 tables (ie. three columns in that table are type REF and are pointing to different tables) then there will be only one type of view for that reference. We are working on a way to apply different types of views to the same table, stay tuned!
Right now, you can open a form via a button at the bottom or menu; can you also create a link to a new form from other places, e.g. use the "URL" option of the "Show" column type ... e.g. using today's example: if a user is entering a site report for a site that's not yet in the database, could there be a "add a new site" button right next to the Site drop-down?
Answer: Not just yet Pavel but this is something that app creators in the construction and engineering industry have been asking for. We will soon have a way to create a new item in the "static" table so you can then add an inspection to it.
Is the picture secure or can the app user download that picture or data?
Answer: All the data in AppSheet is encrypted between the app - server and server - cloud storage. If the image is visible in the app, then the user can technically take that image via different methods. Right click to download the image (web), take a screenshot, etc.
Backing up to Ref columns - I'm used to linking tables on table ID (Like your property ID column in Sites table). How is the Prop ID column used if not for linking?
Answer: In this case we used the name to make the data capture more understandable by the end-user. This is especially useful when you don't start from the list of properties but rather start directly from the form itself. IN that case, if your dropdown is only a long list of difficult to discern ID's, then it might be better to either pick a primary key that gives more information or start the data capture process from the list of properties rather than directly from the form.
How do we keep an order of those pictures, or whatever other data we keep in rows of that side-table?
How do i create a path on the map, as in a side-table of points, by dropping pins on the map? I'd like to keep them ordered, with groups still related to a single row of main table?
Answer: We don't support drawing a path to manage categories of items in the map. A good alternative is that you can use Formatting rules to change the color and add icons to the elements in your map based on the data in the app. Learn more here: https://appsheethelp.zendesk.com/hc/en-us/articles/206122068-Conditional-formatting
Can you still group items in the table view while utilizing the ref feature?
Answer: Yes, you can group them. Keep in mind that you are still limited by the total number of Nested Rows you allow in the inline view. Other than that, you can define all those elements when you make the position REF and the type TABLE.
Can you configure the app to alert persons that their approval is required after an update?
Answer: Yes you can. Learn more here: https://appsheethelp.zendesk.com/hc/en-us/articles/205860137-Workflow-Rules
Is there a way to make reports which email them from the mobile device
Answer: One option is Workflow rules. Let us know if this would help: https://appsheethelp.zendesk.com/hc/en-us/articles/205860137-Workflow-Rules
So we can now have dynamic drop downs in Smarthseet being feed from say an 'Asset' Sheet, a 'Locations' sheet and a 'Work type' Sheet?
Answer: Hi Stuart. We are not sure if you can have dynamic dropdowns in Smartsheet but you can use Smartsheet and AppSheet to create apps that have dynamic dropdowns. The dropdowns in the app will be dynamic based on the source data from the smartsheets.
Can you pull data from a single spreadsheet where the data is on different tabs?
Answer: Hi Christy. Yes you can! Simple add a new table, point at the same spreadsheet and in the Worksheet Name Qualifier give it the name of the tab you want to load. AppSheet will treat it as a separate table :) More info here:
How can we see other portfolio apps? Plz explain more details about that.
Answer: Christy, great request! We'll make a blog post to explain Portfolio apps and share with the community :)