Question

open formula prevents field rule from triggering

Version 12 Forms Forms Layout Designer

Updated July 9

asked on June 26

This is for modern forms.

I have a table that has validation field rules for Contractor Name, Email, and Phone number, when license number is entered. the trick is, the form must allow the customer to enter 1 contractor. To accomodate this, I have a seperate field called Confirm with the following open formula code:

=IF(CONCATENATE(INDEX(contractorTable.contractorName, 1),INDEX(contractorTable.contractorName, 2),INDEX(contractorTable.contractorName, 3))<>"","True","")

I notice that when I enter in the license number, the validation does not trigger unless I do one of two things:

1. I tab and focus on the affected fields: Contractor Name, Phone, Email.

2. if I have "Enforce validation on page progresson" checked. once I fill out all required fields, click Next then click Previous, upon entering in a license number the validation will kick in automatically without having to tab away from the Contractor License field.

I did find if I remove the open formula code, the validation works as intended.

form examples.docx (213.49 KB)

| Download

0 0

Replies

replied on July 9

Actually, now i look again - INDEX is a reference to the row number of the collection/table.

IF(CONCATENATE(INDEX(contractorTable.contractorName, 1),INDEX(contractorTable.contractorName, 2),INDEX(contractorTable.contractorName, 3))<>"","True","")

so that textjoins the contactors name of row1, row2 and row3 and checks if they're blank - it only looks at three rows. And it doesn't count 1 contractor name, if any contractor name is present then it will return true even if its the same name three times.

Is the requirement to make sure a contractor is not repeated in table rows?
We can use row() to get the row number of the current row.
We also use COUNTIF to look across all rows and display a duplicate if they exist.

Using row means the formula is not restricted to working on just INEDEX of three rows

=IF(AND(
  INDEX(contractorTable.contractorName,ROW())<>"",
  COUNTIF(contractorTable.contractorName,INDEX(contractorTable.contractorName,ROW()))>1
),"Duplicate","")

1 0

replied on June 26

I've seen this as well when concatenating values together, the formula doesn't seem to know how to treat the output from the concat. Give the following a try to see if it works differently

=IF(TEXT(CONCATENATE(INDEX(contractorTable.contractorName, 1),INDEX(contractorTable.contractorName, 2),INDEX(contractorTable.contractorName, 3)))<>"","True","")

0 0

replied on June 26

Thank you for replying. Though unfortunately, it appears to be doing the same thing.

0 0

replied on June 28

It looks like the fields in the table are set to read-only? I've encountered similar issues before, and find it's more reliable to leave the fields editable and use a rule to disable them instead. When calculations are used inside table rows, the built-in read-only setting can sometimes prevent the calculation from triggering correctly, whereas disabling the field via a rule tends to work consistently.

0 0

replied on June 29

Was able to get Contractor Email and Phone number working. but the Contractor Name is still not validating when field changes.

0 0

You are not allowed to follow up in this post.

Question

Question

open formula prevents field rule from triggering

Replies

Sign in to reply to this post.