CSV Upload EnumList ValidIf Error

I have an EnumList in which the allowed options are controlled by a ValidIf constraint, based on selection on another field.

SWITCH([REQUEST TYPE],
	"IPG", LIST("Visa","Mastercard","JCB","CUP"),
	"EMAILPAY", LIST("Visa","Mastercard","JCB","CUP"),
	"Online SIP", LIST("Visa","Mastercard"),
  "MKI", LIST("Visa","Mastercard","JCB","CUP"),
  "EMOTO", LIST("Visa","Mastercard","JCB","CUP"),
	LIST("Visa","Mastercard","Bancnet Debit",
  			"BPI Debit","JCB","CUP","AMEX","AliPay","Ayala Card","Petron Card")
)

When entered manually, it works perfectly fine.
image

However, I have activated the CSV upload option, and entries are entered separated by comma.
image

However, appsheet rejects this. It only goes through if only one item is entered in the CSV column.
image

Any help please.

Hi @JPAlpano

On your field options of your column, from Data/Columns panel, did you check this ?


By default it will be blank space, comma, blank space.
You may want to set comma and blank space only.

2 Likes

Yes, I’ve put comma and blank space there.

I’m afraid I don’t see any other track to follow.
According on the big effort that have been made as reported on this thread, last june, I assume this may be a remaining mini-bug into the CSV-import process:

I suggest you contact Support on your case:
https://www.appsheet.com/Support/Contact

2 Likes

When you enter data via the client, the client does the Valid_If validation.
When you enter data via CSV Import, the server does the Valid_If validation.
CSV Import makes use of the REST API. The Imported CSV rows are deserialized and passed to the REST API Add method. It is the REST API Add method that is doing the Valid_If enforcement.

You have probably uncovered a case where the client and server Valid_If validation are not equivalent.
This is very likely a error in the way the server is enforcing Valid_If.
This code is quite complex and it is not surprising that some errors may be present.

Would it be possible for you to create a simple test case that reproduces the problem?
Then submit a bug report and provide the exact steps to reproduce the problem using your simple test case.
If you can do that, it makes it a lot easier for me to diagnose and fix the problem.

6 Likes

Yes. Sure.
With this test case, you mean you want me to also provided a test app with it?

1 Like

If you could that it would be ideal.
Thanks so much for doing so.
It really make it a lot easier for me to find and fix the problem.

1 Like

Hi Phil,

Thanks. See app below:
App Name: CSVUploadEnumListValidIfError-1251190
CSVUploadEnumListValidIfError-1251190

The sample pre-filled CSV for upload is already in the menu.
I’ve also allowed AppSheet staff to access the app in my Account settings.

1 Like

Will try to look at your issue this weekend.

I am in the midst of a major fix to CSV Export. It was not honoring UserFilter expressions. Three customers have reported this problem and I have spent over a month of coding effort to fix it and add tests to verify it is fixed. I am trying to get that fix released.

3 Likes

I stepped through the expression system on the server and I can see why it is failing. It is comparing the concatenated value in the ‘CARDS ACCEPTED’ field, namely ‘Visa, Mastercard, JCB’ to each of the values in the list selected by the SWITCH.

For example, assume the SWITCH selects the list “Visa”, “Mastercard”, “JCB”, and “CUP”. The expression system then compares the combined value ‘Visa, Mastercard, JCB’ to each of the individual items in the selected list. Of course, no value in the list matches the concatenated value ‘Visa, Mastercard, JCB’ .

I am not an expert on the expression system. I am not sure if the expression system on the server has a problem or if you are using the wrong expression in your Valid_If expression. The client seems to handle the Valid_If correctly so it may be an server expression system problem.

I need to talk with someone who understands expression better than I do, to determine if the problem if the result of a bug in the server expression system or in your Valid_If expression.

You might want to check with Steve Coile in the community. He is an expert on expressions (he wrote a lot of the expression documentation) and might be able to tell you if you are using the right Valid_If expression to validate the contents of one list against another.

3 Likes

attn @Steve

1 Like

Hi Phil,

if my Valid_if expression in the issue, then I assume that it should have caused errors in the app when making entries manually instead of CSV upload.

But as you have tested, it works fine, and the valid options get updated based on the selection of the [REQUEST TYPE] as provided by the Switch() statement in the Valid_If field.

Could you please once test the CSV upload for the enumlist field with double-quotes. For example, the above screenshot shows Visa, Mastercard, JCB. Could you have that column’s contents as below for all the rows in the CSV?

image

@JPAlpano @Phil

The Valid If expressions looks fine to me.

My interpretation is that the CSV import is trying to validate the import value as a string against the stringified version of the Valid If result, rather than doing something more appropriate for a list. For instance, I imagine the import is doing something equivalent to this:

CONCATENATE([CARDS ACCEPTED])
= CONCATENATE(valid_if-result)

Instead, for a column of a list type, I’d think it should be doing something like this:

CONCATENATE(SORT(UNIQUE([CARDS ACCEPTED])))
= CONCATENATE(SORT(UNIQUE(valid_if-result)))
2 Likes

Hi Suvrutt,

The reason why i entered it in the csv without the quotes, is because i first tried to enter it manually via the app by selecting the 3 items from the enum list field.

I then looked at the result in the underlying Google Sheet to know how I can enter the same via csv upload.

Nevertheless, I will try your suggestion.

I confirmed that this is a long standing error in the expression system on the server.
I have updated the docs to explain the problem. See topic " EnumList Field Import Fails with ‘failed Valid_If condition’" in this article

3 Likes

It is not an easy fix to escape the valid if expression on export to CSV?
Valie if constrain should have been evaluated the value inside the enumlist type column before the excport csv event to happen. So there wont be the necessity to apply this valid if constrain re-evaluation on exporting to CSV, that s my quick sort after reading this thread and revised documentation.

@Phil

1 Like

Hi Tsuji,

Thanks for your insight. But just to clarify, you were pertaining to Export?
The issue being encountered if for Import.

Hi Phil,

I’m no expert. But if I will imagine this in javascript, server should parse the EnumList column from the CSV file as an array, then loop through each value.

For each iteration, it will then be compared to each value in the Validif() expression.

To oversimply an illustration of it.

let arrEnumListFromCSV = [ EnumListColFromCSV ];

for (let i=0; i<arrEnumListFromCSV.length; i++) {
    // if else statement here to compare arrEnumListFromCSV[i] 
    // to each value of the ValidIf() expression
}

I hope it made sense.

@JPAlpano
Had a similar issue an solved it like this:

IN ( [Tropa N°], SPLIT ( Filtro[Tropa], “,” ))

SPLIT made the trick, now CSV isn’t blank.

The whole Row filter condition of the slice looks like this:

AND (
IF ( ISNOTBLANK ( FILTRO[Fecha inicio]), [Fecha] >= ANY ( Filtro[Fecha inicio] ), TRUE),
IF ( ISNOTBLANK ( FILTRO[Fecha fin]), [Fecha] <= ANY ( Filtro[Fecha fin] ), TRUE),
IF ( ISNOTBLANK ( FILTRO[Tropa]), IN ( [Tropa N°], SPLIT ( Filtro[Tropa], “,” )), TRUE),
IF ( ISNOTBLANK ( FILTRO[Sexo]), IN ( [Sexo], SPLIT ( Filtro[Sexo], “,” )), TRUE),
IF ( ISNOTBLANK ( FILTRO[Categoría]), IN ( [Clasificación], SPLIT ( Filtro[Categoría], “,” )), TRUE)
)

The problem was the way the IN function was getting the list generated by the enumlist fields.

Hope it heps!

1 Like