LinkedIn
Twitter
I have some users that want an app with dozens of unique and complex forms, and have been considering something like an EAV model to handle the data. Here are my thoughts:
- There can be
nnumber ofform#tables, each of which have a distinct column structure based around their form views. - There is a single
datatable with aschema_idcolumn and enough additional columns to hold the data from your largest associated form. Most columns are plain text / varchar() with the exception of some special datatype columns such as datetimes - Each form has an OnSave event that adds a new row to the data table and casts all data from the forms as plain text. The data in the
form#tables could then be automatically purged or alternatively the filter out all existing rows toggle could be enabled to prevent loading it. - To edit the data, an action is created that references a linktoform() formula from a schema table which repopulates the specific form as it existed at save
Benefits:
- To add a new table you simply create the database schema, form view, and add a linktoform() schema to cast the data back to the original form.
- The converse of this requires the creation of several actions, and workflows, detail views, etc. which are specific to each table. If an app has dozens of unique form tables this becomes a lot to manage.
- Filtering project or user specific data only has to occur on the
datatable instead of every form that may exist
Challenges:
- detail views on the ‘data’ table would be wonky.
- You could add a column to the schema table that holds a LIST() of column descriptions, and perform lookups to populate column descriptions
- You could constrain the application detail views to simply be the downloadable PDF’s that are generated on a form-by-form basis
@tony @Grant_Stead @LeventK @Steve
Someone please tell me why this is a bad idea and talk me out of this
I have production applications (high level daily safety paperwork) which use a single table of data and a reference to a schema table to dynamically change the column descriptions in-app. This model is great as it gives the appearance of having several forms, but it only really works when the forms are simple.
If you want to add additional layers of complexity (rows being shown, edited, or required based upon some condition of other answers specific to that form, or suggesting values specific to a formtype), then the aforementioned model becomes rather complex. You have me thinking now, if it would be possible by adding some additional tables to handle the dynamic form aspects. So you would have a schema_columnName, schema_editable, schema_required, schema_suggestedValues…
Maybe I can find a way to store my data as JSON instead.
Or maybe I’m trying to fit a square block through a round hole as Grant implied 
As mentioned, I have a working version for safety forms where every question is a yes/no. My organization had existing safety forms that were already constrained in this manner, so it was simple enough to digitize them. The safety team is also able to add new formtypes or re-issue new versions of existing forms, so long as they are constrained in a similar way. Despite having over 32 different forms and printouts, the apps ‘spec’ view still looks like this:
So, I can attest that the methodology is sound for the simple case. However, I don’t think I will be proceeding forward with the complex case anytime soon.
I knew I bookmarked this for later digestion. 
@Jonathon I’m currently fighting with a system that I made for a client that does exactly this. The idea was to create a platform to capture survey data, but also to allow people to create the surveys, all the questions, the flow of things, etc. inside the app as well.
We created a table to define how each question should look in the form, then created a “Live_Form” that conforms itself based on whatever was specified for what type of data is being collected, how it should look, what values are allowed, etc.
We eventually included a way to pass information from one question to the next, so you could take the answers from one question and use them as options in another. We also make use of the “background record creation” to handle creating skip records; when someone skips a question we still want a record noting they’ve skipped something, but we don’t want to force the users to press “Skip… skip… skip…” to go through dozens of forms, so we create them in the background.
The data entry table is pretty wild; since you can’t dynamically change column types we instead had to include one of every type (well, almost; there’s a few we don’t use), then pull all the conforming info from the instructions table and show the one we need.
It all sounds very good and all… but this is not the way to collect data like this. Trust me. After we got into actually using the app, and real amounts of data started coming in, we came to the realization that we really need the data in their own individual tables.
Don’t get me wrong, the form works - and works rather well despite some HORRENDOUS looking formulas - it’s everything after that that’s a nightmare now. All the things we would like to do with that data would be a whole lot easier to accomplish if things were their own tables.
The idea is sound, even getting it working is possible, and honestly it would be a great way to solicit data from clients about what they want in their app. Like… you could give a tool like this to them and say, “Here, you design your forms, the questions, etc.” - then take that data and build a real table out of that.
But the time-saving aspect we thought we were going to get - where we didn’t have to create all those forms, with skip logic, formatting, etc. - we’re now making up for with having to come up with clever ways to handle data without making the app un-usable.
Hope it helps!
Thanks @MultiTech_Visions - I had been considering a model exactly as you posted. In essence, it would be no different than the example I shared with Grant above, except with extra columns available for different datatypes as you mentioned.
I think this idea goes off the rails when you try and allow people to define their own forms / questions within the app - Thats an additional layer of complexity that I have a hard time wrapping my head around.
If the addition of new forms was left to the app developer, you could slice the data from within the app to create the illusion of each form having its own table. One immediate benefit here is you could then have a single [photos] table that references the dynamic forms table, instead of needing an entire [photos] table for each individual table (each table can only be ispartof for one other table). If that makes sense.
Really interested in the navigation schema you’ve developed, with the left/right arrow buttons. 
There’s a lot going on to make that work… I’ll try and briefly cover it.
Those “View Previous answers” actions are looking at the navigation buttons for the form that was just saved: if they pressed the back button, the “View previous answer” action fires, if they press the next question button, the next question action fires, etc.
The client I made the system for used it to digitize 47 questionnaires; then they went out and started collecting data. 
(Over 1800 rows per respondent.)
Things uh… got out of hand really fast. 
So now the problem we have is there’s WAY to much data in one table, and in order for the client to use the data for any sort of analysis we have to slice it up into the separate 47 questionnaires; it takes time for the system to separate out all the records.
We’ve actually gone as far as to create a separate app to use for “Scoring.” Thankfully some of the questionnaires need further input after the data is collected from the respondent, so having a workflow to “Finalize” the data was actually called for.
In the scoring app, we have 1 table for each of the questionnaires (like we should have done in the FIRST place!) with a ton of LOOKUP() formulas to pull in each answer. Combined with some app formulas to check values, do pre-analysis calculations, summaries, etc. and it’s actually a pretty nice workflow… when it works.
There’s SOOOOO many moving parts 
that even minor tweaks cause catastrophic failure.
