Modern web applications are excellent tools for managing, viewing, and collecting complex data models. With a sleek interface backed by a well-crafted schema, it’s easy to take total control over the data you need to acquire, process, and analyze. But most of the time, doing the hard work to build a full, responsive, performant, and secure CRUD application is overly costly and expensive.
Take a moment to think about the number of tasks a full-stack engineer needs to complete, error-free, just to create one of these applications, including:
- Building the database itself, with the tables and relationships required to represent your data model
- Implementing an ORM layer to interact with your data
- Defining & parameterizing queries for your CRUD operations
- Building out web services to allow a frontend application to manipulate your model via an API
- Developing a full, responsive, extensible web application in HTML, CSS, & JavaScript with all of the form inputs, data views, routing, authentication, account management, and search capabilities your data model demands.
Now imagine you and your firm are responsible for paying for all of this work to be completed, and you’re on the hook for even more if your model, interface, or database infrastructure change. Of course, this is not your only option. If you don’t want to pay for all of this bespoke development, you can use a pre-built solution. Google Forms & SurveyMonkey immediately come to mind. Both solutions let you build up a form interface online and send it out to recipients for get your ingest process started. But what are you really getting out of a simple solution like Google’s?
- Your data is limited to a totally flat table format — if it doesn’t fit in a single row of a spreadsheet, you can’t model it. That means no one-to-many relationships, no data hierarchy, no foreign-key relationships, and minimal complexity. For demanding modeling applications, you’re out of luck.
- You get a clean interface for inputting data, but it isn’t very flexible. Layout is limited, and nothing more involved than a simple step-by-step wizard can be deployed to help your users fill in the data you need.
- Access to data is constrained to a Google Sheets spreadsheet. Spreadsheet software is great in a pinch, but it’s no substitute for a real database. You’ll find yourself severely limited when searching, reporting, visualizing, or simply browsing larger data sets.
Over the last few months, the Composable team has been building our solution for modeling data at scale, and we’re announcing it today with Composable DataPortals. We’re committed to providing a solution that prioritizes your model first and foremost, enabling the entire process from start to finish without needing any particular knowledge of database management, web development, or programming.
Here’s how it works:
- Define your data model and interface in Excel. Complex page hierarchies, tabbing, one-to-many relationships, layouts, groupings, display conditions, and validation rules are all supported out of the box, using a simple table-based format for defining everything.
- We generate your database for you. From what you specify in the Excel, we take care of the entire database layer, right on a server that you own and have full control over. We’ll set up your foreign keys, ensure everything is strongly typed, and ensure your validation rules are always enforced.
- There is no Step 3. You’ve already finished. Just send a link to you DataPortal to anyone with a web browser, and we’ll handle the rest. We take your model and the layout rules you’ve defined and produce a sleek web interface for collecting data from your users. With features like auto-save, revision history, custom styling, and intelligent navigation shortcuts, you can be confident that your users and employees will have no problems providing or viewing the data you need quickly and easily. We also handle all of the authentication.
Let’s dive into a simple example to see how to get started with DataPortals.
We’ll be designing a quick survey form. In the screenshot below, you can see our design for the ‘Survey’ page. We have fields for First & Last Name, DOB, Gender, and a free-form Feedback box. For each field, we define a row with a few pieces of metadata, including:
- Name: A whitespace-free name for the field. Think of this as the name you’d give this field’s column in a database.
- DisplayName: A human-friendly label that will be displayed above the input box for the field on the form interface.
- Note the special case for ‘Label’ fields — DisplayName contains rich text written in Markdown.
- Description: A longer clarification that can be displayed in a tooltip to more fully explain the field.
- Type: The name of the C# data type of the given field.
- ControlType: The type of form control that will be shown to the user to input their data for this field.
- Required: Indicates whether the field is required.
Note the special Type present for our ‘Gender’ field. Here, the ControlType is a Category (a drop-down list of options), so we reference a list of options defined in a separate sheet in the Excel workbook, called ‘Categories’. In this sheet, as shown below, simply define all of your drop-down lists in a single column each, with the name of the list in the first row. Then, you can reference the list as a Type via the ‘Form.ListName’ syntax.
Now that we have our survey page (or, as we refer to pages, containers) defined, let’s create a parent page called ‘SurveyList’ that allows us to create multiple survey instances, creating a one-to-many relationship in our data model. Here, we use the ControlType ‘Table’, indicating that we’d like a table of all the survey instances. Note that the data type for the Table field is ‘[Form.Survey]’, or an array of our Survey containers. We also pick out some of the fields from our Survey container to be displayed in the table, via the Columns column shown to the right.
We have only one last step before we’re ready to begin using our form. We need to create a ‘Master’ sheet that points Forms to the container we’d like to use as the root of our form. We’ll use the ‘SurveyList’ container we defined in the last step. Our ControlType here is always ‘Link’, and we select the container by writing its full-qualified name (including the “Form.” prefix) in the Type column. Also in this ‘Master’ sheet, we define the name of our form and the name of the database that will be created to store its data. Here, I’ve chosen ‘SurveyResponseManager’.
And, we’re done! Let’s go upload our form. From inside the Composable web app, click on ‘Forms” in the navbar and then drag the Excel file into the file drop area on the page. Once the processing completes, you’ll be able to open your brand new form and see our table of surveys.
Let’s create one using the “Create New Survey Entry” button and see our layout.
All of our fields are ready for filling out. Our instructions label is nicely formatted from the Markdown we wrote, each required field is properly designated, and navigation is quick and easy with breadcrumbs.
Want a closer look? Try uploading the source Excel file here: Download.
Now that our front-end is taken care of, let’s see what Composable generated under the hood to store our responses. Browsing to your SQL Server instance, you’ll find a new database created for your form! Many of these tables are generated to support things like authentication, access control, and revision history, but you’ll be sure to find auto-generated tables to represent the full data model defined in your Excel file. Take a look the sample data I’ve added below to the Surveys table via the Form interface.
You’ll notice that all of the fields you defined are present and strongly typed in this table. You’ll also get some useful metadata, like the user who filled out the survey and when they did so. (Another quick note: we automatically protect you from malicious users who would try to corrupt your data.)
Just like that, we’ve gone from scratch to a fully operational data modelling and acquisition platform with just a few tables written in Excel. With a bit more effort, you can create much more powerful forms and data models, like the one below, with complex hierarchies, field grouping, dynamic layouts, nested tabbing, complex validation, and more. And, if you don’t like the way it looks, you can change the design yourself.
We’ll be following this post up with more tutorials about how to get the most out of DataPortals. If you run into any trouble, don’t hesitate to comment here or contact us.
- Deploying Composable Resources via Powershell - December 1, 2022
- Composable APIs via Powershell - December 1, 2022
- Enabling Long Paths in Composable - April 6, 2022
