OnyakTech Forms and Data Viewer
Combine the OnyakTech
Data Viewer and OnyakTech
Forms modules together to create advanced solutions. Use the Forms module to filter information displayed in the Data Viewer and click rows in the Data Viewer to drill down into the detail using the Forms module. Details are listed below.
The quickest way to create solutions with the OnyakTech Forms and OnyakTech Data Viewer is to use the OnyakTech App Builder DNN Module. The App Builder can create the solutions detailed below as well as many other types of data solutions.
Filter the Data Viewer with the Forms Module
- Add the Forms module and the Data Viewer to the same page or different pages. If you want the user to complete your form and display the information on a different page you will need to make sure you set the "Redirect URL" in Display Setup of the Forms module to point to the page where the Data Viewer is located.
- In the Forms module open "Edit Form" and below the editor under the "Save Changes" button is a list of tables in your DNN database. Select the table you want to search and click "Create new form from selected table" button. This will generate a form based on the table you selected. It's the quickest way to get started but you don't have to follow this step. All you need to do is make sure the controls are named after the columns in the Table or View you want to search. For example, if you are searching the Users table in DNN and you want people to be able to search by "User Name" you would create a TextBox control named "USERNAME". The token will be [TEXTBOX-USERNAME]. When you submit your form the query string will contain a parameter named "username" with the value the user entered into your form. The Data Viewer will match the query string parameter name with the column in your table (the Users table in this example) to filter your data.
- Once your form contains the controls you want to search with, open Display Setup and expand "Form Submission Action". Set the "Redirect To URL" to the page where the Data Viewer is located, this can be the same page as the Forms module, a different page on your site or even a page on another site.
- Check the option "Include All Form Values in Redirect" to make sure the details entered into your form are passed over to the Data Viewer using the Query String. If some of the controls on your form are optional then you will also want to check the option "Do not include empty values in Redirects". If you check that option the Forms module will not pass values for controls the user did not enter values into, this will become important if you are using the "Dynamic Search" feature in the Data Viewer. The Dynamic Search is enabled in the Data Source options in the Data Viewer, when this option is enabled the Data Viewer will automatically filter the data in based on the query string and session variables it finds and matches them up with the columns in your data source. This is a great way to setup a data mining solution quickly without having to worry about all of the details. However, if you pass empty values for parameters (due to the user not entering a value into a control that is named after a column listed in the Data Viewer) the Data Viewer will include this in the search and will show records where that column does not contain a value. If that is not the result you desire you will want to check the option "Do not include empty values in Redirects" in the Forms module.
- If the Data Viewer is on the same page as the Forms module open the Data Source Options in the Data Viewer and check the option "Ignore All Issues Until Postback". Checking this option will disable the Data Viewer until the user submits the search in the Forms module. If you want to display all of the data when the page is loaded then leave this option unchecked.
- If you want the Data Viewer to dynamically search your data for you based on the values entered into the search form check the option "Dynamic Search". If you don't check this option you will need to define the search in your SQL Statement using tokens. You can use dynamic SQL by using tokens. There are three types of tokens. Session based [SSN-XXX], Query String [QRY-xxx] and User Prompted [PMT-XXX]. If your search Form contains a [TEXTBOX-USERNAME] control to seach the DNN Users table you will enter your SQL as Select UserName From Users Where UserName like '%[QRY-USERNAME]%'. The "QRY" indicates that the value will be passed to the Data Viewer module in the query string. You could also set the option to store the values in the users session when they submit the form, in that case you would use [SSN-USERNAME] in the SQL statement with your Data Viewer.
- That's it. As long as the control names in your Form module match the column names you are searching in the Data Viewer you will be able to search using a custom search form with the OnyakTech Forms DNN module to Submit the search and the Data Viewer to display a custom listing of the results.
Do not use brackets with your fields and tables when using dynamic parameter tokens, they will conflict and your tokens will not render properly. For example, don't use SQL like this: Select [UserName], [UserId] From Users. Both UserName and UserId will be treated as a dynamic token in the Data Viewer and in this case it's not what you want.
Setting up the Data Viewer DNN module to open the details of the row clicked in the Forms module follows the same approach as searching with the modules. The key is to make sure the Control Names match the values passed in the Query String or Session.
- After you add the Data Viewer and Forms module to pages in your DNN web site, open the Data Source options of the Data Viewer and set the "Redirect URL on Row Click" to the URL of the page where the OnyakTech custom Forms module is located. By default the Data Viewer will pass the value of the primary key in the URL. You can extend this by adding additional columns in the query string by usign the tokens to represent the values of the columns in the row the user clicks. This can be useful when the Primary Key in your SQL statement may not be the primary key value of the table your Forms module is connected to.
- In the Forms module open "Edit Form" and below the editor under the "Save Changes" button is a list of tables in your DNN database. Select the table you want to search and click "Create new form from selected table" button. This will generate a form based on the table you selected. It's the quickest way to get started but you don't have to follow this step. All you need to do is make sure the controls are named after the columns in the Table or View you want to search.
- If your Form contains the TABLE and PRIMARYKEY tokens, you can remove them and set these in Display Setup under the "Form Data Options" section instead. They both act the same in most cases. However, if you are using Events in your Form to dynamically change the values listed in a dropdown control or to set the value of a Textbox based on the value selected in another control that pulls the data from different table in your database you will want to use the options in Display Setup instead. The OnyakTech Forms events only have access to the connection strings defined in Display Setup and not the TABLE and PRIMARYKEY tokens in your form. The only required options in the "Form Data Options" section of Display Setup in the Forms DNN module is "Save to Database" (if you want to save the users changes, otherwise you can ignore this option), Table and Primary Key.
- If your table is located in a different database enter the connection string to the database in the "Connection String" option. Otherwise leave it empty to use the same connection string DNN is using.
- That's it. You can now click a Row in the Data Viewer to view the details of that record using a custom layout in the OnyakTech Forms DNN module. To see more advanced configurations of data searching and drill down use the OnyakTech AppBuilder module to create the solutions and then view the configurations in the Data Viewer and Forms module as your guide.
TIP: The Data Viewer requires a unique value in order to enable most of its features, you define this column in the "Primary Key" in Data Source options. In most cases you will set this to the primary key of your table or the Data Viewer will automatically set it for you in some features. It's not required that the column you set in the "Primary Key" option is actually defined as a primary key in your database. You could also reference a SQL Server View and enter a column that is the unique column of your View without it actually being defined technically as a primary key. All that is required is that the value is unique.
The following was posted by Michel in the OnyakTech Forums, Michel has some really creative techniques using the OnyakTech modules and has helped out a lot of people with the OnyakTech DNN modules through the OnyakTech Forums.
Create a page
add a form module and under, add a dataviewer module
IN FORM MODULE :
Create a form like this
Month : [DROPDOWNLIST-MONTH]
Year : [DROPDOWNLIST-YEAR]
Race Type [DROPDOWNLISTE-RACETYPE]
You can also add : [SUBMITRESET::Text=Clear form::cssclass=btn] (allows user to clear all fields in form)
The name of the fields you use in the form (ex : MONTH) has to be the same that colums name in your database
Save your form in the name of your choice with the option "save as form" under the editor.
Exit the editor
Open "display Setup" and
1) choose your form to display
2) In form data option, select nothing (everything must be uncheked)
3) in "form submission action" , in "redirect to URL" set the URL of your page (this page ! )
4) check the checkbox "include all form values in redirect... (I'am not sure it is exactly the right text because mine is in french , but it is the first checkbox of this section)
5) Check the checkbox "not include empty values in redirect" (the same, mine is in french. It is the second checkbox)
6) in "other options" section, check "force user to set values in control list" (it is the first checkbox in this section)
7) in "other options" section, uncheck "auto profiling...." to be sure to not make mistakes
8) Click on update at the bottom
Set the values for each dropdown box with direct values or SQL values
For that select your controle name and click on "open control panel for selected control"
set the values in "data option" section
IN DATAVIEWER MODULE
In "Data source option"
1) choose your data source and set your sql statement for source data
2) In "Data Handling" section check the checkbox "Enable Dynamic Query String Searching:"
3) if you want, you can add custom messages in "custom messages" section
4) in "save changes" section, click on "update"
And now it would be work
In datavieawer all items are displayed if nothing is selected in form
if user make some choices in form and then press on "submit", dataviewer will only show matched items
If you want, you can also put your dataviewer in another page (not the same than form).
You just have to adapt the "redirect URL" in form settings with the right URL (the URL wher yous dataviewer instance is)
Hope that can help you
Let me know