We have many customers interested in using Scribe Online IS to create Orders in their accounting application. This blog will describe how to do so with SQL Server as a backend from Opportunities in their CRM application.
To start, I need to know the next appropriate Order Number value when I am creating the Order in SQL, so will I have a Stored Procedure that already exists in SQL get me the next Order Number value. Once I have that value, I will be able to create my Sales Order correctly.Although you cannot call stored procedures directly in the Scribe Online process definition editor, you can trigger them to execute on your database. Think of triggers like Workflows in the CRM application world. They are created to automatically execute code during events on a particular table. Read more about triggers. Here are additional resources for creating a trigger in specific databases:
Using a database trigger, I can have a stored procedure called to run a SQL query for Scribe to use with these three steps:
- Create a staging table with a trigger that calls your stored procedure.
- Modify or duplicate your stored procedure to write the results to your staging table.
- Create a Scribe Online map to trigger the stored procedure and then Lookup the results.
Prerequisites:
- Scribe Online account with an IS trial or subscription.
- Scribe Online on-premise Agent installed and a Connection made to your database.
- Basic knowledge of triggers and stored procedures (or aptitude to learn about SQL scripts).
Step 1 – Create the staging table and Trigger
I will create a new staging table (named dbo.Trigger) with an attached database trigger (named dbo. GetNextTrigger). Here’s what my Staging table and Trigger looks like:
CREATE TABLE
[dbo].[Trigger](
[Triggerbit] [bit]
NULL,
[DateTriggered]
[datetime] NULL,
[Guid]
[uniqueidentifier] NULL,
[Result] [int] NULL
) ON [PRIMARY]
GO
CREATE TRIGGER [dbo].[GetNextTrigger]
ON [dbo].[Trigger]
AFTER INSERT
AS
BEGIN
EXEC [dbo].[GetNextNum]
END
GO
You will want to change the stored procedure (highlighted) to the name of yours. You will also want change the Result field(s) to be the correct data-type for the result of your stored procedure. The stored procedure must be located on the same database as the table/trigger.
Step 2 –Modify/Duplicate your Stored Procedure
I want to find the next order number using a SQL query. Once you’ve built a query to find the next number, or duplicated an existing stored procedure that does this, you will want to add a query to update those results to your staging table. This is what mine looks like:
ALTER PROCEDURE [dbo].[GetNextNum]
AS
BEGIN
SET NOCOUNT
ON;
DECLARE @Max INT
SET @MAX = (SELECT COUNT(*) + 1 FROM [dbo].[salesorder]
WITH(NOLOCK))
UPDATE [dbo].[Trigger]
SET [Result] = @Max
WHERE ([Result] IS NULL
OR [Result] = '')
END
Make sure that the name of your stored procedure matches the name you are using in your trigger if you needed to create or duplicate the stored procedure.
Step 3 – Creating a map to get the value returned by your Stored Procedure
Once you’ve created your new staging table, you’ll want to login to Scribe Online and create a connection to your database, or refresh the metadata on that database.
The simplest configuration should insert into the dbo.Trigger table with a unique value (1st operation). Following the trigger, lookup the result field(s) of your previous step (2nd operation). Then you can use the results of your lookup in any subsequent step (3rd operation). Here is a more detailed explanation of how I configured my map:
|
Figure 1: Step 1 – Create Trigger row operation Target mappings.
Using the result of my NEWGUID() that I am inserting on the 1st operation (in Fig. 1 above), I will be able to search for the result of my Stored Procedure on the 2nd operation Lookup Criteria (on Fig. 2 below). The GUID will ensure that I always limit me Lookup operation to the record I just created.
Figure 2: Step 2 – Lookup Stored Proc Result operation
Lookup Criteria.
On the 2nd operation, Lookup Stored Proc Result, click the Field List tab and check the Result(s) field(s) so that you can use it on subsequent operations.
Figure 3: The Result is available for target mapping.
On your 3rd (or any subsequent) operations, you can use the Result field from your Stored Procedure (Fig. 3) to map to your target (Fig. 4). For each row you process in Scribe Online, the stored procedure will run to ensure each order has the correct order number.
Figure 4: Map the Result field from the 2nd operation’s Lookup.
Even though I have three operations above for each source row, Scribe will only log them as 1 row processed:
Notes and considerations
- My Stored Procedure runs very quickly, so I am
able to use the result of it on the next step. If stored procedure runs slower,
the result of it may not be committed to the database in time for Scribe to find
it with your Lookup operation. You may be able to run other operations or retry
your lookup with later operations or in a subsequent map.
- Consider indexes on your database to optimize the performance of your Stored Procedure where it makes sense.
- If when running your Scribe Online IS map, you receive row errors on each create operation to your staging table, there may be an issue with your trigger, stored procedure or both. Ensure that you can create rows in the staging table manually first.
