Solved: Condition to fulfill in Drop Down Column

TotalSolutions · 11-17-2021 03:50 AM

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

TotalSolutions

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

View solution in original post

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.

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

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

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

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…