Separated "DATA VALIDITY" for Adding new row and for Editing existing row

[Swoopy]

My app needs 2 separated validations for a column. The first one is for adding a new row, which is working fine (I have the code shown below in Valid if expression). But for updating data, another different line of code is required. I don’t know how to incorporate these 2 validations in a column Valid if expression because the Add and Edit require different validations. Please kindly advise.

[_THIS] <=
SUM(
SELECT(
Receive[Quantity],
[Object] = [_THISROW].[Object]
)
)

SUM(
SELECT(
Locate[Quantity],
[Object] = [_THISROW].[Object]
)
)

[Aleksi · Accepted solution]

You can wrap it with the IF statement. Something like IF(IN([KeyColumn],TableName[KeyColumn]),…,…)

[Swoopy]

Thank you for your advice. But how can I differentiate the Add mode from Edit mode with this IF(…) and IN(…) ? So, I can correctly put my 2 different codes in the IF().

I have 2 different valid codes for each mode. It’s something like

IF(being in Add mode, do Add valid code,
IF(being in Edit mode, do Edit valid code,
true
)
)

What commands can be used to check “being in Add mode” and “being in Edit mode”?

[Aleksi]

IN([KeyColumn],TableName[KeyColumn]) will give you a result TRUE if the record already exists and FALSE if not. Then you know which validation formula you should use inside of your IF statement.

[Swoopy]

Aleksi:

IN([KeyColumn],TableName[KeyColumn])

Thank you so much. It works.

Regards,

[Aleksi]

You’re welcome