CSV Upload EnumList ValidIf Error

JPAlpano · 11-02-2021 03:00 AM

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.

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

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

Any help please.

Aurelien

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.

JPAlpano

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

Aurelien

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

Phil

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.

JPAlpano

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

Phil

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.

JPAlpano

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.

Phil

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.

Phil

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.

Aurelien

attn @Steve

JPAlpano

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.

Suvrutt_Gurjar

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?

JPAlpano

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.

Steve

@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)))

Phil

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

Koichi_Tsuji

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

JPAlpano

Hi Tsuji,

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

JPAlpano

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

Hi @Phil ,

Any news if your team has figured out a way to have this bug fixed?
My team is having to upload the CSV with only one value in the Enum List first,
and then they have to edit the uploaded data to change the Enum List values to the correct multiple selections.

Riobeef_Bio5

@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!

JPAlpano

Hi @Riobeef_Bio5

I can’t figure how to test your suggestion in my case.
I notice that your list options are being looked up from a column. (Filtro[Tropa])
But my list is being manually provided.

SWITCH([REQUEST TYPE],
	"EPAY", LIST("Visa","Mastercard","JCB","CUP"),
	"Online SIP", LIST("Visa","Mastercard"),
	LIST("Visa","Mastercard", "Debit","JCB","CUP","AMEX","AliPay")
)

And I’m curious with the use of your IN(). Does your filed allow multiple selections or only one?
The field I’m having issues with allows multiple selections.

So I’m not sure how using the IN() function may address it.

Riobeef_Bio5

Hi, you don’t have todo use IN in your case, you have tu use SPLIT to separarte the ítems of each list.

Hope it helps!

JPAlpano

Hi Rob,

Thanks for your response.

I still dont get it. In order to use SPLIT(), the option/choises must already be available in an existing column. That means that the choices are also fixed.

In my example, the available options changes depending on the entry from a previous column.
Also, when the user uploads that CSV, it has the multiple values in that column.
In order for me to use that split(), then that means the CSV must have alread been uploaded already, so that the APP can run the SPLIT() on those values.

Can you check the sample app I posted above? I would really appreciate it if you can help me with this.

Suvrutt_Gurjar

Is it possible to know while uploading through CSV what logic you use for the enum dependency? Meaning what are the possible values for the enum [Request Type] during CSV upload? And how you are using this CSV upload? Meaning for same [Request Type] can the “Cards Accapted” options be different from record to record?

Phil

JPAlpano,
No. I have not gotten to it yet.
Phil

Bean

@JPAlpano & @Phil

Our team is very much following this thread and interested in any developments. Please let us know if there is a solution or someone figures a workaround. Is there a way to bypass the valid if on upload for that field but keep the valid if for user input in AppSheet?

Bean

@JPAlpano & @Phil

Update - I think I found a solution using the INTERSECT function.

Legend: [Units] is my enumlist field I’m applying these conditions to, and [Unit] is my enum column.

Main Idea: split your input [Units] to get a list and compare how many times that list matches the values you’re willing to accept Buildings[Unit]. Only accept the input if # number of inputs= # of matches.

Valid IF:

COUNT(
   INTERSECT(SPLIT([Units],","),
     SELECT(Buildings[Unit], [Building] = [_THISROW].[Building]))
  )
= COUNT(SPLIT([Units],","))

Suggested Values filters to the values you want from the dropdown:

IF(OR([Status]="Value1",[Status]="Value2"), SELECT(Buildings[Unit], [Building] = [_THISROW].[Building]),
 SPLIT(SELECT(Activity[Units], 
   AND(([Building] = [_THISROW].[Building]),([Customer] = [_THISROW].[Customer]) )),","
  )
)

Please let me know if anyone finds anything wrong with this!

JPAlpano

Hi Bean,

Your validation assumes that entries inboth fields (Units and Unit) already exists. This is the only way you can use Split() function.

The issue is with a csv upload in which the upload fails because the EnumList column in the CSV is being rejected.

Since the upload does not go through, then there is no row where your Split() formula will be able to parse.

Note that my EnumList valid if is not fixed as the allowed andwers changes depending on a selection from another field. Your [Units] in your example says that the options do not change, hence you are able to do split. My EnumList column is not based in a other table and the options are being added dynically using List().

Suvrutt_Gurjar

Had requested to know your upload sequence.

As a workaround, please explore the following.

If you are willing to add another text or long text type hidden column in the table, then you could upload the required values through CSV as a text string. You could then split this uploaded text string in the valid_if of the enumlist to get the desired values for the enumlist.

But this approach’s working /feasibility will depend upon how you are linking [Request Type] and [Cards Accepted] fields.

JPAlpano

Hi Suvrutt,

