Efficiently extract grid data for custom tables

In order to extract grid data to add it to custom tables, you would have to couple it with an actual form export with the grid.

Disclaimer: This information is subject to change

Example of getting individual grid cell values:

–@cashflow is a parameter passing in the grid question json data

–gridCellValues[0] references the first row of the grid

–‘$.”Qtr 1”’ references the column in the row

set @capy1q1=(select * from openjson(@cashflow,‘$.gridCellValues[0]’) WITH(vcInvestCapitalY1Q1 varchar(15) ’$.”Qtr 1”’))

set @capy1q2=(select * from openjson(@cashflow,‘$.gridCellValues[0]’) WITH(vcInvestCapitalY1Q2 varchar(15) ’$.”Qtr 2”’))

set @capy1q3=(select * from openjson(@cashflow,‘$.gridCellValues[0]’) WITH(vcInvestCapitalY1Q3 varchar(15) ’$.”Qtr 3”’))

set @capy1q4=(select * from openjson(@cashflow,‘$.gridCellValues[0]’) WITH(vcInvestCapitalY1Q4 varchar(15) ’$.”Qtr 4”’))

Example of returning all rows in the grid as separate rows in a sql select:

–inserts the grid rows into a custom table as separate rows/records

insert into tblInvestments(unqInstance_GUID,chInvestmentType,vcWBSCategoryCode,vcWBSCategoryName,intSortOrder,vcPercent,vcThisRequest,vcPriorRequests,vcFutureRequests)
select @isid,‘C’,* – ’*’ is shorthand for the seven column values being extracted
–from the grid data via the OPENJSON call below
from OPENJSON(@invC,‘$.gridCellValues’)
WITH(
vcWBSCategoryCode nchar(10) ’$.”WBSCat”’,
vcWBSCategoryName varchar(50) ’$.Investment’,
intSortOrder integer ’$.”Sort Order”’,
vcPercent varchar(8) ’$.Percent’,
vcThisRequest varchar(15) ’$.”This Request”’,
vcPriorRequests varchar(15) ’$.”Prior Requests”’,
vcFutureRequests varchar(15) ’$.”Future Requests”’)