How to query/display data from a Form table?

Eric Butler asked on March 5, 2020 17:07

Hi Kentico community,

I thought this was a pretty straightforward task but I can't find documentation on how to do it and haven't stumbled across a solution in the Admin. We're still back on Kentico 10, FYI.

I need to display a list of submitted form data on a webpage.

I've created the form, it's live and storing submissions in a database table correctly. Let's call the table Form_UserFeedback, the public feedback field UserResponse and an admin-only field IsApproved (boolean, defaults to False) so we can whitelist ones we want to show publicly.

I can't figure out what web part to use to query a Kentico Form table. Everything I'm seeing only looks at custom tables or page types (as the "Class type" of all "query" dialogs in these repeater and data source web parts) -- neither of which are useful here. I found the SQL data source web part, but surely I don't have to re-state my database, username and password just to get data out of the same DB everything else is in?

The query is easy: SELECT UserResponse FROM Form_UserFeedback WHERE IsApproved = True. I just can't figure out where to put that. Can anyone point me in the right direction?

(Also: I know how to create a Transformation to output the looped content. I just need to figure out the data source part.)

Thanks in advance.

kentico 10 web part

Correct Answer

Brenden Kehren answered on March 5, 2020 17:44

Use a Basic Repeater and pair it with a Query Datasource. The query datasource requires you to choose from a query which is in the system. To make this work, create or use an already created Page Type. Typically I use a page type with no database representation (or a container) and name it " < Your Name> Querys and Transformations". Then in the page type, add a new query. Your query can simply be

select ##TOPN## ##COLUMNS## from form_custom_formname WHERE ##WHERE## ORDER BY ##ORDERBY##

Name your query and save it. Then go back to the query datasource on your page and select the new query you created on that page type. In the basic repeater, place the ID of the query datasource in the property and set a transformation and you should be set.

2 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on March 5, 2020 17:19

Have you checked out the API Examples Kentico has?

This should get you what you're looking for.

0 votesVote for this answer Mark as a Correct answer

Eric Butler answered on March 5, 2020 17:33

Hey Brenden, Thanks for this link, and if I have to custom code this through the API, this looks like what I need. Of course now I'll have to read up on how to use the API and what to do with that linked code snippet, etc.

Is there really not a way to do this through a Web Part in the Admin interface? Seemed to me like a basic ask, especially since it's easy to do for custom tables that way.

0 votesVote for this answer Mark as a Correct answer

Dmitry Bastron answered on March 5, 2020 17:42

Hi Eric,

You can use Macro data source web part + Basic Repeater + Transformation:

Use similar macro in a datasource web part: {% GlobalObjects.BizForms["BizForm.UserFeedback"].Items #%}
Use Basic Repeater, it allows all data sources, not only pages
In the transformation just use {% UserResponse %} macro

1 votesVote for this answer Mark as a Correct answer

Eric Butler answered on March 5, 2020 18:29

Brenden and Dmitry, You all are both rockstars. Both solutions worked after I added an extra WHERE clause to Dmitry's: {% GlobalObjects.BizForms["BizForm.UserFeedback"].Items.Where("IsApproved = 1") #%}.

I'm very appreciative of the time and effort both of you took! Cheers.

0 votesVote for this answer Mark as a Correct answer