Hi, my use case is I want to provide an excel template for users to download and fill out. Some of the information the user is filling out is dependent on data in a ref table that updates occasionally, so before the user downloads the document, I run Export Data Store Entity to Excel smart service in a process to update the document, so when the user downloads the document, it'll have the most updated ref data.
Here's the issue I'm running into - the excel template has two sheets:
- Sheet 1 is where the user fills out info. This sheet has data validation rules to make it more user friendly, most notably vlookup functions and data validation dropdowns that pull from Sheet 2
- Sheet 2 is where the ref data lives, and what the Export Data Store Entity smart service updates
However, after the smart services runs, the excel template that gets generate has no data validations - no dropdowns or excel functions. I'm imagining even though I'm only having the smart service update Sheet 2, the entire excel document gets impacted and erases all data validations. Below is the code that is being run in the smart service (note: I'm using the smart service's process model version, but for sake of clarity here's the SAIL code version):
a!exportDataStoreEntityToExcel( documentToUpdate: cons!DOC_CREATE_EXCEL_TEMPLATE, entity: cons!REF_TABLE, sheetNumber: 2 )
Is this expected behavior or is there another workaround I can consider?
Thanks in advance
Discussion posts and replies are publicly visible
We had similar requirements and the way I implemented is
1. Have an excel template with all validations.
2. The occasional data change in ref table was driven by admin actions so whenever data was modified by admin the same process would update the excel template’s ref data sheet in the template3. Users always download the latest excel template’s ref data as step 2 updates the version of old template document instead of creating a new one.
Even with data changes the validations were untouched. Ensure that the smart service in process model updates the exact cell column/row which is used in excel data validation column. Also pass the old template as existing template to the smart service so it doesnt overwrite anything on sheet 1. There can be more smaller delicate configurations that might be impacting the outcome but this is a perfectly solvable problem. Let me know if you need more help on this