Is it possible to build widgets (e.g. forms, modals, lists, containers) from information stored in a database (or otherwise from data that can be modified in real time)?
So, the database would contain the layout of the form, how many buttons, the locations, etc.
So do you want to auto-generate and modify apps in real-time? This is not possible today.
I’d love to know more about your goal here. Do you want to generate a lot of apps or will your apps change in real-time?
For each client, I am making an app that my staff can use to fill out and edit Name, Address, Phone Number, etc. I’d like staff to be able to add new fields. For instance, if a staff member wants to add a field for “Referred By” or “Nickname” or whatever, I’d like them to be able to do this by just clicking on a button to “add new field” and choosing the field type (number, selectbox, text field). I can easily set up a script that then adds that field to the database. And I assume there’s a way to get the field to automatically show up in tables. But when someone wants to edit a row in the table, as far as I can tell you have to hard code the form/modal where they enter in the data.
Giving the staff access to modify the appsmith app would be a way to do this, but I think that would be too complicated, and too easy for them to accidentally mess things up. On the other hand, I don’t want to have to be the one to update the app whenever someone wants to simply add a field.
One thing I’m thinking of is just adding some extra fields and marking them hidden. Then I can activate them as needed. But that’s a kludgy solution compared to being able to dynamically generate the form/modal.
And now that I think about it, it’ll be much easier if I just set up two tables side by side. So, the first table has the client names. Click on a client name, and it opens up a table with two columns, which I’ll call key and value. Click on a key/value row, and then you get a form to edit the value. I can put multiple controls on that form (dropdown menu, textbox, whatever), and hide/unhide whichever control is appropriate.
Seems like this will work. I’ll get at it.
Thanks for the details. This is a really useful feature suggestion. Even though you have a temporary solution, I’d actually like to figure out if we can build this.
@anthony we’re working on a feature to auto-generate a page from a database table here
We’re also adding a JSON form that will automatically populate the fields based on the data sent to it
Do you think these 2 issues will solve the problem for you?
The “Create a form from a JSON object” requirement in the second feature request sounds like it would probably accomplish what I need.
As it turns out, after spending several hours working on this yesterday, I’ve decided that having two (ultimately more than two) tables is going to be advantageous anyway. What I have now is a postgresql table called “clients” that has one column, client_name (varchar, a primary key). Then I have a second table called “keysandvalues” that has three columns, client_name (a foreign key), key (varchar), and value (text). The primary key is (client_name, key). So this allows me to have different sets of keys for each client, and I think that’s going to be very advantageous.
I’ve got a proof of concept working that displays the clients table, and has buttons for “add client,” “delete client,” and “refresh table” (the latter probably won’t be needed once I get everything coded correctly, but it’s helpful for debugging). When you select the client in the first table, the second table populates with the keys and values for that client. You then have buttons for “add key/value”, “delete key/value”, and “refresh table”. On the right I have a form, that populates with the key (a disabled input) and value selected in the second table. It has an “edit” button, so if you want to change a value, you select the client, then select the key, then fill in the new value and hit “edit”. (I have a constraint so that you can’t accidentally delete a client without deleting all the key/value pairs first. I guess I’ll add a more streamlined way to delete a client and all pairs, with lots of “are you really really sure” popups, but for now this works nicely to prevent screwups.)
It seems to work, and most of the time I spent was reading documentation. This could have been implemented in just a couple hours (probably even less than that) if I didn’t have to teach myself how appsmith works. (I also ran into trouble while using Safari; I’m not sure if these issues are documented, but appsmith works fairly perfectly in Chrome, and fairly poorly in Safari; no big deal, once I figured this out I switched to Chrome.)
Next I’m working on creating a third table, maybe named “options”, that provides options for each type of key. I’ll add a drop-down menu to the form, in addition to the input box. If you select an option from the option menu, it’ll populate the input box. If you edit the input box, it’ll reset the option menu to unselected. Then when you hit the “edit” button it’ll edit the key to the value in the input box. Managing options will be done on a separate page, as it’s not as common of a thing to do, but it will be needed frequently enough that I don’t want people to have to edit the appsmith app to do it.
With “Create a form a JSON object” I could get more fancy, and have different controls besides just option menu and input box (which hopefully will one day be able to be combined into a combobox anyway). But for the most part, I think I’ve got enough to get this out to my staff for testing in the near future. (I probably should add a date-selection box, which can usually be hidden. I can probably use the same hack of updating an input box whenever the date gets updated using the date control, and just storing the value as a string. This will make sense for my use-case, as sometimes you’ll want to put the date as “Unknown” or “July 2021” or something else that doesn’t fit neatly into using a date-selection box.
(One thing I’m doing with all of this is automated PDF generation. Staff should be able to enter in the data, select a PDF template from a list, and download the filled-in PDF. I have some other tools I’m using for PDF generation. Some of which are ready for production, and some of which are just in the planning stages.)
I’m really impressed by how simple it was to create this app and also by how fast it runs. I’ve been trying to do it several other ways, struggling for months and ultimately giving up, and I whipped this up in just a few hours, without making a whole lot of compromises. I’m kind of glad I was forced to rethink things, as the way I was trying to do it previously would have been a lot harder. But it will be useful to solve very limited problems.
Both of those features would be excellent. The generate form from json especially would be useful.
It sounds like there is potential that this design may come back to haunt you later.
The model you are describing is known as EAV (entity, attribute, value). There are many articles describing why this type of model can be evil (for an admin/designer like yourself).
That said, the nosql/json world made EAV possible and even awesome, but from a data integrity perspective, it is problematic.
In these situations, it is a good practice to give the client table an integer “id”, and if you want the client name to be unique, put a constraint on it (are you using a RDBMS like Postgres or Mysql?).
And to flush out as many possible fields as you and your customer can think of in advance (address, phone, city, etc etc.)
Then for those random “attributes” that users may want to add, that sounds a lot like “tagging” which appsmith can do well with tagbox/select2 fields with multi-select. The tag list in the select2 can be dynamically generated from existing tags (which helps with duplicates and misspellings).
Thanks David. I’m using postgresql.
Yes, I’m using an EAV model in an RDBMS. And yes, it’s going to be a mess from a data integrity perspective.
The thing is, the way my firm is currently storing data is already really ugly. Edgar Codd would be rolling over in his grave if he ever came to haunt us. We have duplicate data in our contacts database and in spreadsheets in various places all over the hard drive, and in text notes, etc. We have checklists and outlines and emails and scanned documents and the kitchen sink. In our contacts database, we might have the phone number for someone listed as “don’t call them - call their son.” Etc.
The advantage of the mess is that it’s flexible. And staff will not give up that flexibility (nor should they). So the task at hand is to get the data into a somewhat better format for automation without giving up that flexibility.
Perhaps I’ll succeed, and perhaps I’ll fail, but I’m fairly determined to try.
kudos to you for attempting to wrangle that! May the BOFL be with you 
(edit: figured i should link to that as googling bofl returns 
results)
Here’s an okay description of the main issue I’m dealing with: https://rockset.com/blog/dynamic-typing-in-sql/
For example, I want a date field, say for date of death, but I want to have some data where the date is “N/A”, or “unknown”, which are two very different values, and both of which differ from NULL, which could be unknown (i.e. the person is dead but we don’t know when) or it could be that no one bothered to fill in that information yet. The date of death might be “August 2020”, with the exact date unknown.
I’m not going to code up every possible exception value, and I want to be able to make normal, efficient queries on the 90% of the data in the database that does fall into the nice normal structure.
No idea, yet, if there’s a good off-the-shelf solution for this or if I’m going to have to make it all myself.
If it turns out to be too difficult, I’m going to relax the “efficient” requirement. For 5-10,000 rows, it doesn’t have to be all that efficient, with the hardware of today.
This is an interesting data normalization problem. Maybe it would help if you break the date into 3 separate fields, so that the month and day can be optional.
I would also suggest a DateType field with options for Exact, Estimate, Unknown and N/A. Then, require all 3 parts of the date for exact, and hide all 3 for unknown and N/A. And estimate would allow only a year, or year and month.
You could also add a calculation field that builds a valid date to query, and maybe default the date to the 1st when it’s not provided. That way you have a valid date to query on the estimated ones, and no date on the unknown and N/As.
Updating the app to let users do all that, in a way that makes sense for people who are not programmers, will take a while.
And it still doesn’t capture the possible values listed on a death certificate (https://www.cdc.gov/nchs/data/dvs/DEATH11-03final-ACC.pdf).