Expression Error in Validif

Rifad · 03-07-2023 12:36 AM

I have an expression in ValidIF. It works when I TEST it but returns nothing the app.

I contacted support and they told "Valid if only check the output but doesn't returns a value."

"It is just to check whether the value is matched with the condition or expression."

"With drop downs it's different scenario."

So I had to send him help documentations so that he can understand appsheet can return as Yes/no,List.

Then support told me they will look into the issue and contact me back and closed the chat. Does anyone know If i am doing it right ? Looks like a bug to me if I am correct.

Here is my code

SELECT(
			Product Bill of Materials[BOM Product ID], 
      
      IN(
        [Product Bill of Materials ID], 
        SPLIT(
        SELECT(
        			Sales Order Acceptance Items[List of Bill of Materials],
              [Sales Order Acceptance ID] = [_THISROW].[Sales Order Acceptance ID]
      
     				 ), " , " )
      	)
	
  		)

Suvrutt_Gurjar

Could you please try the below expression once?

SELECT(
			Product Bill of Materials[BOM Product ID], 
      
      IN(
        [Product Bill of Materials ID], 
        SPLIT(
        SELECT(
        			Sales Order Acceptance Items[List of Bill of Materials],
              [Sales Order Acceptance ID] = [_THISROW].[Sales Order Acceptance ID]
      
     				 ), "," )
      	)
	
  		)

Basically, the suggested change is "," instead of " , " in the SPLIT() expression.

Rifad

I tried. It returns empty when I test.

Rifad

Okay looks like this is showing correct now. But test in now not working. If it was this simple I think AppSheet Support need someone who knows about all this.

Rifad

Instead of VALIDIF same row same formula used in SUGGESTED VALUE it shows wrong result. And test window shows empty result.

Suvrutt_Gurjar

In my testing it works in test as well as the below screenshot shows for valid_if

Also for suggested values , I am getting the proper test results.

@Rifad wrote:

I think AppSheet Support need someone who knows about all this.

The help article on SPLIT() describes it well on when to use "," and " , " under the "common problems" section in the article.

SPLIT() - AppSheet Help

Could you please update your problem is now solved or not?

Rifad

I tried and its not working.

This is result for ValidIF

Result when Suggested Value

I tried wrapping with TEXT(). I tried with comma space comma. Its not giving accurate result.

Rifad

The column [List of Bill of Materials] is an ENUMLIST. Can that be a reason for this error ?

Suvrutt_Gurjar

I have noted that. In my testing [Bakery Products] that is used in SPLIT() expression is also an enumlist. I tried to create a test setup similar to yours.

Sorry, I have no more suggestions at his stage as according to my understanding of the requirement and testing, it should work in your case as well with "," 🙂

Rifad

Its just returns first ENUMLIST only of [List of Bill of Materials] ENUMLIST.

Row01: Test01 , TestO2 , Test03, Test04

Row02: Test05 , Test06

Expression	In App	In Test Window
ValidIF Expression	Returns 1st Row EnumList❌ Just 4 Items in dropdown	Empty
Suggested Values Expression	Returns Both Row EnumList✅ Shows 6 Items in dropdown.	Empty

Suvrutt_Gurjar

Oh okay. Thank you for all the neat details. Not sure why it does not appear in the test window. Could you try the below expression

SELECT(
			Product Bill of Materials[BOM Product ID], 
      
      IN(
        [Product Bill of Materials ID], 
        SPLIT(TEXT(
        SELECT(
        			Sales Order Acceptance Items[List of Bill of Materials],
              [Sales Order Acceptance ID] = [_THISROW].[Sales Order Acceptance ID]
      
     				 )), " , " )
      	)
	
  		)

Rifad

This works in VALIDIF now but not working correctly in Suggested values

Rifad

Shows in Test window. But not properly in the app. I will just use a simple expression and not waste my whole day on this. But hope appsheet support looks into the issue.

Koichi_Tsuji

Dont waste your precious time. 😀

Rifad

@Rifad wrote:

I contacted support and they told "Valid if only check the output but doesn't returns a value."

"It is just to check whether the value is matched with the condition or expression."

"With drop downs it's different scenario."

I already did. I had to send that person help documents about ValidIf behaviour.

Rifad

I had to send him this

Koichi_Tsuji

You mean my suggested expression does not help?

Koichi_Tsuji

This statement is correct.

If we pass the exprssion to this field, the users need to select the values out of it. If they select, this returns yes, while the user have no choice to select the value which returns false out of it. My undrestanding was your problem is not there.

Koichi_Tsuji

@Rifad

Yeah, this is "aray of array" kinda of problem with AppSheet.

Im not parsed my expression, so sorry if it is not working, but please try this out.

SELECT(

     Product Bill of Materials[BOM Product ID],

           IN(

          [Product Bill of Materials ID],

                 SPLIT(TEXT(SPLIT(

                         TEXT(SELECT(

                   Sales Order Acceptance Items[List of Bill of Materials],

                  [Sales Order Acceptance ID] = [_THISROW].[Sales Order Acceptance ID]    

            )),

            " , "

        )),",")   

    )   

)

Koichi_Tsuji

@Rifad

Before passing value to SPLIT expression, just wrap with TEXT() expression.

You need to do it twice.

Good luck!

@takuya_miyai

@Suvrutt_Gurjar