Thanks for your suggestion. The ValidIf() of the [Card Accepted] field depends on the selection of the [Request Type] field.

SWITCH([REQUEST TYPE],
	"EPAY", LIST("Visa","Mastercard","JCB","CUP"),
	"Online SIP", LIST("Visa","Mastercard"),
	LIST("Visa","Mastercard", "Debit","JCB","CUP","AMEX","AliPay")
)

This means that if the users selected “EPAY” in the [Request Type] field, then the [Cards Accepted] field will accept four (4) card types.
If the user selected “Online SIP”, then the [Cards Accepted] field will only accept two (2).
And if the user selected any other [Request Type] other than the two mentioned, then all Card Types will be accepted.

Regarding the long text hidden field, if I implemented that, then that means the ValidIf() will now be depending on the hidden field, right? I’m just concerned how it will affect the manual entry via the form since the field will be hidden.

You can see the actual app and the sample CSV upload here:
CSV Upload EnumList ValidIf… (appsheet.com)

If you can make a copy and see the implementation, it may help explain more what I mean.
I would really appreciate some help here. Thanks.

Suvrutt_Gurjar

Hi @JPAlpano ,

Thank you. In the sample app you have shared, the sample CSV is inaccessible. The error message “404-File or directory not found” is getting displayed.

May we know how your CSV upload works? Does CSV upload also follow the Valid_if like expression? Meaning while uploading through CSV do you upload both [Request Type] and [Card Accepted] that exactly follow the Valid_if pattern?

JPAlpano

Hi Suvrutt,

Apologies as I have moved the CSV file to another folder. The link should be working now.

And to your question, yes, I upload both fields in the CSV.
If you try uploading the CSV with only one answer, say for example, Visa, then the upload goes through.
The moment you add multiple answers to the csv, then that’s where it begins to fails, since, as per Phil, the server reads it as a string of text instead of individual entries to loop through.

Suvrutt_Gurjar

Correct. You could have a further argument on the valid_if as below

IF(ISBLANK([CardsAcceptedCSV]),

SWITCH([REQUEST TYPE],
“EPAY”, LIST(“Visa”,“Mastercard”,“JCB”,“CUP”),
“Online SIP”, LIST(“Visa”,“Mastercard”),
LIST(“Visa”,“Mastercard”, “Debit”,“JCB”,“CUP”,“AMEX”,“AliPay”)
) ,

,

SPLIT([CardsAcceptedCSV], “,”)

)

[CardsAcceptedCSV] is the hidden text field used to upload the cards option. So if this text field is blank for a record, the normal valid_if expression that you have for forms is active or else the split text string that yields a list from the hidden text field is used.

JPAlpano

Hi Suvrutt,

Unfortunately, I cannot do this.
Because if I use your suggestion, then that means whatever the user uplaoded in the CSV will be used as the Valid expression. So let’s say the user selected “Online SIP” as the [REQUEST TYPE],
and in the CSV upload, he entered “Visa, Mastercard, “JCB”, CUP”, then this will be accepted as valid where as it should be rejected since only “Visa, Mastercard” should be valid for “Online SIP”

Suvrutt_Gurjar

Oh okay. Thank you for this clarification.

Suvrutt_Gurjar

Hi @JPAlpano ,

How about the below

in the valid_if of [CardsAcceptedCSV] you could have the following

SWITCH(
[REQUEST TYPE],
“EPAY”, ISBLANK(SPLIT( [CardsAcceptedCSV], ", ") -LIST(“Visa”,“Mastercard”,“JCB”,“CUP”)),
“Online SIP”, ISBLANK(SPLIT([CardsAcceptedCSV], ", ") - LIST(“Visa”,“Mastercard”)),
LIST(“Visa”,“Mastercard”, “Debit”,“JCB”,“CUP”,“AMEX”,“AliPay”)
)

The above valid_if will ensure that the text type CSV upload field is verified for validity based on [REQUEST TYPE] during the CSV upload process itself.

The cards accepted field’s valid_if then can continue to remain as below

IF(ISBLANK([CardsAcceptedCSV]),

SWITCH([REQUEST TYPE],
“EPAY”, LIST(“Visa”,“Mastercard”,“JCB”,“CUP”),
“Online SIP”, LIST(“Visa”,“Mastercard”),
LIST(“Visa”,“Mastercard”, “Debit”,“JCB”,“CUP”,“AMEX”,“AliPay”)
) ,

,

SPLIT([CardsAcceptedCSV], ", ")

)

JPAlpano

Now that’s something promising.
Let me try this and get back to you.

Really hope @Phil and the team can fix this on the server side so we don’t have to rely on our workarounds.

JPAlpano

Hi @Phil ,

Any news? Is this even in the pipeline for a fix?