Hello,
We are building a site which will be used to gather request and I'm trying to find if there is a possibility to make bulk upload of these request using excel. The form which need to be submitted contain single rows and also arrays of data. We want all of these bulk uploaded request to stay in "draft" mode and not being push to the backend databases, because that can lead to incorrect information to the database since we have some validations which need to pass. We need to give the client the agility to edit these information when they are loaded as bulk. What are the capabilities of Appian to load bulk data, what are the limitations?
Thank you!
Discussion posts and replies are publicly visible
Depending on how standardized the data is in the Excel sheets, you can easily have Appian try to read the data into in-process info (and maybe pass it to a "temp" database table prior to approval / sanitization / etc). The rest really depends on variables you haven't mentioned - like how big "bulk" is, how many different excel file(s) if more than 1, who will be uploading these, who will be approving the data and to what extent they would need to make corrections/etc before it's finalized. The best thing to do would be to build a demo process and try, IMHO.
The problem is that each request have 10+ different tables associated with it. Also contains for example and editable grids which customer need to enter multiple lines for each request. How this can be associated with the excel file and make sure they are in the same request. We have the freedom of building the excel sheet as we want as long it will work in Appian and we can leave them in draft so we can use validations inside Appian before submitting them to the databases.
It should be possible to build something like this of course -- i'm afraid to say there's no "magic wand" solution that I know of, meaning you'd need to build much of it manually (easier or harder depending on how strictly you can enforce standardization in your Excel files). As I mentioned before sometimes the best approach is to just cut to the chase and implement a "feasibility check" version yourself.
The most preferred way should be as already suggested to use temporary or staging DB tables to load data from excel. Keep two additional columns in each staging table named as VALIDATION_MESSAGE and IS_VALIDATE. Update validation message if any from sanity, data, datatype or any other validation. Update IS_VALIDATE true if no validation message else false. This way you can export only those rows which are not valid and display to the user so that they can correct and upload another set of excels with right data.
We want to let users view all the uploaded requests since they might have more information which they need to fill out. So the bulk upload will not have all of the fields it will have the required ones but not all. Thats why we want request to stay in draft in the right tables so after they finish them to click submit on each one of them and push them to the database.
You can achieve it using the above suggested approach.