davenh99/inventory

import records from excel (just products for now but build it to be useable for any records) #13

New issue

Closed

opened 2026-03-29 08:15:43 +11:00 by davenh99 · 0 comments

davenh99 commented

2026-03-29 08:15:43 +11:00

(Migrated from codeberg.org)

plan:

load excel spreadsheet using excelise or whatever.
scan first row, and validate each field (and relation model)
then, scan each row. if relation, try find by id, else try find by name, else add to a batch to create by name (if other fields required, we mention this [i.e., could not find relation field product.uom by id or name 'unit(s)', cannot create with name 'unit(s)' as missing required field 'x' (either check for misspelling or add this column)]). if not relation, try type convert each value into the field's type.
if errors above, display errors by row. (and which rows are valid)
otherwise, continue to validation. need to create relation records if applicable, and use their ids to validate each row. (can i roll back the created records?, yes do it all in a transaction, deleting them at the end).
if all rows validated, display an import button and message that it all looks good.

new plan:

checkbox before loading/validating for 'create missing records by name', with description
load excel, and scan first row getting fields and validating they exist, and type of each field.
if we have relations, determine creation priority. this is tricky -> how about, to keep simple, we create from top to bottom in order. if any not existing by name or id, create if all required fields present. nothing dynamic atm.
run a transaction with everything, and at the end discard. this is necessary rather than validation, as some child records require parent records to be created temporarily, for the child records validation.
so -> upload -> check/uncheck checkbox for 'create missing records (otherwise attempt to update, and error on missing by name/id)' -> validate (will have rich preview of all records to be created, nice little badges for created/updated, row level errs etc) -> upload.

final touch ups:

change payload name from record to records, change all errors to return err.Error() where applicable.
change failed to failedcount, or failedinds or something. chaneg parent to parentcollection.
change filterColIndexes to something more descriptive
change the final return message to success instead of not implemented.
need to populate recordsToCreate and recordsToUpdate
what happens when we put in only the relation, not relation.id? (should be identical behaviour, maybe tack on the id)
what about select with multi select? need to split by comma probably. -> actually, remove all of this logic. ocketbase alreayd has preparevalue, findsetter, etc which normalise all the fields. better handles bools and everything. so just setvalue simply. for multi select, needs to be json serialisable so put [] around the values, like: ["opt 1", "opt 2"].
change 'ImportRecordValidationResult' to have 'success' bool, and message instead of []Errors.
this behaviour is wrong: if relFieldToUpdate.IsMulti {
record.Set(fmt.Sprintf("%s+", fieldName), relFieldToUpdate.RecordIds)
},, instead just set it normally to the ids... also don't need diff behaviour if is multiple, pass the list of ids it's already handled in the field normalise.

test cases:

test with basic, no related, few records. validate, and import. (for rest just test import)
test basic with invalid data (i.e. strign for number field)
test with one relation field, create by name. another with finding by id.
test with nested relation fields, with multi relations. another with complex relations, double multi, with varying number of relations in each level to test the correct separation.
test invalid excel with double nested relation with no data for find or create the in-between relation.
test creating relation, without required fields.
test multi relation finding multi by id.
test modifying relation just by id, not creating/updating the relation.
empty file
file with no normal fields only relations
multiple sheets

plan: - load excel spreadsheet using excelise or whatever. - scan first row, and validate each field (and relation model) - then, scan each row. if relation, try find by id, else try find by name, else add to a batch to create by name (if other fields required, we mention this [i.e., could not find relation field product.uom by id or name 'unit(s)', cannot create with name 'unit(s)' as missing required field 'x' (either check for misspelling or add this column)]). if not relation, try type convert each value into the field's type. - if errors above, display errors by row. (and which rows are valid) - otherwise, continue to validation. need to create relation records if applicable, and use their ids to validate each row. (can i roll back the created records?, yes do it all in a transaction, deleting them at the end). - if all rows validated, display an import button and message that it all looks good. new plan: - checkbox before loading/validating for 'create missing records by name', with description - load excel, and scan first row getting fields and validating they exist, and type of each field. - if we have relations, determine creation priority. this is tricky -> how about, to keep simple, we create from top to bottom in order. if any not existing by name or id, create if all required fields present. nothing dynamic atm. - run a transaction with everything, and at the end discard. this is necessary rather than validation, as some child records require parent records to be created temporarily, for the child records validation. - so -> upload -> check/uncheck checkbox for 'create missing records (otherwise attempt to update, and error on missing by name/id)' -> validate (will have rich preview of all records to be created, nice little badges for created/updated, row level errs etc) -> upload. final touch ups: - change payload name from record to records, change all errors to return err.Error() where applicable. - change failed to failedcount, or failedinds or something. chaneg parent to parentcollection. - change filterColIndexes to something more descriptive - change the final return message to success instead of not implemented. - need to populate recordsToCreate and recordsToUpdate - what happens when we put in only the relation, not relation.id? (should be identical behaviour, maybe tack on the id) - what about select with multi select? need to split by comma probably. -> actually, remove all of this logic. ocketbase alreayd has preparevalue, findsetter, etc which normalise all the fields. better handles bools and everything. so just setvalue simply. for multi select, needs to be json serialisable so put [] around the values, like: ["opt 1", "opt 2"]. - change 'ImportRecordValidationResult' to have 'success' bool, and message instead of []Errors. - this behaviour is wrong: if relFieldToUpdate.IsMulti { record.Set(fmt.Sprintf("%s+", fieldName), relFieldToUpdate.RecordIds) },, instead just set it normally to the ids... also don't need diff behaviour if is multiple, pass the list of ids it's already handled in the field normalise. test cases: - test with basic, no related, few records. validate, and import. (for rest just test import) - test basic with invalid data (i.e. strign for number field) - test with one relation field, create by name. another with finding by id. - test with nested relation fields, with multi relations. another with complex relations, double multi, with varying number of relations in each level to test the correct separation. - test invalid excel with double nested relation with no data for find or create the in-between relation. - test creating relation, without required fields. - test multi relation finding multi by id. - test modifying relation just by id, not creating/updating the relation. - empty file - file with no normal fields only relations - multiple sheets