From Inistate to SQL Database

Things to Take Note

When performing integration between Inistate and a SQL database, ensure the following steps and validations are followed:

1. Mapping File Preparation

• Prepare a mapping file that defines how each field from Inistate corresponds to the SQL database columns.

• Ensure that all required fields are included in the mapping file.

• Double-check that optional fields are also handled properly to avoid missing data.

2. Data Type Validation

• Verify that each mapped field matches the data type defined in the SQL database.

• Examples:

  • Text → VARCHAR, NVARCHAR

  • Integer, Number → INT, DECIMAL

  • Date Time, Date → DATETIME, DATE

• Ensure that the data from Inistate does not conflict with SQL type restrictions (e.g., trying to insert a string into a numeric field).

3. Value Length Check

• Validate the maximum length allowed by the SQL database for each column.

• Example:

  • VARCHAR(50) → Ensure the incoming string is 50 characters or less.

• Truncate or handle values exceeding the defined length to avoid insertion errors.

4. Null & Default Handling

• Check whether the SQL column allows NULL values.

• If not, ensure default values are provided during integration.

5. Data Integrity & Constraints

• Validate data against primary keys, foreign keys, and unique constraints.

• Ensure that reference data exists before inserting dependent records.

Sample Code

Logic Script

To find {fieldName}, you are required to have access to Alpha or use Network in Developer Tools [F12].

resultList = []

# search
entries = Module("{YOUR-MODULE-NAME}").findAll({'$and':
    [
        { 
            'Audit.UpdatedDate': { '$gt': Inputs['lastSync'] }
        },
        {
            'Audit.UpdatedBy': { '$ne': 'External' }
        }
    ]
}).OrderBy(lambda x: x.UpdatedDate).Take({Count})

debug("entries", entries)

# format SQL value
def sqlString(val):
    if val is not None:
        if isinstance(val, basestring) == False:
            val = str(val)

        return val.replace("'", "''")

    return val
    
def sqlBoolean(val):
    if val is True:
        return 1
    
    return 0
    
# prepare data
def prepareRow(x, items):
    row = JObject()
    row['NO'] = items.IndexOf(x) + 1
    row['Type'] = x['Type']
    row['Description'] = x['Description']
    row['Amount'] = x['Amount']
    row['IsActive'] = 1 if x['Is Active'] == True else 0
    return row

def preparePayload(data):
    payloadData = JObject()
    payloadData['FirstName'] = sqlString(data['First Name'])
    payloadData['LastName'] = sqlString(data['Last Name'])
    payloadData['Email'] = sqlString(data['Email'])
    payloadData['PhoneNumber'] = data['Phone Number']
    payloadData['Department'] = sqlString(data['Department'])
    payloadData['Position'] = sqlString(data['Position'])
    payloadData['DateOfJoining'] = data['Date Of Joining']
    payloadData['DateOfBirth'] = data['DOB']
    payloadData['Salary'] = data['Salary']
    payloadData['IsActive'] = sqlBoolean(data['Is Active'])
    payloadData['DocumentID'] = data.DocumentId
    payloadData['Benefits'] = JArray.FromObject(data['Benefits'].Select(lambda x: prepareRow(x, data['Benefits'])))
    payloadData['LastModified'] = data.UpdatedDate
    return payloadData

for entry in entries:
    payloadData = preparePayload(entry)
    resultList.append(payloadData)

# results
debug('resultList', resultList)
outputs['results'] = JArray.FromObject(resultList)

If the information type is Module or User, use data.displayField({displayName}). For Example:

payloadData['Name'] = sqlString(data.displayField('Name'));

To avoid errors caused by deleted records during synchronization.

The LastModified must be included and positioned as the last field in the payload.

Sample SQL Template

SQL Template

