Condition to fulfill in Drop Down Column

[TotalSolutions]

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

[Rafael_ANEIC-PY]

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.

help.appsheet.com

Valid_If Column Constraint

How to check if a given form input is valid based on its type.

help.appsheet.com

Conditional Expressions

How to use expressions so that behavior depends on a set of conditions.

[TotalSolutions]

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?

[shahappsheet]

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

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

[shahappsheet]

You can nest it as well.

[TotalSolutions]

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

[shahappsheet]

“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.

[TotalSolutions]

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.

[shahappsheet]

Oh, Sorry. I missed the top parts.

Checking, Whether I can Help…

[shahappsheet]

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?

[TotalSolutions]

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.

[shahappsheet]

IS IT THIS?

[TotalSolutions]

Yes!!

[Steve]

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

[shahappsheet]

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

[TotalSolutions]

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

Please let me know

[shahappsheet]

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

[shahappsheet]

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”,"")
)

[TotalSolutions]

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”,"" )

)

[shahappsheet]

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.

[TotalSolutions]

User will only Select the Billing Status as Accounting Status is updated by the Accountant So if its bank means its Not Done and If its Selected means the Accounting is Done.

[shahappsheet]

On that case my suggestion, Account Status should be automated. That means no need to select or deselect the Account Status. To me it is unnecessary as “Completed” means Account Status is “Done” and for the other two Billing Status, it is “Not Done”. Actually I can not feel any necessaity of this selection. It (Account Status) can be ready Only. However, I dont know whether you have any other purpose of the Selection of “Account Status”.

I dont know whether I could clear myself.

[TotalSolutions]

Logically you are correct but I need a manual selection on Accounting Status as that will be done by the Accountant and Billing Status is updated by the Sales person.

[shahappsheet]

Ok, No Problem. I honour your wish. Tomorrow, I will check it.

Thanks.

[TotalSolutions]

You are a Gem of a Person!!

[TotalSolutions · Accepted solution]

I put the following formula in Valid If of Billing Status and it WORKED!!:
IF ( AND ( [BS] = BS35, ISBLANK ([SaleBill]) ), False, True )

Also following in Invalid Value Error:
“Bill Entry not made in Accounts, once done you can set the Billing Status as Completed.”

This was possible ONLY BECAUSE OF YOU!!

You had mentioned the True False in your formula and that clicked me to try this and it worked!!

You are a Rockstar Genius!! God Bless!!

[shahappsheet]

You are so Kind. Wish you all the best.

[TotalSolutions]

Thank You Sooooo Much…