Set up a table trigger when using SQL tables ("LinkedObject") for transactional data import

By default, the field length for the Address field is 100 characters in Microsoft Dynamics 365 Business Central for the Vendor table, while the field length in edoc D365 BC invoice cockpit is 50 characters.

If you export transactional data from edoc invoice and the vendor addresses are longer than 50 characters, a problem occurs because the field lengths for the vendor address do not match between edoc invoice and edoc D365 invoice cockpit.

To work around the issue that arises when using SQL tables of the LinkedObject type, you must generate a table trigger in Microsoft SQL Server Management Studio.

If you have not configured any web services for data import in the edoc D365 BC invoice cockpit, you must set up a table trigger to truncate the field contents of vendor addresses to 50 characters.

Here's how

1. Open Microsoft SQL Server Management Studio on the server running Microsoft Dynamics 365 Business Central.

2. Connect to the server instance where the Business Central database is stored.

3. Select the Business Central database and run the following SQL script there once:

   SQL

   SET QUOTED_IDENTIFIER ON;
   SET ANSI_NULLS ON;
   GO
   
   CREATE TRIGGER [dbo].[trg_edoc_headvalue_trunc_vendor_street]
   ON [dbo].[edoc_transfer_headvalue$0f000123-ab88-44f0-9de8-73b0de545b98]
   AFTER INSERT
   AS
   
   BEGIN
       SET NOCOUNT ON;
       /*
         Shorten vendor_address_street to 50 characters
         for the rows that are being inserted.
       */
   
       UPDATE tgt
           SET tgt.[vendor_address_street] =
               CASE
                   WHEN i.[vendor_address_street] IS NULL THEN NULL
                   ELSE LEFT(i.[vendor_address_street], 50)
               END
   
       FROM [dbo].[edoc_transfer_headvalue$0f000123-ab88-44f0-9de8-73b0de545b98] AS tgt
       INNER JOIN inserted AS i
           ON tgt.event_id = i.event_id;
   END;
   
   GO
   

The table trigger was created successfully.