Sync Records Smart Service - full sync

Hi

How do you use Sync Records Smart Service to fully sync a record type that was just modified by a back end stored procedure

My case is new data comes in daily in CSV files - which gets uploaded into a set of temporary tables

After this a stored procedure inserts them into the main tables that are connected with the record types.

As of now we are syncing these record type manually from the web page

I though this smart service would have an option to do a full sync, but it needs the IDs of the record types to sync - well these are unknown since they were just inserted by the stored procedure in DB so not available yet.

Do I need to come up with another data base query to retrieve the new ID - form the database?

  Discussion posts and replies are publicly visible

Parents
  • You must be having a timestamp when these CSV files are introduced in the system, considering this is a repetitive activity. 
    If yes, you can schedule the sync of the records 2-3 minutes after the SP has been completed. 
    Or, if you are using a process model to write this data, then as you said, you can use the sync record smart service. Here is the link on how to configure it - https://docs.appian.com/suite/help/22.2/Sync_Records_Smart_Service.html

    It requires you to provide primary keys of the record. You can run this smart service for the data you just inserted and if it is more than 1000 rows, then run this service in batching. 

  • yes the records need to be synced all the time...

    so your suggestion is to add a select node from db with a query like "select top 1000 Id from table_x where XYZ" then feed that into sync node - then do a loop 

    I am wondering what the design constraint was against a true full sync by record type.

  • 0
    Appian Employee
    in reply to octavian

    The sync records smart service was designed to keep your data up to date between full syncs, as data is updated on your external data source. You can use it in conjunction with the execute stored procedure smart service in your process model with a query after the stored procedure to retrieve the relevant primary keys to sync. You can incorporate a loop as suggested if your list is greater than 1000 records. 

  • yes that is what we have now - flat files are loaded into temp tables then a stored procedure does a glorified update + insert into main tables.

    Note: we are getting the data from day 0 all the time - so this is an entire set of data either changed, new or the  same. So currently the stored procedure does update on matching rows and insert on new rows

    New rows are going to be easy to add a "LastUpdated" timestamp. However, for existing rows, do you recommend to modify the stored procedure to record a timestamp for only the rows that have been actually updated?

    Doing this would lead to a lot of rows being eliminated from the Sync process that will follow. 

    Another question is, if I have more record types related to each other from the same DB tables, is there any internal sync triggering when one record type is synced to force a related record type to sync too?

    Or I just have to make a list of all record types that are displayed in interfaces and sync them all one by one?

Reply
  • yes that is what we have now - flat files are loaded into temp tables then a stored procedure does a glorified update + insert into main tables.

    Note: we are getting the data from day 0 all the time - so this is an entire set of data either changed, new or the  same. So currently the stored procedure does update on matching rows and insert on new rows

    New rows are going to be easy to add a "LastUpdated" timestamp. However, for existing rows, do you recommend to modify the stored procedure to record a timestamp for only the rows that have been actually updated?

    Doing this would lead to a lot of rows being eliminated from the Sync process that will follow. 

    Another question is, if I have more record types related to each other from the same DB tables, is there any internal sync triggering when one record type is synced to force a related record type to sync too?

    Or I just have to make a list of all record types that are displayed in interfaces and sync them all one by one?

Children