External attributes in Orbit message personalisation

You need to configure external attributes in your database before you can use them in Orbit campaigns.

Create the external data table

To create your external data table:

  1. Create a new database or use an existing one (for example, PS_ExternalData)

  2. Create a table with at least two fields: one for codes and one for descriptions

  3. Populate the table with your code and description values

Example SQL:

Copy 
CREATE TABLE [dbo].[AppealCodes](
    [AppealCode] varchar(50) NULL,
    [AppealDescription] varchar(255) NULL
) ON [PRIMARY]
GO

INSERT [dbo].[AppealCodes] ([AppealCode], [AppealDescription]) 
VALUES (N'EMRG01', N'Emergency Relief Fund')
GO

INSERT [dbo].[AppealCodes] ([AppealCode], [AppealDescription]) 
VALUES (N'EDUC01', N'Education Programme')
GO

Register the external attribute

To register your external attribute in the system:

  1. Access your PeopleStage database.

  2. Locate the ExternalAttributeDefinition table.

  3. Insert a new row with the following information:

    • Name: A descriptive name for the external attribute

    • ExternalDataType: Set to Database

    • SqlProvider: Set to SqlServer

    • SqlConnectionString: The connection string to your external database

    • SqlTimeOut: Timeout value in seconds (for example, 30)

    • SqlCommand: A SQL query that returns Code and Description fields

    • Folder (optional): Organise attributes by specifying a folder name

Example SQL command:

Copy 
SELECT AppealCode as Code, AppealDescription as Description FROM AppealCodes

Troubleshooting

External attribute doesn't appear in Orbit

Cause: The external attribute may not be properly registered or the database connection is incorrect.

Solution: Verify that you've inserted the attribute definition into the ExternalAttributeDefinition table with all required fields. Test your SQL connection string separately to ensure the database is accessible.

Personalisation displays blank values

Cause: The SQL command isn't returning Code and Description columns, or there's no matching data.

Solution: Run your SQL command directly against the database to verify it returns results with columns named exactly Code and Description. Check that your external table contains data for the codes being used in your campaign.

Database timeout errors

Cause: The SqlTimeOut value is too low for complex queries or slow database connections.

Solution: Increase the SqlTimeOut value in the ExternalAttributeDefinition table. Start with 60 seconds and adjust based on your query performance.