Microsoft SQL Server Integration - Import Financial Plans

This article details the configuration steps of the Microsoft SQL Server to OnePlan integration to import financial plan data into OnePlan. Financial plan data is the time phased financial data for specific plans in OnePlan.

Source Data Requirements

Setup Database

• This is a one-way integration: data is imported into OnePlan but not written back to SQL.
• The required data must exist in tables or views within a specific schema. 
• A sample SQL script is attached to this article (OnePlan_FinancialPlans.sql) to demonstrate the required structure. 
• You can add fields to the script's table/view as needed to reflect your source data. 

Identifying Resources

• Non-generic resources: Identified by email address. 
• Generic resources: Identified by Display Name (e.g., "Developer").

Cost Values

Positive values are accepted up to 2 decimal places. Should there be more than 2 decimal places the values will round up visually in the Resource Planner.

Custom Fields

For the integration to write to a custom field in the financial planner a custom field must first exist in OnePlan in the financial planner.

The source SQL data table must have a field that matches the internal name of the financial planner custom field in OnePlan.

Custom fields are picked up automatically, they don't need to be mapped.

OnePlan Configuration

Set Plan External ID

Enable Custom Cost Categories

• In the OnePlan Configuration page, navigate to Financial Plan, and expand the Advanced tab.
• Check the box labelled "Allow Plan Level Cost Categories."

Identifying Plans with External IDs

Each plan must be uniquely identified by a Plan External Id. 

• The Id allows OnePlan to match existing plans.
• The field must be set up and mapped manually in OnePlan (it is not out-of-the-box). 

Step 1: Create the Custom Field

1. In OnePlan, go to Settings (gear icon) > Fields.
2. Select Plan Fields, then click New Field.
3. Name is something like "Plan External Id".
4. Set the field type to Text. 

Step 2: Assign as External ID Field

1. In OnePlan, go to Settings (gear icon). 
2. Go to Financial Plan > Import.
3. Use the External Id Field dropdown to select your new custom field.
4. The selection saves automatically. 

Note: This field is now used by OnePlan to identify plans for syncing. 

Step 3: Add field to Plan Details (Optional)

Add the custom field to the Plan Details form so it can be located easily and updated manually if needed. 

1. In OnePlan Configuration page, navigate to Plan Details.
2. Select a Plan Type you want this field added to.
3. In the far-right column labeled Fields, find your custom field.
4. Drag and drop the field into the desired section of the Plan Details layout. 
5. Click Save next to the Plan Type dropdown. 

OneConnect Configuration

Field Mappings

BulkResrcImport - ResourcePlans

While any number of fields can be mapped for the import, at minimum, the fields listed below must be mapped for the data to come into OnePlan: 

Example for this mapping:

Strategies

SQLTableToFinancialPlanner

This strategy imports records from SQL database as time phased financial plan data into OnePlan.

By default, this strategy has been set to Enabled, however the Schedule Type has been set to None. This means that the strategy will not trigger on a set schedule and will only run when manually triggered through OneConnect.

This strategy will load the records from the "dbo.CostTable" table or view, and process each record, and bring over any values mapped to OnePlan. 

Should the source data have a Cost External Id that already exists in OnePlan, and the Cost Name does not match any existing cost category name, the integration will create a new row with the Cost Name, however all data will be allocated to the previously existing cost category that has the Cost External Id

• OnePlan_FinancialPlans.sql446 Bytes