Forms 11 Layout Designer - Lookup field does not populate when only one result

posted on February 7 • Show version history

I am experiencing this again. I had mentioned this before on this post but could get it going by setting the lookup to be per row (all values are per row and NOT outside of the table). Also, I did not hear if this is expected behavior or a bug.

I can see that lookup is returning the value via the Network tab in Chrome:

However, the value never makes it to the field in the table:

If I click the drop down value, it then populates. I have attempted to use javaScript to retrigger the lookup, but doing so really negatively impacts the time before the user can interact with the form. It takes to long to load AND is a workaround. However, by retriggering the field it will load correctly. No the issue seems to only be on load.

Here is the lookup:

There are no formulas on any of the above 3 fields.
Forms Data Types:
EmpID = Number

CalendarDayID = Number

hotText = Text/ Single Line

SQL Data Types:
EmployeeID = bigint
CalendarDayID = bigint

holYN = varchar(5)

Happen to ring a bell for anyone? Can anyone reproduce this in their environment?

Thanks!

0 0

replied on February 9

Hi Chris,

Your screenshot shows that the current form is a user task, so do you have values submitted for these fields in previous step?

Lookup rules triggered on form load would not overwrite previous submitted value on lookup target field, even if it was previously submitted as empty.

Only after user manually updated the trigger field of lookup rule, then the result would fill the target field.

0 0

View 1 previous reply

replied on February 10

Great question. I did look at that. There are no values in any of the rows of that column.

I could get this working. The issue has to do with the data type coming over from the view the lookup is pointing to. My confusion has to do with why a field formula could not successfully convert the data type. I am currently trying to reproduce the issue so I can share some details. I'll post them here when I have something.

0 0

replied on February 10

I can't reproduce it either in another test form.

There was another issue on this form regarding converting a data type.

Before I share the details, just know there were reasons that why I got off on the path of needing the 'true/false' column to be varchar in the view. I don't recall all the reasons, but it might have been trying to NOT deal with a Boolean data type.

The end problem, which seems reasonable, is that I could not convert a Forms number field value to evaluate in an IF statement in a formula. Please let me know if this is intended behavior.

formula in "Text Val":
=IF(
INDEX(testTable.holTxtNum, ROW()) = "TRUE",
"HOL",
INDEX(testTable.NumHrs, ROW())
)

Where testTable.holTxtNum is a number field type and is returning a varchar text string from a SQL view:

^as you can see the value does get returned in "True/False_num", but will not evaluate in the above IF formula.

I tried encapsulating it in TEXT() and even TEXT(VALUE()), but no luck. Is this expected behavior?

Thanks so much

0 0

replied on February 10

This is expected, the "False" value filled on the number field could not be parsed as a valid number so the value could not be used for any further calculations.

Can you use single line field here instead?

0 0

replied on February 11

I changed the value in the SQL view to be 1 or 0 and then it worked with the number field type. Thanks for the confirmation!

0 0

Discussion

Discussion

Forms 11 Layout Designer - Lookup field does not populate when only one result

Comments 5

Follow-Ups 0

Sign in to reply to this post.