Sending Form Data to SQL Server | GlobalForms 10 Administration

Save submitted data to SQL Server for reporting or other systems, either selected fields or the whole submission. This uses the SQL Query form action; set up the database connection first, as described in SQL Query Form Actions.

Save selected fields

Write an insert statement in a SQL Query action, using form values:

SQL

INSERT INTO Customers (Name, Phone)
VALUES ('{{ data.name }}', '{{ data.phoneNumber }}');

Save the whole submission as JSON

Store every field, including data grids, as one JSON value that you can query later. This requires SQL Server 2016 or later.

1. Create the table and stored procedure

SQL

CREATE TABLE GF10DATA (
  IID BIGINT IDENTITY(1,1),
  DATA NVARCHAR(MAX),
  FORMNAME VARCHAR(255)
);
GO
CREATE PROCEDURE dbo.GF10PUSH @DATA NVARCHAR(MAX), @FORMNAME VARCHAR(255)
AS
INSERT INTO GF10DATA (DATA, FORMNAME) VALUES (@DATA, @FORMNAME);
GO

2. Collect the form data into one field

Add a hidden Text Area with the API key JSON.
In the Submit button's Button Custom Logic, add:
JavaScript
```
data.JSON = null;
data.JSON = JSON.stringify(data);
```

3. Send it with a SQL Query action

SQL

EXEC dbo.GF10PUSH @DATA = '{{ data.JSON }}', @FORMNAME = 'Customer Intake';

4. Read the data

Use SQL Server's JSON functions. Each form field is available by its API key:

SQL

SELECT IID, FORMNAME,
  JSON_VALUE(DATA, '$.firstName') AS FirstName,
  JSON_VALUE(DATA, '$.lastName') AS LastName,
  ContactDetail.Phone
FROM GF10DATA
CROSS APPLY OPENJSON(DATA, '$.ContactDetails')
  WITH (Phone NVARCHAR(50) '$.Phone') AS ContactDetail;

In this example, ContactDetails is a data grid containing a Phone field.