November Happy Hour will be moved to Thursday December 5th.

Episerver Forms - how to query the database

Vote:
 

So we built a form using Episerver forms.    I know it has a CSV export there.    The client wants to automate the processing of these forms outside of episerver by directly querying the sql database to get new records for the day.  

Can you give us some insight into what tables + queries I should be looking at?    I looked in the tblXFormData table but it was blank.   

What db queries do I need to do to recreate the CSV export?   Thanks,

#227838
Sep 14, 2020 15:03
Vote:
 

The Episerver form submission data store into [tblBigTable] table the query which triggered on .csv download is similar to below:

You need to modify the query and use your own column (e.g. String04, String01 etc..) to fetch the data which you want to display.

 select  
CAST (R01.pkId as varchar(50)) + ':' + UPPER(CAST([Identity].Guid as varchar(50))) as [Id], R01.pkId as [StoreId], [Identity].Guid as [ExternalId], R01.ItemType as [ItemType],  
R01.String04 as "__field_48",  
R01.Boolean01 as "SYSTEMCOLUMN_FinalizedSubmission",  
R01.String01 as "SYSTEMCOLUMN_HostedPage",  
R01.String02 as "SYSTEMCOLUMN_Language",  
R01.Indexed_DateTime01 as "SYSTEMCOLUMN_SubmitTime",  
R01.String03 as "SYSTEMCOLUMN_SubmitUser"  
from [tblBigTable] as R01   
inner join tblBigTableIdentity as [Identity] on R01.pkId=[Identity].pkId   
where R01.StoreName='FormData_c8119de0-665c-431e-af5b-049112e99de2' and R01.Row=1
#227842
Sep 14, 2020 16:45
Vote:
 

Also, The Episerver does not recommend to use the database directly to write the query for fetching records and display them. The best way to use IFormDataRepository (inside EPiServer.Forms.Core.Data namespace) to get submitted data.

If Above query fullfill your requirements then welcome!

#227843
Sep 14, 2020 16:53
Vote:
 

Is there a way with this Forms Service API, to actually submit the form... put an entry in the CMS?    The reason why I ask is that we have a very highly designed form that might be difficult to break up but shouldn't change at all.

#228014
Sep 17, 2020 16:28
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.