Community > Answers

How can we help you today?

Ask Question
Manage notifications
Subscribe to this question
75 views

What is the Query/SQL to get data from a Live Visualizer generated data structure?

What is the Query/SQL to get data from a Live Visualizer generated data structure? 

I think I see the variable length structure derived and maintained from _Submission, _SubmissionInf, _LiveForms?

Will you please provide the details?

erith erith
asked 04/19/2021 19:38
Add Comment
Last Activity 04/21/2021 19:01

2 Answer(s)

  • answered 04/20/2021 15:31
    Ashish Pachori

    All the submission of Live Visualizer is saved at LiveForms, LiveForms_Submission and LiveForms_SubmissionInfo tables.

    You can write query by your own.

    Thanks

    Add Comment
    erith
    replied 04/21/2021 17:08

    Thank you. I did understand that much. I've just had a hard time getting the data and images using a query. I want the form (list) and item (detail) data. I've asked Mandeeps. They refused to provide. Thought it would be an easy lift for them since they know their product inside and out. I'm also surprised both the query and API details for, at least, "read" consumption are not somewhere in the community. Still working on it. If I don't move to something else and get it done. I will post it in several places (DNN, Mandeeps, etc.). Thanks for the reply. 

     

    Reply
  • answered 04/21/2021 19:01
    erith

    Structure developed by client... don't ask... :) I hope the SQL helps someone. 

    SELECT s.SubmissionID
    ,s.ModuleID
    ,s.SubmissionGUID
    ,
    (
    SELECT f.FileName
    --si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    INNER JOIN dnn_vw_Files f
    ON si.FieldValue = f.FileId
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '3433BA65-A570-452E-BA00-A0D0A41D3B15' --'Family Picture - Family_Picture_FileName'
    ) AS Family_Picture_FileName
    ,
    (
    SELECT f.Folder
    --si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    INNER JOIN dnn_vw_Files f
    ON si.FieldValue = f.FileId
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '3433BA65-A570-452E-BA00-A0D0A41D3B15' --'Family Picture - Family_Picture_Folder'
    ) AS Family_Picture_Folder
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '00A3BABE-AC42-412F-8B38-C4007266BCA9' --'Family Name'
    ) AS Family_Name
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'C54F2C6E-111D-4BCE-BE7F-61A0F8949EFF' --'Family Anniversary'
    ) AS Family_Anniversary
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '3D463EA1-B281-4E61-9D69-5DC8987360D0' --'Family Phone (Cell)'
    ) AS Family_Phone_Cell
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '65D30FA7-5180-4BFE-A3E1-86B92D11C83C' --'Family Email'
    ) AS Family_Email
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '00D9C366-CD2A-4C72-BC52-62BBBD0F10AD' --'Family Address'
    ) AS Family_Address
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '29F02CAA-A70E-4362-BCB6-3CBBF2C37DD9' --'Family City'
    ) AS Family_City,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '7E8D9DA9-ED57-435D-BE52-4E390ED37302' --'Family State'
    ) AS Family_State,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '0669D0B1-6DC3-46E4-8AE9-85F951E50B22' --'Family Zip'
    ) AS Family_Zip,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '8DEB888F-2427-4042-9787-01A75141A865' --'Husband First Name'
    ) AS Husband_First_Name
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'F378C5C5-ABF4-4626-9168-5E2D7E576B00' --'Husband Last Name'
    ) AS Husband_Last_Name,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '667A858E-E3AD-4766-B297-7A5A607B6C79' --'Husband Phone (Cell)'
    ) AS Husband_Phone_Cell,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'B1C2CC9B-D117-4950-BEE5-CA659A1BC4B1' --'Husband Email'
    ) AS Husband_Email
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'C585B1CE-82CF-46F3-AB66-E4904C9233FF' --'Husband Date of Birth'
    ) AS Husband_Date_of_Birth,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '52579F36-35B3-43B9-9540-FC793523B50B' --'Husband Company'
    ) AS Husband_Company,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '92B79CB1-FC52-4553-9D32-20C9348A229D' --'Husband Occupation'
    ) AS Husband_Occupation
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'A7FF0656-AE43-4E51-9DC9-83ECDDFE46BE' --'Husband Getting to Know Him'
    ) AS Husband_Getting_to_Know_Him
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'D4C97A47-AFDB-4066-AC3D-F71036A9EEF1' --'Wife First Name'
    ) AS Wife_First_Name
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '1CF1A615-770B-44BC-8651-170B5331C5ED' --'Wife Last Name'
    ) AS Wife_Last_Name
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '6A35592E-5C31-4504-87B3-8D5C50ABA88F' --'Wife Phone'
    ) AS Wife_Phone
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'B5613C3D-70D8-472F-983E-F9FC3E87E897' --'Wife Email'
    ) AS Wife_Email
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '7E063FB2-B1E2-407B-9B8A-FBF639C94C04' --'Wife Date of Birth'
    ) AS Wife_Date_of_Birth
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'AA390175-D30B-4D6B-A0D2-0C66BAD0E0C2' --'Wife Company'
    ) AS Wife_Company
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'C3984411-0BDA-464E-AC86-45DFBEA8C00B' --'Wife Occupation'
    ) AS Wife_Occupation,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '0FD426F3-AB70-4B8B-B71A-29B27D431495' --'Wife Getting to Know Her'
    ) AS Wife_Getting_to_Know_Her
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '8E5E0FDE-A956-4C98-9EE6-793A4075550C' --'Child 1st Name'
    ) AS Child_1st_Name
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'C6E56F0F-8441-42EE-AC00-5C53967F050B' --'Child 1st Occupation'
    ) AS Child_1st_Occupation
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '7ABA33EF-0557-4A7F-8BF0-7477D1E462CD' --'Child 1st Getting to Know Them'
    ) AS Child_1st_Getting_to_Know_Them,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '0055A970-4C8F-40C6-B070-F5E47A3FD984' --'Child 2nd Name'
    ) AS Child_2nd_Name
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'DE9846E7-5719-4F55-AB3C-98D0EBE4B559' --'Child 2nd Occupation'
    ) AS Child_2nd_Occupation
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '6D56A746-4F97-4C2C-977B-1F35232FE392' --'Child 2nd Getting to Know Them'
    ) AS Child_2nd_Getting_to_Know_Them
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '092E3422-1879-4B0B-B5A8-FF991668E677' --'Child 3rd Name'
    ) AS Child_3rd_Name
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '30E5BE1E-C0A0-4F6E-BC02-017FE5234A16' --'Child 3rd Occupation'
    ) AS Child_3rd_Occupation
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '8B0EC5C3-6285-4F98-B0FB-E6C0E1945AF2' --'Child 3rd Getting to Know Them'
    ) AS Child_3rd_Getting_to_Know_Them
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    'D51854E6-968E-4395-94B4-3CC1A3E6A7EB' --'Child 4th Name'
    ) AS Child_4th_Name
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '4730C1B7-7E64-45D0-8112-51D9842E1C6D' --'Child 4th Occupation'
    ) AS Child_4th_Occupation
    ,
    (
    SELECT si.FieldValue
    FROM dnn_LiveForms_SubmissionInfo si
    WHERE si.SubmissionID = s.SubmissionID
    AND si.FieldGUID =
    '0C560EA3-1B92-4C67-892F-8D910F11BA6E' --'Child 4th Getting to Know Them'
    ) AS Child_4th_Getting_to_Know_Them

    FROM dnn_LiveForms_Submission s
    WHERE s.ModuleID = 1015

    /*
    '3433BA65-A570-452E-BA00-A0D0A41D3B15' --'Family Picture'
    '00A3BABE-AC42-412F-8B38-C4007266BCA9' --'Family Name'
    'C54F2C6E-111D-4BCE-BE7F-61A0F8949EFF' --'Family Anniversary'
    '3D463EA1-B281-4E61-9D69-5DC8987360D0' --'Family Phone (Cell)'
    '65D30FA7-5180-4BFE-A3E1-86B92D11C83C' --'Family Email'
    '00D9C366-CD2A-4C72-BC52-62BBBD0F10AD' --'Family Address'
    '29F02CAA-A70E-4362-BCB6-3CBBF2C37DD9' --'Family City'
    '7E8D9DA9-ED57-435D-BE52-4E390ED37302' --'Family State'
    '0669D0B1-6DC3-46E4-8AE9-85F951E50B22' --'Family Zip'
    '8DEB888F-2427-4042-9787-01A75141A865' --'Husband First Name'
    'F378C5C5-ABF4-4626-9168-5E2D7E576B00' --'Husband Last Name'
    '667A858E-E3AD-4766-B297-7A5A607B6C79' --'Husband Phone (Cell)'
    'B1C2CC9B-D117-4950-BEE5-CA659A1BC4B1' --'Husband Email'
    'C585B1CE-82CF-46F3-AB66-E4904C9233FF' --'Husband Date of Birth'
    '52579F36-35B3-43B9-9540-FC793523B50B' --'Husband Company'
    '92B79CB1-FC52-4553-9D32-20C9348A229D' --'Husband Occupation'
    'A7FF0656-AE43-4E51-9DC9-83ECDDFE46BE' --'Husband Getting to Know Him'
    'D4C97A47-AFDB-4066-AC3D-F71036A9EEF1' --'Wife First Name'
    '1CF1A615-770B-44BC-8651-170B5331C5ED' --'Wife Last Name'
    '6A35592E-5C31-4504-87B3-8D5C50ABA88F' --'Wife Phone'
    'B5613C3D-70D8-472F-983E-F9FC3E87E897' --'Wife Email'
    '7E063FB2-B1E2-407B-9B8A-FBF639C94C04' --'Wife Date of Birth'
    'AA390175-D30B-4D6B-A0D2-0C66BAD0E0C2' --'Wife Company'
    'C3984411-0BDA-464E-AC86-45DFBEA8C00B' --'Wife Occupation'
    '0FD426F3-AB70-4B8B-B71A-29B27D431495' --'Wife Getting to Know Her'
    '8E5E0FDE-A956-4C98-9EE6-793A4075550C' --'Child 1st Name'
    'C6E56F0F-8441-42EE-AC00-5C53967F050B' --'Child 1st Occupation'
    '7ABA33EF-0557-4A7F-8BF0-7477D1E462CD' --'Child 1st Getting to Know Them'
    '0055A970-4C8F-40C6-B070-F5E47A3FD984' --'Child 2nd Name'
    'DE9846E7-5719-4F55-AB3C-98D0EBE4B559' --'Child 2nd Occupation'
    '6D56A746-4F97-4C2C-977B-1F35232FE392' --'Child 2nd Getting to Know Them'
    '092E3422-1879-4B0B-B5A8-FF991668E677' --'Child 3rd Name'
    '30E5BE1E-C0A0-4F6E-BC02-017FE5234A16' --'Child 3rd Occupation'
    '8B0EC5C3-6285-4F98-B0FB-E6C0E1945AF2' --'Child 3rd Getting to Know Them'
    'D51854E6-968E-4395-94B4-3CC1A3E6A7EB' --'Child 4th Name'
    '4730C1B7-7E64-45D0-8112-51D9842E1C6D' --'Child 4th Occupation'
    '0C560EA3-1B92-4C67-892F-8D910F11BA6E' --'Child 4th Getting to Know Them'
    */

    Add Comment
Add an Answer