Question

Populate Table Row from Lookup Based on Row ID AND Populate Dropdown Fields With Other Options

Forms Version 11 Self-Hosted Forms Layout Designer

Updated February 5, 2024

asked on December 15, 2023

I am working on a Forms process that has a table. The table has 5 columns, one of them being a row id and the other 4 being drop-down fields. The end user needs to enter the 'Row ID' and that will kick off a lookup to return the corresponding row from a database table and populate a table row in the form (the row id is a field within the table row of the form).

With the drop-down fields populated from the row id lookup, I would also like them to populate with additional options from another lookup configured on each field.

The idea here is for the end user to see how a row in the database is currently configured and then using the drop-down fields, change the values if needed. This will then kick off a workflow that will then update the row in the SQL table.

Does anyone have any ideas how to accomplish this? I thought I had done this before, but using the new designer I am not having any luck and think I must have been thinking of something else.

0 0

Answer

SELECTED ANSWER

replied on December 18, 2023

Firstly make sure your Forms version is later than 11.0.2211.30807.

And you can use the configure these two rules like below. So the row would be the same of first rule's result and the Dropdown Fields would be populated by the second rule.

See details of configuration in attached file.

Business Process FillDropDownFieldTwoLookupRulesDifferentOption.txt (48.39 KB)

| Download

0 0

replied on December 19, 2023

It looks like it's missing the condition for the first one. The end user needs to enter in the row id value first and have it fill in the other 4 columns with the existing values.

Then the user would be able to select the drop-down fields for each of the 4 fields and change it to another value based on the lookup configuration for that field.

I think what your example is doing is populating multiple rows with all the various employee id's, which is not what we are wanting to do in this case.

0 0

Replies

replied on December 15, 2023

Usually in a situation like this I would use a stored procedure or view to join the two lists on the database side rather than on the form side.

For setting the "current" value, I had a hidden single line field that got the current value from the lookup and used javascript to set the value of the dropdown once it was populated.

I'm not sure the LFForm object currently has any methods that would allow you to modify a dropdown list's options; there's one for autoCompleteValues, but not seeing the same for dropdowns.

0 0

replied on December 19, 2023

I am currently researching a similar (but very different) process flow. We have a table that has users by departments and divisions and flags as to how certain other features should respond. So I want to create a process flow that allows the users to maintain these records without the need of direct database sql (our current process.) I have it where I use lookups on the e-mail to populate the form, however, in the case of a user having multiple records, this all breaks down. I also need to be able to add or delete records as needed. I want to have a process flow where it starts with an e-mail lookup and if no e-mail is found, the user goes to an 'Add record' form. If an e-mail is found, I want the user to have a screen displaying the records available to them, where they can modify an existing record, or delete a record. I know how to do the individual pieces, just do not know how to map it all into a single process flow. Thoughts?

0 0

replied on December 21, 2023

You can use Stored Procedures to update your database by configure proper procedures.

0 0

replied on December 27, 2023 • Show version history

I was able to find the Forms process where this concept works in the Classic Designer.

This screenshot shows populating a table row column with a value:

And this screenshot shows the same column being filled in a different Field Rule:

0 0

View 7 previous replies

replied on January 15, 2024

@████████ are you able to confirm if this is a bug in the Forms Layout Designer?

0 0

replied on January 24, 2024

Wanted to bump this since the lack of this ability in the Forms Layout Designer is preventing us from moving some Forms processes from the Classic Designer.

0 0

replied on January 30, 2024

Hi Blake,

Sorry for late response, I will take a look on your question and give you an update ASAP.

0 0

replied on January 31, 2024 • Show version history

Hi Blake,

If my understanding is correct, when you populate the "Employee's Name" field, first you want the table to populate rows with "HAADSC-DESC", and in the dropdown list, the options comes from "positionAssignment". I can make this work with Layout designer

Compare with classic designer

As you can see, the rules are defined same, and after input 'form' in the single line, I get the same list of IDs, with an extra "forms" option which is from the Last Name column

0 0

replied on January 31, 2024

Thank you for testing that. What version of Forms were you using for your testing?

0 0

replied on January 31, 2024

I'm using 11U5

0 0

replied on February 1, 2024 • Show version history

I just tested this in 11 Update 5 and it works until I get to the rules for the 4th column. Below is the basis of how the rules for all 4 columns are configured.

When I add the 2 rules for the Level 4 Approver, it only populates the first row. If I disable the rules for the Level 4 Approver it works as expected.

UPDATE: Doing some additional testing with the Level 4 rules disabled, I occasionally get rows back where rows are created, but all 4 column values are empty.

0 0

replied on February 4, 2024

Hi Blake,

For rule #8 and 10, are you using same field "Employee Whose Approval Matrix Rows...." in the condition? If they are same, this is not supported as you try to use one field in two rules as condition, and because the rules are executed one by one, so it's likely the later rule will overwrite former one. That's why you say disable rules for Level 4 will make it work, it's possible that the results are not overwritten.

In conclusion, this is not a supported way for lookup rules to execute.

0 0

replied on February 5, 2024

Zhiyong, thank you for the clarification on that. Could there be a verification of the lookup rules added to show a warning message when this happens? Currently it lets me save it and doesn't imply that it isn't supported.

1 0

replied on February 5, 2024

I ended up creating a stored procedure to pull the rows that I need and it is working as expected now in Forms 11 Update 5.

1 0

You are not allowed to follow up in this post.

Question

Question

Populate Table Row from Lookup Based on Row ID AND Populate Dropdown Fields With Other Options

Answer

Replies

Sign in to reply to this post.