Question

Filling a drop down menu from a db lookup field

Forms

Updated April 1, 2016

asked on March 31, 2016

So I have come across this situation a number of times while creating forms. The most recent example is this: I would like to look up a person's name and return their position on a fairly large HR database. When their position matches a view, I'd like to fill in choices in a drop down menu. Since the lookup rules only run when the page loads, this creates an issue since the first value is necessary to fill the drop down menu with the list in the view.

I have looked around, and it seems as though I need some type of "on change" event to trigger after the initial position field contains data. What would be the simplest way to do so?

0 0

Replies

replied on March 31, 2016

Are these all field variables or are they in tables? I have found that no matter how many on change or java script you have, table lookups and fill ins are not very reliable.

0 0

replied on March 31, 2016

So, here's how it works... The user logs in, and it captures their name. A lookup is run on a db to find their position, then fill a position field. Then I have a number of "position selection" drop down fields that have fields rules on them to show or hide/depending on the position of the person logged in. So if the person is a manager, they will see the "position selection" drop down list for vice president, and the lookup will populate the list of vice presidents the person can choose from.

0 0

replied on April 1, 2016 • Show version history

Scott,

I believe that the issue you are running into here is a result of the fact that when lookups alter a field they do not trigger any events on that field (because the change was not made by the user).

We've run into similar situations and the way we have generally dealt with it is via the database side rather than through forms. In the situation you described, we would create a SQL view that gave the position selection information based off the person's name (joining via the position).

Something along the lines of:

SELECT People.Name, People.Position, Positions.PositionSelection 
FROM People
INNER JOIN Positions ON People.Position = Positions.Position

This could end up returning a very large amount of data (and slow down the responsiveness of your form) depending on the number of people in your database and the number of "position selections" for each position.

If that's the case, you would turn it into a stored procedure that accepted the name as input and used it in a WHERE clause to limit the amount of data.

EDIT: Fixed my poor use of English and formatting

0 0

You are not allowed to follow up in this post.

Question

Question

Filling a drop down menu from a db lookup field

Replies

Sign in to reply to this post.