{% for staff in payload %}
IF EXISTS (SELECT 1 FROM Staff WHERE DocumentID = '{{ staff.DocumentID }}')
BEGIN
  UPDATE Staff
  SET 
    FirstName = N'{{ staff.FirstName }}',
    LastName = N'{{ staff.LastName }}',
    Email = N'{{ staff.Email }}',
    PhoneNumber = N'{{ staff.PhoneNumber }}',
    Department = N'{{ staff.Department }}',
    Position = N'{{ staff.Position }}',
    DateOfJoining = {% if staff.DateOfJoining and staff.DateOfJoining != '' %}N'{{ staff.DateOfJoining }}'{% else %}NULL{% endif %},
    DateOfBirth = {% if staff.DateOfBirth and staff.DateOfBirth != '' %}N'{{ staff.DateOfBirth }}'{% else %}NULL{% endif %},
    Salary = {{ staff.Salary }},
    IsActive = {{ staff.IsActive }}
  WHERE DocumentID = '{{ staff.DocumentID }}';
END
ELSE
BEGIN
  INSERT INTO Staff (
    DocumentID, FirstName, LastName, Email, PhoneNumber, 
    Department, Position, DateOfJoining, DateOfBirth, Salary, IsActive
  )
  VALUES (
    '{{ staff.DocumentID }}',
    N'{{ staff.FirstName }}',
    N'{{ staff.LastName }}',
    N'{{ staff.Email }}',
    N'{{ staff.PhoneNumber }}',
    N'{{ staff.Department }}',
    N'{{ staff.Position }}',
    {% if staff.DateOfJoining and staff.DateOfJoining != '' %}N'{{ staff.DateOfJoining }}'{% else %}NULL{% endif %},
    {% if staff.DateOfBirth and staff.DateOfBirth != '' %}N'{{ staff.DateOfBirth }}'{% else %}NULL{% endif %},
    {{ staff.Salary }},
    {{ staff.IsActive }}
  );
END

{% if staff.Benefits and staff.Benefits.size > 0 %}
  DELETE FROM Benefits
  WHERE StaffID = '{{ staff.DocumentID }}';
    AND NO NOT IN (
      {% for benefit in staff.Benefits %}
        {{ benefit.NO }}{% if forloop.last == false %}, {% endif %}
      {% endfor %}
    );

  {% for benefit in staff.Benefits %}
    IF EXISTS (
      SELECT 1 
      FROM Benefits 
      WHERE StaffID = '{{ staff.DocumentID }}'
        AND NO = {{ benefit.NO }}
    )
    BEGIN
      UPDATE Benefits
      SET 
        Type = N'{{ benefit.Type }}',
        Description = N'{{ benefit.Description }}',
        Amount = {{ benefit.Amount }},
        IsActive = {{ benefit.IsActive }}
      WHERE StaffID = '{{ staff.DocumentID }}'
        AND NO = {{ benefit.NO }};
    END
    ELSE
    BEGIN
      INSERT INTO Benefits (StaffID, NO, Type, Description, Amount, IsActive)
      VALUES (
        '{{ staff.DocumentID }}',
        {{ benefit.NO }},
        N'{{ benefit.Type }}',
        N'{{ benefit.Description }}',
        {{ benefit.Amount }},
        {{ benefit.IsActive }}
      );
    END
  {% endfor %}
{% else %}
  DELETE FROM Benefits
  WHERE StaffID = '{{ staff.DocumentID }}';
{% endif %}

{% endfor %}

It needs to be converted into a string format before being added to jobs.json.

Extras

Jobs json

The jobs.json file defines the set of connector tasks to run during integration.

Each job entry specifies:

• name: Logical name of the task.

• enabled: true/false to turn this job on or off.

• connectionString: A valid ADO-style string.

• debug: Enable detailed logging.

• sqlTemplate: Your SQL template script to select changed records.

• hook: The callback URL to receive each batch of synced rows.

• lastSync: Timestamp of the last run.

• interval: Polling interval in minutes.

jobs.json

{
  "SQLSync": [
    {
      "name": "{NAME}",
      "enabled": true,
      "connectionString": "Data Source={SERVER};Initial Catalog={DATABASE};User Id={DB_USERNAME};Password={DB_PASSWORD};",
      "to": "sql",
      "debug": false,
      "sqlTemplate": "{SQL TEMPLATE}",
      "hook": "https://api.inistate.com/api/automationHook/hook/{MODULE-ID}-{LOGIC-NAME}",
      "lastSync": "{LAST_SYNC_DATE_TIME}",
      "interval": 5
    }
  ]
}

Extras