How to set up Valid If formulas in different states in the enumlist?

Hello /Chelsea,

How to set up Valid If formulas in different states in the enumlist ?

TABLES : ORDER
Column name : Order Status
Type: Enum
Values: 1. Open
2. Deliverable

How to set the Valid If formula?

The following 4 conditions must be met at the same time,
then the “Deliverable” in [Order Status] can be opened to click…

1.USEREMAIL()=ABC@gmail.com

  1. Accounts receivable=0

  2. Account receivable>0, but payment method=cash on delivery

  3. The goods arrive date before today and today.

THANK YOU SO MUCH~

Regards,
Chelsea

3 Likes

HI,STEVE

Thank you for your reply…

My Valid_If formula found an error!

=My formula=

AND
(IN([Order Status],“Deliverable”),(USEREMAIL="ABC@gmail.com"),[Accounts receivable]<=0)

=Error message=

Parameter 2 of function IN is of the wrong type

image

=Challenge=

I need to let those who meet the conditions at the same time

(for example…

(USEREMAIL="ABC@gmail.com"),

[Accounts receivable]<=0))

Only then can you open the [Deliverable] option in Type’Enum’.

How can my formula be corrected? Or is there any other way?

Thank you very much~

Regards,
Chelsea

1 Like
3 Likes

[quote=“CHELSEA, post:3, topic:50996”]

HI,LYNN

Thank you for your reply…

My Valid_If formula is adjusted as follows, there is no error message.

=Formula=

AND(IN(“Deliverable”,{“Deliverable”}),[USEREMAIL]=“ABC@gmail.com”,[Accounts Receivable]<=0)

=Question=

But this did not bring me effective help,

Are there any other settings that need to be adjusted?

Or does this formula need to be adjusted?

image

Thank you very much~

Regards,

Chelsea

2 Likes

Your first attempt was actually pretty close:

In response to @Lynn’s post, you changed your expression to:

In your first try, AppSheet was complaining because your IN() expression, IN([Order Status],“Deliverable”), wasn’t given a list to look in. The first argument to IN() should be what you’re looking for; the second argument should be the list you’re looking in. "Deliverable" is not a list, so AppSheet complained.

In your second try, you used IN(“Deliverable”,{“Deliverable”}). {“Deliverable”} is, in fact, a list containing a single item, the text, Deliverable. That satisfies IN()'s requirement for a list. The problem here, though, is that the first argument is also the literal text, Deliverable. Your expression asks the question, does the word Deliverable occur in a list that consists only of the word Deliverable? Yes, it does. Always. Every time. Without fail. :slight_smile:

What you want is the best from both tries:

IN([Order Status], {"Deliverable"})
3 Likes

Hi ,Steve,

Thank you for your reply…

Thank you very much, I corrected the formula…

= purpose =

image

=Formula=

AND(
AND(IN**(“Order Status”,{“Deliverable”}),([USEREMAIL]=" ABC @gmail.com"),( Accounts receivable]<=0)),
OR(
IN(“Order Status”,{“Open”}),TRUE))

=Question=

Is the formula I set up correct?

Does it meet my purpose?

I found that after the actual operation, the operation is always invalid,
But I can’t figure out what went wrong.

Thank you very much~

Regards,
Chelsea

No.

Use the expression I provided.

1 Like

Hi ,Steve,

Thank you for your reply…

I used the expression you provided and changed the formula.

=Formula=

OR(
AND(IN([Order Status],{“Deliverable”}),([USEREMAIL]=" ABC @gmail.com"),( [Accounts receivable]<=0)),
OR(IN([Order Status],{“Open”}),TRUE)

=Question=

Does it meet my purpose?

When I use the unauthorised [USEREMAIL]

& [Accounts receivable]>0 to select Deliverable,

The system agreed to this choice, and no invalid messages appeared.

How can I solve this problem?

Thank you very much~

Regards,
Chelsea

That’s for you to answer.

Hi ,Steve,

Thank you for your reply…

After using it, there is a problem…

situation 1 . Open

Fits my purpose.

situation 2 . Deliverable:

When I use the unauthorised [USEREMAIL]

& [Accounts receivable]>0 to select Deliverable,

The system agreed to this choice, and no invalid messages appeared.

Please help modify the formula?

Or how to solve the problem?

=Formula=

OR(
AND(IN([Order Status],{“Deliverable”}),([USEREMAIL]=" ABC @gmail.com"),( [Accounts receivable]<=0)),
OR(IN(“Order Status”,{“Open”}),TRUE))

= purpose =

Set up a formula in Valid If, so that it can be used in situation 1. or situation 2.

Thank you very much~

Regards,
Chelsea

IN(“Needle”,Haystack)

2 Likes

Hi, Lynn

Thanks for reply…

The formula has been adjusted,
But I found another problem…

Is USEREMAIL equal to the login email being accessed?
Or is it the first time or former writer?

This text…
[USEREMAIL]=" ABC @gmail.com "

I need to change USEREMAIL to [currently visiting user].

When the user who is visiting = ABC @gmail.com, the restriction is lifted.

The current situation is that [USEREMAIL] in excel is the first time writer (e.g. DD@gmail.com.
So invalidation occurs.

How can I modify it?

Thanks~

Regards,
Chelsea

2 Likes