Roundtrip data: Export and Import Error with Computed and Validation Expressions

[Fernananas]

Have you checked if this issue already exists?

• yes

Molgenis EMX2 version

Software version: v11.40.3. Database version: 23.

Description

When a computed expression retrieves a value from another table and is used in a field, the issue arises during the roundtrip of data export and import:

Export Behavior:
The column for the computed field is left empty in the downloaded data. This is acceptable if the computed value is correctly recalculated during the upload process. See screenshot example download, field: datebirth, is empty, this field is computed value.

Import Behavior:
However, during the import, the computed value is not populated, leaving the column empty. If this field is used in other validation rules, it leads to an error due to the missing value. The datebirth field is used as validation in entryDate, so if empty it gives error:

Reproduction steps

On the dev, I create schema: https://emx2.dev.molgenis.org/issueFernanda, there you can download Orders, and try to upload this downloaded xlsx. Then you get error.

If schemas are removed:

1. Go to https://emx2.dev.molgenis.org/
2. create new database with petstore and example data
3. Go to schema editor
4. Add column dateBirth in Order AND in Pet
5. In Order and column dateBirth, add computed expression: "pet?.dateBirth"
6. Add column entryDate to Order
7. Add validation expression in column entryDate: if(!(entryDate>=dateBirth))'First Contact date cannot be less than birth date'
8. Save everything
9. Go to pet, add a pet with dateBirth
10. Add new order selecting the pet with dateBirth
11. Make sure you also add a entryDate.
12. Save the order
13. Download orders with download button.
14. Go to Up/Download and upload the file you just downloaded.
15. See error.

Expected behaviour

1. During Export:

The computed field should either:
a. Be populated with the computed value in the exported data.
b. Be explicitly marked as a computed field to indicate it will be calculated upon re-import.

2. During Import:

If the exported data does not include values for computed fields, these fields should be automatically recalculated and populated during the upload process.
Validation should consider the recomputed values, ensuring no errors arise due to missing values in computed fields.

3. Roundtrip Behavior:

Data exported and then re-imported without modification should result in no errors or data loss, maintaining consistency and meeting validation requirements.
This ensures that the roundtrip process (export → import) is seamless and error-free, preserving data integrity and usability.

System configuration

• Device: computer
• Operating system: iOS
• Browser: Chrome