Skip to main content
rn5947
rn5947
April 5, 2024
Solved

Syncing Third Party Updates to a series of Database Tables

  • April 5, 2024
  • 9 replies
  • 0 views

We have a series of Records configured in one large relationship structure (50 + record types).  A third party process retrieves data from a SOAP webservice, extracts pertinent data and using a series of stored procedures inserts that into the database.

Is there a way to capture those new entries and sync them into my records without having to examine 50 tables and perform 50 Sync Record operations?

    Best answer by stefanhelzle0001

    You already run a stored procedure that could keep track of all the updates. Why not write these to a separate table. Then a scheduled process in Appian could manage these update by syncing the modified records.

    9 replies

    shubhama926776
    shubhama926776
    Brainy
    April 5, 2024

    You can automate the process by setting up periodic database queries to retrieve new entries, mapping the data to your record types, and then using Appian's record sync functionality to seamlessly integrate the new entries without manual examination or sync operations for each record type.

    Refer for sync records 

    https://docs.appian.com/suite/help/24.1/Sync_Records_Smart_Service.html#:~:text=The%20Sync%20Records%20smart%20service,first%20configure%20a%20sync%20expression.

    शुभम्
      rn5947
      rn5947Author
      April 5, 2024

      My Record Types are based on those same tables, so I see my worst case scenario as:

      1) Schedule a process that periodically uses a!queryEntity against the tables (requires CDT configuration?). Say I get these results:

      • Table 1: PK Values {100,101,102}
      • Table 2: PK Values {1000,1001, 1002}
      • Table 3: PK Values {2000,2001,2002}
      • Table N: PK Values {10000,10001,10002}

      2) Trigger a series of Synchronize Record Smart Services where the configuration looks like:

      • recordType!Table 1:{100,101,102},
      • recordType!Table 2: {1000,1001, 1002},
      • recordType!Table 3:{2000,2001,2002},
      • recordType!Table N:{10000,10001,10002},

      Is there a better way?

      shubhama926776
      shubhama926776
      Brainy
      April 5, 2024

      For me it looks good.

      I implemented similar solutions few days back.

      शुभम्
      stefanhelzle0001
      stefanhelzle0001Answer
      Brainy
      April 5, 2024

      You already run a stored procedure that could keep track of all the updates. Why not write these to a separate table. Then a scheduled process in Appian could manage these update by syncing the modified records.

        rn5947
        rn5947Author
        April 5, 2024

        I see two streams of relatively tedious work:
        1) Identifying the changes.

        2) Synching the changes.  

        I think there are ways to short-path the first one, either through a tracking table updated by the stored procedures, or a view that is aware of Appian's last poll and shows the keys that have been added since last poll.

        Part two is where I think Appian could provide a lift.

        From our perspective, there are only a peak of 120 updates a month,  but the data model is terribly distributed.  Even having something that synchs the records based on the top-level parent, and transcends the relationships to synch children or reference tables.

        Terms & ConditionsAccessibility statement