How to validate two columns in form

1

I would like to make the validation rule that one of two columns, A and B should be not blank.

But it seems the validation formula on A like IsNotBlank(CurrentValue) And IsNotBlank(thisRow.B) or Not(CurrentValue = "") And Not(thisRow.B = "") do not work.

How can I make the validation rule according to multiple columns?

2

Hey @Changjun_Lim ,
Depending on where you want to put the validation (in another column?), you could probably use:
if( IsBlank(thisRow.A) and IsBlank(thisRow.B) , "Error: A or B must have a value" , "correct ")

If one value has to be blank, you would use the opposite:
if( IsNotBlank(thisRow.A) and IsNotBlank(thisRow.B), "Error: A or B must be blank value" , "correct ")

It is always advisable to make a small doc with neutral data to share with the community so we can see what you are trying to accomplish, that makes it a bit easier to help.

Greetings, Joost

2 Likes
3

@joost_mineur
Your formula does not work on the validation of the form.
The validation formula should return boolean value, true or false.
Screenshot_2021-03-18 AHAM

And IsBlank(thisRow.A) and IsBlank(thisRow.B), which I alreay have tested, does not work.

4

Can you share an example doc with your desired output?

5

Sorry, misunderstood what you were trying to do, you never mentioned this was needed on a form. I think there is no easy way to do this at the current time. You can make one of the fields required, but that is not the answer to your question.

6

Did anyone manage to figure this out?

7

My current understanding of the topic is:

  • The value of the current field is accessible in the validation formula with currentValue and so are the other fields via the thisRow object, but if you want to use filter() with the current field it will throw a ‘Circular reference’ error. You can circumvent this by creating an additional calculated column to be the same as the one you want to use within filter().
    • If the form is embeded into another doc or is accessed via the published link, the filter() formula doesn’t work, even if you are referencing the same table, the ‘Include referenced tables’ option is activated and I’m logged in with the same user with full access to the original doc.
  • Calculated relation columns depending on form fields doesn’t update according to the current value of those fields in the form.

Does anybody know why it doesn’t work when the form is embedded or how can I work around this?

Here’s a little example:

1 Like
8

You can use thisrow in formula and call other property

image
image967×203 4.47 KB

image
image318×279 10.1 KB

1 Like
9

Hi @Math_24,

Thanks for answering. I’m of course aware of that option, that’s where I’m struggling.

I was testing this out in a complex doc I’m building, instead of creating a simple doc test like you did, which was silly. For some reason in my doc the warning about circular references is not displayed and that led to some confusion on my side.

What I wrote above makes no sense, so I will edit it with my current understanding, with the hope that someone can throw some light into the topic.

2 Likes
10

Yes, share a sample doc / example and we’ll see together if it’s possible.

1 Like