Hi,

I am trying to generate a report based on data collected by ODSForm. Problem I'm running into is that the data from the questions are stored in a way I'm not familiar with and am not sure how to get at.

The table is called:
ODSFormSubmission

The Fields in the table are:
FormSubmissionID,
ModuleID,
PortalID,
SubmissionDate,
Submission,

The form questions and answers are all stored in the "Submission" Field as below.
*** Name *** MyName *** Address *** 123 MyAddress *** City *** MyCity ***

I have created a simple report in the reports module using:
SELECT ODSFormSubmission.*
FROM ODSFormSubmission;

What I get back looks like this:
FormSubmissionID ModuleID PortalID SubmissionDate Submission,
1 391 0 7/12/2007 4:55:21 AM *** Name *** MyName
*** Address *** 123
MyAddress *** City
*** MyCity ***
2 391 0 7/12/2007 4:57:50 AM *** Name *** MyName
*** Address *** 123
MyAddress *** City
*** MyCity ***



Does anyone know what this is called?
Does anyone know how to seperate the date out of the submission field?

Any help is greatly appreciated!!!
Thanks.

I suggest you contact the developer of OSDForm to obtain the required information.

Success!

Scrap the xsl, and just use a SQL query.

In our case, we had to pull out bids for an auction, names, and the time/date column from the submissions table:

SELECT top 4 CAST (substring(Submission,charindex('$',Submission),800) AS money) as [Leading bid],SubmissionDate as [Bid Date/Time], substring(Submission,
                       charindex('name ***',Submission)+8,
                       10) as [Bid by]
       from databasename.dbo.ODSFormSubmission
        WHERE ModuleID= 5651 AND charindex('$',Submission) > 0
Order by [Leading bid] DESC

 

In your case you can scrap the money conversion, but I'd imagine you'll have to use a pair of nested subtrings for each item - i.e. one to trim off the "Name ***" before the name, and another to trim from "*** Address" on.

Hope that helps.