I need some design suggestions on my requirement below .
I have a excel which has around 1000 rows and has 5 columns . All four columns are some labels and the last column has VALUE(Sample below)
My requirement is to upload this excel file and save into Appian DB but before i save this data i want to perform some validations on VALUE column let say :
1. first 5 rows should fall into tolerance band of 100-200
2. Next five rows should fall into tolerance band of 10-35
3. first 5 rows should sum upto 100
If values doesn't pass above validations then i need to tell user to re upload the excel with correct values .
Thanks in advance
Discussion posts and replies are publicly visible
This you can achieve by providing the upload file interface component.
Reading the file in the process
Create an expression to select the number of rows
Keep the timer for delay if you want to delay the row writing.
Let me know the definition of tolerance band with respect to your explanation. Because its more relevant to resistors in electronics.
I would suggest loading the data into a 'Staging' table as it is in the Spreadsheet, and then using a Stored Procedure (or set of SPs) to conduct the validations. If the data passes validation you can then move load the data to the actual data table. If there are any validation errors you can then report them to the User via a SAIL Interface.
I'm not personally an expert in Stored Procedures, so I can't speak to whether the method suggested by Stewart above would be easier or not. For me, though, it would be harder than simply handling the logic within Appian.
Here's a quick breakdown of my suggested design/process flow:
A slightly simplified version of the process flow I'm thinking of is pictured here:
So this works too, but if the size (number of rows and.or number of validations that need to be applied gets very large then you need a blindingly fast way to achieve this, and that's when Stored Procs come into play, so I guess I was pre-empting these issues by suggesting the solution I did.
That makes sense. I guess it comes down to the data size/density/etc in the poster's "1000 row" excel sheet. I assume it'd need some testing out first.
So just to satisfy my own curiosity - I created a test excel sheet with just over 3600 rows of data in 3 total columns, and all in one test expression I've
The whole shebang executes on average in less than 250 ms, which should be acceptable in all but the most extreme circumstances. Obviously it bears further testing with a sheet with "realistic" data populated in 5 columns x 1000 rows, but, at the very least, it bodes well for the use case / solution method.
© 2023 Appian. All rights reserved.