Checking for Required Columns in table

[mondob]

Hi,

I have page in Retool app where users can upload csv files. Files might be different format and have different columns.
There are 2 required columns: "vat_number" and "country".
How could I add rule to check if these columns exist?
This is the page of my app:

Users upload file, then map columns from file to columns in database and then click on button "Upload Leads".

I think the right way to check if required columns exist would be as follow:

1. When user click on "Upload Leads" the code that checks if columns "vat_number" and "country" exist runs.
2. If both columns exist then all following codes run as usual.
3. If column "vat_number" is missing, then user gets notification that says "File can't be uploaded without vat number". (I think module missingVatColumn should be opened).
4. If column "country" is missing, then user get notification that says "Leads can't be uploaded without country. Please choose a country of your leads from the list below." And there customer can choose the country from the dropdown list.

Could you help me to figure it out how the checking if required columns exist in database should be done? I think JavaScript code is needed here, but non of what I tried works.

[ScottR]

I think you need to know what is required in the database if you want to use JS and add the logic in it...but you could simply attempt to upload the contents of the file into the db and if it fails, show the user the error regarding the fields which are not nullable/cannot be empty

[AnsonHwang]

Hello, I think you should validate the data before insert it to database.

Here is my solution, I add a new column validation to the table.
and I will use js to validate the value if it's empty, if empty, the error message will display in this column, if no it will be empty. I also change the background of that column base on the length of that column, empty means no error.

{{(currentSourceRow[MapColumn.data.company]?"":"company name is empty; ")+(currentSourceRow[MapColumn.data.vat]?"":"vat number is empty; ")+(currentSourceRow[MapColumn.data.country]?"":"country is empty; ")}}

Here a export app json, you can upload to create app to check detail.
and also I attached the testing csv data for you referrence.
Pls notice that I use New table component
New XLS Worksheet.csv (99 Bytes)
test (1).json (21.3 KB)

Extra message:
In new table component, there is status indicator can archeive similar result. If you need you can dig in it.

Have a good day.

[mondob]

Hi @AnsonHwang, thanks for your replay.
I probably didn't present the question very clearly.
The thing is that I need to check not if the value exist in column, but I need to check if the column exists in table. Because there might be cases when user upload files that do not have vat_number or country columns.
So my question is how to check if column exist in dynamic table (where columns might be different each time)?

[AnsonHwang]

yes, I known you meaning. I ADD one extra column won't disappear. even you load different structure csv, it read the data base on the mapping data. as long as your mapping is right, the tooltip is right

[mondob]

ohh, I see now.
I've tried this on my app, but this do not work

[AnsonHwang]

{{(currentSourceRow[MapColumn.data.company]?"":"company name is empty; ")+
(currentSourceRow[MapColumn.data.vat]?"":"vat number is empty; ")+
(currentSourceRow[MapColumn.data.country]?"":"country is empty; ")}}

Pls be notice that, here property of company,vat,country come from the name of your select component

Pls use New table component instead of Lagencytable
with old(lagency table) you can't use currentSourceRow

Here is docs for new and old table different.

Table component and Legacy Table component differences | Retool Docs