How do I show column with "Valid_if" constraint while blank?

[rmsmeltz]

I have my Work_Order_Number column set with the following Valid_if expression:

IN( [_THIS], SELECT(Work Order Form[WO#], AND([_THISROW].[FNSKU_ASIN] = [FNSKU_ASIN], [_THISROW].[Amazon_Shipment_ID] = [Shipment ID], [Status]='Boxing' ) ) )

How do I prevent them from saving the form when this column is not included or blank?
I have the “Require” box checked.

[Marc_Dillon]

Try wrapping your expression with

OR( FALSE , #original-expression# )

This will prevent Valid_If from creating a dropdown of choices for the column, and force it to just evaluate a Yes/No condition. I’m assuming your issue is that if there is a blank list of valid options, it auto-hides the column, and “Required” is not enforced for hidden columns.

[rmsmeltz]

So, I don’t mind the drop down, but yes… I don’t want to column to be hidden and want it to remain required. I’m having a similar problem having a non-required column to display while also wanting the Valid_if to prevent duplicate values. I’m using the following expression:
OR(FALSE,IFS( ISNOTBLANK([Supplier Title]), UNIQUE(LIST([UPC])), ISBLANK([Supplier Title]), LIST("") ) )
This obviously doesn’t do anything and gives me an error, but I have been reading and tweaking all day and can’t figure anything out. You’re help is appreciated as always, @Marc_Dillon

[Marc_Dillon]

I’m confused, did my previous suggestion fix your first problem and now you’re asking about something else?

[rmsmeltz]

Kind of. Your previous suggestion makes the column show in the form, but now it seems to be a complete list of values, instead of only the ones based off my Valid_if constraints. Not a huge deal, since they normally scan the barcode, but it would be less confusing if the Work Orders in the drop down were only the ones which match the constraint.

My second question is the same as the first, but on a different table/column and not required.

[Marc_Dillon]

rmsmeltz:

Your previous suggestion makes the column show in the form, but now it seems to be a complete list of values, instead of only the ones based off my Valid_if constraints. Not a huge deal, since they normally scan the barcode, but it would be less confusing if the Work Orders in the drop down were only the ones which match the constraint.

Hmm. The primary purpose of wrapping your IN() expression with the OR(FALSE,…) is actually to inhibit the dropdown from appearing (it just so happened that I thought the same technique would meet your requirements in this particular case), but you’re saying the dropdown still appears? Is there perhaps something in Suggested Values?

rmsmeltz:

I’m having a similar problem having a non-required column to display while also wanting the Valid_if to prevent duplicate values. I’m using the following expression:
OR(FALSE,IFS( ISNOTBLANK([Supplier Title]), UNIQUE(LIST([UPC])), ISBLANK([Supplier Title]), LIST("") ) )
This obviously doesn’t do anything and gives me an error,

You need to provide more details about this new problem, I have no idea what you’re doing here.

[rmsmeltz]

It seems I have created a mess

Marc_Dillon:

Hmm. The primary purpose of wrapping your IN() expression with the OR(FALSE,…) is actually to inhibit the dropdown from appearing (it just so happened that I thought the same technique would meet your requirements in this particular case), but you’re saying the dropdown still appears? Is there perhaps something in Suggested Values ?

I do have something in suggested values. Is there a way to make those conditional? Similar to a Valid_if dependent drop down?

Marc_Dillon:

You need to provide more details about this new problem, I have no idea what you’re doing here.

Here I want the UPC field to show regardless, but the input is not required HOWEVER, if they input the UPC (always 12 digits), then it is unique. I still have the key column separate, but I don’t want two people from the same company to be able to enter the same UPC more than once (I have @MultiTech_Visions company/user protocol in place). I suppose I would also need the error to display the title of the existing entry, as that is the only piece of data required for any UPC they add.

[rmsmeltz]

rmsmeltz:

I’m having a similar problem having a non-required column to display while also wanting the Valid_if to prevent duplicate values. I’m using the following expression:
OR(FALSE,IFS( ISNOTBLANK([Supplier Title]), UNIQUE(LIST([UPC])), ISBLANK([Supplier Title]), LIST("") ) )
This obviously doesn’t do anything and gives me an error,

@Marc_Dillon I solved this problem with
NOT(IN([_THIS],UPC[UPC]))

[MultiTech · Accepted solution]

If you need to both apply validation, and provide a list of suggested values, then you need to make use of both formula spaces.

• use validif for the validation formula
• and use suggested values for the list of suggested values

Here’s something you might try for the valid if, it includes the requirement that the field not be blank.

And(
IsNotBlank([_this]),
IN( [_THIS], SELECT(Work Order Form[WO#], AND([_THISROW].[FNSKU_ASIN] = [FNSKU_ASIN], [_THISROW].[Amazon_Shipment_ID] = [Shipment ID], [Status]='Boxing' ) ) )
)

[Steve]

MultiTech_Visions:

Here’s something you might try for the valid if, it includes the requirement that the field not be blank.

Valid If is not evaluated if the input field is blank, so IsNotBlank([_this]) is redundant. I prefer just using TRUE in this case.