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
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.htmlIt 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.
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?
This seems to be super complicated. I would like to repeat my question: Does this need to be a SYNCHED record?
Yes - the appian application is a consumer of this data and does things based on updates that are flowing in from these files. How else would you propose? (maybe something we don't see)
I echo what Harshit said, if you're getting a daily dump of the CSV files, could you just rely on the regular scheduled sync rather than trying to utilize this smart service? If your process to upload the CSV files runs at 4:00AM every day, just schedule your sync for 4:30 or something and you'll get the latest data pretty soon afterwards.
There will be a little temporary difference between the stored procedure execution and the sync, but this seems a lot easier and should result in a similar outcome.
If this timeline is not acceptable, can you provide more context about your use case?
In my case, stored procedure execution will happens at any point of time, my stored procedure may delete existing rows and insert new row in DB table. So, is there any way to full sync record in real-time right after execution of stored procedure?
No way to trigger a full sync except through the interface.
If you're updating rows through the stored procedure, would you know which rows were impacted? If so, you could the sync record smart service following the stored procedure.