Condition to fulfill in Drop Down Column

AppSheet AppSheet Q&A
1

I have a dropdown (Enum) for Billing Status Column and the options shown are Make Bill, Bill E-mailed and Completed.

I also have a dropdown (Enum) for Accounting Status Column and the option is Not Done & Done.

So if we select Billing Status as Completed then the Accounting Status should have the value Done, only in that case it should allow the Billing Status to be selected as Completed otherwise it should show an error saying “Accounting Status is Not Done. Please enter that first then select the Billing Status as Completed.”

Please let me know how to do this.

Thank You!!

2

Hello @Total_Solutions, you can do that using the “valid if” and “invalid value error” parameters in your “Billing Status” column, the logic should be fairly simple, so i’ll add a few articles so you can read more about it.

2 Likes
3

Thanx for the quick response

To select the Billing Status as Completed, the Accounting Status should have the value Done. I am not able to figure out how to write the IF expression in the Valid If.

Can you please help?

4

Inside ‘Valid if’ an example I mention as follows-

IF (COUNT(Company_Info[Company Name])>0,TRUE,FALSE)

5

You can nest it as well.

6

shahappsheet:

IF (COUNT(Company_Info[Company Name])>0,TRUE,FALSE)

I need to write the formula in Billing Status Valid If:

IF ( AND ( [BillingStatus] = “Completed”, [SaleBill] = “Done” ), Then Completed Selection is Allowed, Otherwise Not Allowed )

How to write the above expression?

Please let me know. Thanx

7

“Then Completed Selection is Allowed, Otherwise Not Allowed”- What is this? Is it a view or action? What is the meaning of this? Where you intend to use it?

Thanks.

8

Total_Solutions:

I have a dropdown (Enum) for Billing Status Column and the options shown are Make Bill, Bill E-mailed and Completed.> > I also have a dropdown (Enum) for Accounting Status Column and the option is Not Done & Done.> > So if we select Billing Status as Completed then the Accounting Status should have the value Done, only in that case it should allow the Billing Status to be selected as Completed otherwise it should show an error saying “Accounting Status is Not Done. Please enter that first then select the Billing Status as Completed.”> > Please let me know how to do this.> > Thank You!!

I have a dropdown (Enum) for Billing Status Column and the options shown are Make Bill, Bill E-mailed and Completed.

I also have a dropdown (Enum) for Accounting Status Column and the option is Not Done & Done.

So if we select Billing Status as Completed then the Accounting Status should have the value Done, only in that case it should allow the Billing Status to be selected as Completed otherwise it should show an error saying “Accounting Status is Not Done. Please enter that first then select the Billing Status as Completed.”

Hope you got what I am trying to implement.

Thanx.

9

Oh, Sorry. I missed the top parts.

Checking, Whether I can Help…

1 Like
10

3X_4_6_46e2fdbb28e8425172093c136eb0953c83857b10.png
3X_4_6_46e2fdbb28e8425172093c136eb0953c83857b10.png376×525 17.5 KB

3X_7_0_707373425486c56ebd6d854bb1acd277e1f5cb2b.png
3X_7_0_707373425486c56ebd6d854bb1acd277e1f5cb2b.png377×525 16.7 KB

If you Click on Make Bill/Bill E-mailed it will show ‘Not Done’ but ‘Done’ will be shown when clicked on ‘Completed’.
Is it OK?

11

So kind of you to have taken the effort to make a test view!

Account Status needs to be Manual Selection Not Automatic. There is some reason why I want it that way.

Please let me know… Thanx.

12

3X_8_6_86042b72faf86d7a7a9e6eaa795aa32ebd217d3e.png
3X_8_6_86042b72faf86d7a7a9e6eaa795aa32ebd217d3e.png375×526 17.5 KB

3X_c_e_ced8aa474817131cb5bd305b142de2dbb860f1a1.png
3X_c_e_ced8aa474817131cb5bd305b142de2dbb860f1a1.png377×526 18.4 KB

3X_4_0_4007372038c43ed5acf1f76bfe4a23d2ff291a9f.png
3X_4_0_4007372038c43ed5acf1f76bfe4a23d2ff291a9f.png372×530 18.2 KB

IS IT THIS?

13

Here’s the simplest approach:

The Valid if expression for the Billing Status column:

IF(
  ("Done" <> [Accounting Status]),
  LIST("Make Bill", "Bill E-mailed", "Completed"),
  LIST("Completed")
)

The Valid if expression for the Accounting Status column:

IF(
  ("Completed" <> [Billing Status]),
  LIST("Not Done", "Done"),
  LIST("Done")
)
1 Like
14

Steve, I think he doesn’t want auto selection. It seems to me he want validation after manual selection. So far I could understand.

15

Yes that is exactly what I want. Validation after Manual Selection.

Please let me know

16

Good. I will post here today after my lunch. Thanks.

1 Like
17

Yes!!

18

Valid If of Account Status Field:

IF(OR(AND(TEXT([Billing Status])=“Completed”,TEXT([_THIS])=“Not Done”),
(AND(TEXT([Billing Status])=“Make Bill”,TEXT([_THIS])=“Done”)),
(AND(TEXT([Billing Status])=“Bill E-Mailed”,TEXT([_THIS])=“Done”))
)
,False,True)

Invalid Value Error of the Account Status Field:

IF(AND(TEXT([Billing Status])=“Completed”,TEXT([_THIS])=“Not Done”),“Done Should be Selected”,
IF(OR((AND(TEXT([Billing Status])=“Make Bill”,TEXT([_THIS])=“Done”)),
(AND(TEXT([Billing Status])=“Bill E-Mailed”,TEXT([_THIS])=“Done”))),“Accounting Status is Not Done”,“”)
)

19

Great! Your logic works!!

But there is some more to this:

Accounting Status which I have named as SaleBill has only 1 entry in the Dropdown which is Done, So incase Done is not selected means its blank means its Not Done.

When we select Completed in Billing Status it should check the above.

I have written the following formula with the help of your formula but there is something I am missing and can’t figure out.

Also my Billing Status is a Ref column and So: BS01 = Make Bill, BS05 = Bill E-mailed & BS35 = Completed

Accounting Status Column name is: SaleBill

IF ( OR ( AND ( [BS] = BS35, ISBLANK ([SaleBill]) ),

( AND ( [BS] = BS01, [_THIS] = ISNOTBLANK ([SaleBill]) ) ),

( AND ( [BS] = BS05, [_THIS] = ISNOTBLANK ([SaleBill]) ) )

)

, False, True)

Invalid Value Error of the Account Status Field:

IF ( AND ( [BS] = BS35, [_THIS] = ISBLANK ([SaleBill]) ), “Done Should be Selected”,

IF ( OR ( ( AND ( [BS] = BS01, [_THIS] = ISNOTBLANK ([SaleBill]) ) ),

( AND ( [BS] = BS05, [_THIS] = ISNOTBLANK ([SaleBill]) ) ) ), “Accounting Status is Not Done”,“” )

)

20

OK, At first I would like to know whether all the time user need to click both of the fields? If that so, no need to validate the BLANK. In the table select Require. Then no need to validate ISBLANK. But if you sometimes need to keep it unselected then it is necessary. But I think both of the button need to be selected/chosen all the time, isn’t it?

Please let me know. Then I will check your code.