I'm creating a view where the user will log product testing. The backend of it is a mssql database with the test record table and a child table that stores the test values that will be used for analysis.
This is currently done manually on a spreadsheet and there are around 80-100 datapoints, depending on the test.
What's the best practice or maybe a good way to display the blanks to the user and save them to the table? Should I update every time each datapoint is entered or store them and update after all are entered?
I would do all at once most likely with a submit button. Say they enter something incorrectly - you don't want to be saving that. Them clicking a submit button on a form is a pattern most people are used to from web forms and it's at that moment I would save all datapoints.
I would do this in a transaction especially if there is more than 1 INSERT/UPDATE query being executed to ensure you're database doesn't get malformed where you have these half forms where maybe the first part ran correctly but then ran into an error before the second INSERT query. Make it all or nothing with a transaction.
I typically highlight components yellow for things that need to be filled in or haven't yet been selected or inputted, and turn them white after a possible option has been selected. This corresponds ultimately to the enable property of the submit button. If the submit button is not enabled, I should be able to see easily what was not filled in because it is highlighted.
These are just some generic guidelines that I hope answer at least part of your question. Do use transactions and since you're using perspective, come up with a style that indicates "incomplete" for a form field, and a style that indicates "completed" for a form field (for me, yellow and then white) and stay consistent with it through your app.
For your scenario, considering the volume of data points, it's generally more efficient to collect all the data from the user interface first and then perform a bulk update to the database. This minimizes the number of connections and transactions your server has to handle at once, which can be crucial for performance, especially with large datasets.
In terms of user interface, consider using forms with grouped fields or a multi-step process to guide the user through data entry without overwhelming them. You can look at examples of data entry software like Microsoft Access or online form builders that provide templates and functionalities suited for handling complex data sets efficiently.
If you're using a database that supports stored procedures I would recommend using those and binding saved queries to them. They perform faster and it allows you to wrap multiple transactions with rollback and validation options if those make sense.
I'd recommend validating your data points in the python before calling the sp though.
I would make a template for a single data point, use a repeater to display however many you need, and then yes use a submit button to run a stored query and store all of the data at once.