locked
Generating a report from Dynamic form RRS feed

  • Question

  • User585649674 posted

    I am creating a framework which has multiple forms. Each form can have multiple fields like textbox, radio button, check box etc. The user will submit the form.

    I need to save the dynamic form field value in DB. I was thinking to save the submitted form data as individual records in JSON or XML structure. I did not want to save each field data for each form as individual record.

    But then i also want to generate report for each type of form. E.g i need to generate report on Form 1, like select field1, field 2, avg(field3) group by field 1. it will be different set of query for each forms.

    How to achieve this. Is there any best design practice being followed. How does theRCQ or products like IBM RCQ, service now, Jira works where user can select columns and generate reports. I understand in products like jira the columns are fixed. And not dynamic as in my case.

    Form Id Form Name Enabled
    1 Car Service Request true
    2 Service Complaint true
    3 Car booking true
    Control id  Form Id Field Name Type
    1 1 Customer Name textbox
    2 1 Service Start Date datepicker
    3 2 Complaint Date datepicker
    4 3 Customer Name textbox
    5 4 Booking Date datepicker

    Note we do not have NoSQL, we can use RDBMS only.

    Wednesday, May 15, 2019 8:30 AM

All replies

  • User475983607 posted

    The problem you are trying to solve is not clear.  It seems you have a minimal schema to support the html form rendering.  

    Are you having a problem generating the HTML Form?  Do you need help with a routing design?  Can you share your code and explain what issues you are facing?  Or do you need help designing the entire application?

    How to achieve this. Is there any best design practice being followed. How does theRCQ or products like IBM RCQ, service now, Jira works where user can select columns and generate reports. I understand in products like jira the columns are fixed. And not dynamic as in my case.

    It is not possible to provide guidance without understanding what you are reporting on.  However, most database providers have built-in reporting tools.  For example, SQL Server has Reporting Services.  You'll need to read the doc for the DB provider you plan to use.

    Wednesday, May 15, 2019 1:20 PM
  • User585649674 posted

    The client requirement is a simple form entry. they have upto 17 forms, they wanted to add new forms. they want to add new fields to existing form. Hence i cannot hard code the aspx/view pages. I am looking something like this.  https://www.youtube.com/watch?v=3o92v9DNfz0

    I have already created logic to generate html. When a form is selected, it automatically fetches the dynamic form and display in html page.

    I want logic to save the data to database. Each form will have 15-20 fields. one form can be submitted 1000 times.

    After submitting I want to generate excel report on each form in excel sheet. Each row in form will be each submission, where each column in dynamic field. I wanted an option to configure query for each form. Please note since it is a dynamic form the column names will not be fixed.

    Thursday, May 16, 2019 6:43 AM
  • User475983607 posted

    I have already created logic to generate html. When a form is selected, it automatically fetches the dynamic form and display in html page.

    I want logic to save the data to database. Each form will have 15-20 fields. one form can be submitted 1000 times.

    I assume the HTML form is rendered by looping over a query result set.  That means you know the form ID and input element names.

    After submitting I want to generate excel report on each form in excel sheet. Each row in form will be each submission, where each column in dynamic field. I wanted an option to configure query for each form. Please note since it is a dynamic form the column names will not be fixed.

    Confused.  Why can't you simply loop over the request collection to fetch the submitted values?  This is the same concept as rendering the HTML.  Save the value in a new table that has at least the form Id and control Id. 

    Reporting is a simple matter of joining the form, control, and values table.  

    Thursday, May 16, 2019 11:06 AM
  • User585649674 posted

    Why can't you simply loop over the request collection to fetch the submitted values?  This is the same concept as rendering the HTML.  Save the value in a new table that has at least the form Id and control Id. 

    What is the data schema you think would be better option 1 or 2 ? which is better for generating excel report. As columns values are rows in option 1. In excel each row need to one submission and excel columns to be values. Each form will have separate excel sheet

    option 1

    Submission Id Form Id Field Id Value
    1 3 1 some value  1
    1 3 2 12
    1 3 3 19-Jun-2019
    1 3 4 true
    2 3 1 some value 2
    2 3 2 12

    or option 2

    Submission Id Form id Form Collection
    1 1 {name: 'some value', experience : 21; date of birth : '19-jun-2019'}
    2 21 {vehicle:'car', licenseplate:'AFH123'}
    3 12 {shirtsize:'medium', location :'place of birth'}
    4 12 {name: 'some value', experience : 21; date of birth : '19-jun-2019'}
    Thursday, May 16, 2019 11:21 AM
  • User475983607 posted

    nideeshm

    What is the data schema you think would be better option 1 or 2 ? which is better for generating excel report. As columns values are rows in option 1. In excel each row need to one submission and excel columns to be values. Each form will have separate excel sheet

    Excel is nothing more than a format.  IMHO, Excel is a non-issue.  Your first step is to solidify the exact reporting requirements then pick an option that best fits these requirements.

    Option 1 is good for filtering on individual values and option 1 easier for optimizing queries.   Option 2 requires extra C# code (or SQL) to convert JSON to an object (or result set).  Option 2 is good when filtering by Form Id is the deepest filter requirement.

    Thursday, May 16, 2019 2:02 PM
  • User585649674 posted

    Excel is nothing more than a format.  IMHO, Excel is a non-issue.  Your first step is to solidify the exact reporting requirements then pick an option that best fits these requirements.

    To generate the excel report, i need to write a query i cannot use SQL as the number of forms and fields are not fixed.  I want to write a dynamic query which can be executed using ORM . I want to have a table like below with ORM query against each form. How can i execute this ORM query in C# ? When user adds a new form field, i would have to change the query column in the below table and the report will have the new column.

    Form id Form Name Enabled Query
    1 student detail true from object_model select name, age
    2 parent detail true from object_model select parent_name, parent last name
    3 extra curricular detail true from object_model select curricularname, startdate, enddate
    Friday, May 17, 2019 1:39 AM
  • User-1764593085 posted

    Hi nideeshm,

    I want to write a dynamic query which can be executed using ORM .

    In my opinion, ORM is used for CRUD and not applied for dynamic generating.

    https://softwareengineering.stackexchange.com/questions/357802/dynamic-query-building-for-report-generation-tool-suggestion-for-better-approa

    Xing

    Friday, May 17, 2019 9:36 AM
  • User475983607 posted

    To generate the excel report, i need to write a query i cannot use SQL as the number of forms and fields are not fixed.  I want to write a dynamic query which can be executed using ORM . I want to have a table like below with ORM query against each form. How can i execute this ORM query in C# ? When user adds a new form field, i would have to change the query column in the below table and the report will have the new column.

    Form id Form Name Enabled Query
    1 student detail true from object_model select name, age
    2 parent detail true from object_model select parent_name, parent last name
    3 extra curricular detail true from object_model select curricularname, startdate, enddate

    I do not understand your approach.   Have you written any code that you can share?

    I would approach the problem differently.  You're essentially moving HTML Form markup from the View to a Table.  I would focus on this fact as rendering a report or a view is pretty much the same problem.  

    Friday, May 17, 2019 11:19 AM
  • User585649674 posted

    I do not understand your approach.   Have you written any code that you can share?

    I havent started writing code. I wanted to fix the approach before starting the code. I thought the object query can be taken as string from configuration / database and execute in C#. 

    Tuesday, May 21, 2019 5:27 AM
  • User475983607 posted

    I recommend building a few proof of concepts.  This help you find a path and highlight and challenges.  IMHO, populating an HTML form and populating a report are very similar.  

    I havent started writing code. I wanted to fix the approach before starting the code. I thought the object query can be taken as string from configuration / database and execute in C#. 

    The approach, serialized object stored in a table, might be fine.  I cannot answer this question as I do not have you reporting requirements.  This approach requires reading the entire table to filter by a property within the serialize object.

    Tuesday, May 21, 2019 10:45 AM
  • User585649674 posted

    I have started some coding. I need help in forming the compound field which can be repeated. A compound field  is nothing but mix of one or two field as shown below. In my database there are normal fields and compound field. The compound field has a set of child fields. I am adding the parent field name to child field names for compound field. In the below image passportnumber text box has field name as "passportnumber" but  family member name text box has field name as "Familymembers_name"

    capture https://github.com/nideeshmanian/dynamicformspoc/blob/master/Capture.PNG

    I have the below code to get form collection and covert to XML.

    public string ToXML(IFormCollection formcollection)
            {
                string xml = string.Empty;
                StringBuilder sb = new StringBuilder(); //use string builder
                using (XmlWriter writer = XmlWriter.Create(sb))
                {
                    writer.WriteStartDocument();
                    writer.WriteStartElement("Collection");
    
                    foreach (string key in formcollection.Keys)
                    {
                        //if (key.Contains("_"))
                        //{
                        //    int loopcount = formcollection[key.ToString()].ToString().Split(",").Count();
                        //    //for (int i = 0; i < loopcount; i++)
                        //    //{
                        //    //    writer.WriteNode()
                        //    //}
                        //}
                        //else
                        //{
                            writer.WriteElementString(key.ToString(), formcollection[key.ToString()].ToString());
                        //}
                    }
    
                    writer.WriteEndElement();
                    writer.WriteEndDocument();
                }
                xml = sb.ToString();
                return xml;
            }


    Which is generating XML like below. As you can see the three record values are comming as comma separated for <FamilyMembers_Relation>

    <Collection>
    <Activity>2</Activity>
    <MemberID>A12340-01</MemberID>
    <PassportNumber>ASDQWER</PassportNumber>
    <FamilyMembers_Relation>Father,Mother,Father</FamilyMembers_Relation>
    <FamilyMembers_Name>fathername,mothername,</FamilyMembers_Name>
    </Collection>

    I want the XML to be like this. I am not well versed in XML. I kind of understand to check for underscore then create node, then split by commas to add them indivudually. Please help me here.

    <Collection>
    <Activity>2</Activity>
    <MemberID>A12340-01</MemberID>
    <PassportNumber>ASDQWER</PassportNumber>
    <FamilyMembers>
      <FamilyMember>
         <Relation>Father</Relation>
         <Name>fathername</Name>
      </FamilyMember>
      <FamilyMember>
        <Relation>Mother</Relation>
        <Name>mothername</Name>
      </FamilyMember>
    </FamilyMembers>
    </Collection>

    I am planning to save each form submission as XML. I would also want to generate excel report like below

    Passport Number Family Member relation Family Member Name submitter date Submission number
    z1234 father john 19-jun 2019 1
    z1234 mother angela 19-jun-2019 1
    Z3245 father dan 20-jun-2019 2

    Monday, June 17, 2019 3:57 AM