I am working currently on data model for an application which includes a form / questionnaire.
I am not sure about, what is the best way to store "grid/table"-answers. Hope for your help, suggestions and best practices!
Here is description of the requirement:
There will be only 4 types of forms. Each has defined set of input fields. Some fields are conditional and only shown based on answer of previous field.
Based on the user input, user will have to fill one of the form.
Fields (questions) are grouped into 4 sections and have different answer types:
There are at least 7 different questions which require table as an answer. These tables have different amount of rows and columns. Some column headers contain years, which generated dynamically based on user input. Depending on question, user has to input in the table either number, text or select the check mark. Also user can add rows to some tables.
Example:
My data model so far (doesn't include grid-type answers)
My questions:
hii @AnnaTri,
I have created a datamodel to store the any kind of answer eg. choice, multiple choice, number, text and also table.
you can store table dynamically in form of rows into tableData entity.
I have tried to keep it futuristic so less or no modification are required while requirement enahancement.
I am also attaching the OML file for your help.
Hope this will help you.
Thanks & Regards,
Sanjay Kushwah
Hello @Sanjay Kushwah ,
thank you very much for your reply. Your suggestion has been very helpful. I'm diving a bit deeper into the data model you recommended and have a couple more questions:
About the Data Model:
Storing Table Data:
Would love to hear your thoughts on these points.
Best Regards Anna
Hi,
I was just trying to think ahead with possibilities that more question and response types gets added in future. You could still come up with a data model supporting that but it is not possible to support all possible patterns, more so because it is not easy to manipulate an existing data model.
One relatively easier option could be to store your questions as well as responses in JSON format. Each JSON format will be associated to a specific structure. Each structure would have necessary attributes to support specific question/response format. Your logic will capture the response into a structure, convert it into JSON and then store the JSON in database entity. In the same entity along with JSON you would store some attribute to indicate the compatible structure type which you will use during conversion from JSON to structure.
In future when any new question/response format is introduced which is not supported by existing structures, you would have to add a new structure in the logic and you are set to support the new format too.
Hope this helps!
Junaid
Hii @Junaid Syed, It is possible to save response in JSON format but there is one limitation when JSON is too big more than your field length than it while creating or updating record outsystems will throw String or binary data would be truncated error.
since than you need to determine the maximum length of your field/attribute but @AnnaTri also have to save Table format response where we can not define how much long it can be.
so it will be better to save table type response into row based database (form of multiple rows).
Hi @Junaid Syed ,
Thank you very much for your reply.
In my case, forms will stay relatively unchanged. They fall into just four categories, and their structures are quite consistent—think of them like different types of an application form.
Indeed, I was thinking about using JSON type but only for storing "table"-answers. In that case, I should take into account the limitations mentioned by @Sanjay Kushwah.
Your suggestion is definitely on my radar for future projects.
Best Regards, Anna