Can retool reasonably accommodate complex data entry requirements?
My apologies if the functionality I am about to describe exists, I simply don't have time to discover all of your use-cases at this time by learning your technology at length. That said, I do like what I have seen. I’m exploring the market and If the requirements are simply too broad I would appreciate it if you just respond generically with, we can’t do most of that.
I was hired to build a series of scientific data management applications that serve to collect natural resource data, automating the process of submitting this data to the EPA. There are a handful of existing clunky solutions made by 3rd parties however the team of scientists wanted a very streamlined data management system. More specifically editable data grids that respond instantly, have very robust validation based on relational rules such as foreign keys but also based on business rules driven by events that occur during data entry sessions.
For the forms and editable data grids, some of the most important requirements are:
- Zero noticeable latency during data entry and submission. I mean this literally, so long as this is handled there will be no scalability concerns, the system scale in terms of users and demand will remain relatively static over time.
- Data and submission must be done asynchronously via ajax or API (Single Page Application)
- A well organized and flexible menu navigation system with meaningful restful URLs. Are your forms meant to be hosted on say an “Intranet” or are we locked into your interfaces?
- Data grid traversal can be done as you would with a spreadsheet, tab to next field, return to save
- Prevent non-applicable characters from being typed into a textbox in certain situations.For example when a code for a scientific analysis method can contain only letters.
- In-place transformation, fields/controls can accept multiple possible formats, an alternate format will be translated or transformed upon tab or if invalid a message is displayed. For example if a month and day is detected in a date field, once a cell traversal occurs the system adds the current year
- A data row can be “test” submitted so that business rules can reside on the backend. For example if field A is blank,then field B is required. Appropriate messages are subsequently sent to the front end and the corresponding fields are designated as invalid.
- Data transformation and translation must be possible on submission but also on retrieval such as is the case for an edit form or in this case an editable row of an editable data grid. In other words translating or formatting the data in a manner that differs from how it is stored in a database
- HTML/Dom elements must be loaded dynamically with the exception of the parent element that contains the editable grid
- Extensibility: The backend class objects that process events, apply business rules and manage data storage must be able to be changed when business rules change. This means accessible back end changes preferably changes can be made on premise by a coder..
- Assign one to many and many to many relationships using dialogs while still maintaining a spreadsheet look and feel without interrupting the flow of the grid layout
- To validate types and formats on the front end then potentially revalidate them on the back end. In some cases formatting changes from year to year and is configurable through administrative tools. Validation must be dynamic.
- To check relationships using services/APIs while the user is interacting with the data grid
- Analyze a data row after submit for self consistency when for example there are multiple ways that a row can be submitted. For example if field A is not null and has a particular format, then field B is required.
- Since scientific precision or the lack thereof can have special meaning for scientific data, data sent to an update form or grid from the database must sometimes retain a fractional zero even though 1 == 1.0 and some json conversion libraries treat 1.0 as integer 1. If the database reports 1.0, then 1.0 must be what is populated in the corresponding text field.
- Provide functionality that is analogous to a database trigger when particular data is saved via a form or data grid. This is done for purposes such as journaling and to apply other business rules. They are events that can be triggered during data submission and in some cases during form/grid interaction.
- A universal submission methodology where data input can be intercepted, abstracted or transformed and event triggers can be applied regardless of the type of component being used to submit the data. In other words, form A must have access to the same backend libraries as form B.
- Informative and very specific user messages that appear and point the user to the offending field or field set when the back end or front end determines that a business rule or integrity rule is being violated
- The ability to have global configuration setting impact and dictate the behavior of forms. Not necessarily having dynamic fields/controls but definitely impacting the behavior of the form such as how validation functions, when new scientific equipment warrants a change in scientific precision and thus a change in validation and/or relational constraints. Also conditionally hiding or showing a field based on configurable settings that administrative users can change.
- Lists such as select dropdowns or lookup fields must not only be loaded via service but must also be dynamic and change depending on the user's context (Role based security/permission rules)
- RE vanilla forms, the ability to change the form behavior, what fields show up and what information can be submitted based on the user's role based security context. In many cases simply having many variations of a particular form is not realistic.
- Are there hooks or callbacks available to perform custom notifications?
Having become accustomed to these quality of life features, the stakeholders are now wanting to future proof this system. I am inquiring with you because applying the above requirements to a 3rd party system such as Retool offers some guarantees that I cannot in terms of system longevity.
Can your system reasonably accommodate some or all of these requirements?