Question

Lookup multiple rows from database

Forms

Updated November 9, 2016

asked on November 8, 2016

I would like to create a lookup for a table in sql server based on an employee id. The table I want to pull the information from will have and return multiple rows based on the employee id.

The table is basically monitoring which project and what percentage of time the employee will be devoting to it. So for example employee 1 may spend 50% time on project A and 50% on Project B.( attached example screen shot)

The "allocation_code" is the employee id, the "an_org" is the project/department and "an_allocation_pct" is the percentage of time allotted to each.

I would like to pull every project and percentage for a given employee and put it into a multiline field on the form. However when I try to create a lookup rule to query the table I don't get any results. I have other lookups for single values that work just fine.

Is what I am trying to do even possible?

Employee Project percentage time.JPG (20.1 KB)

| Download

0 0

Answer

SELECTED ANSWER

replied on November 8, 2016

Hi Mark, you definitely want to use a Table as it will work as you explained above.

When you create the Table select the Append Setting that appears in the Row area

Then in the Lookup rule, select the "as new row" to the right of the Fill area. The Data Source Values would be set to your DB Table's Fields.

When you choose the Employee name and the lookup runs, this will build a Dynamic Table just as you explained.

2 0

Replies

replied on November 8, 2016

Hi Anthony

It would be easiest to have the Data lookup populate a Table such as this

Is this where you want to get to or are you trying to trying to get all of the info into a Single Multiline Field?

0 0

replied on November 8, 2016

Thanks for your reply. Maybe I don't have it configured correctly but it seems as if after I preview this I have to pick from the drop downs to get the values using the table as you suggest.

I would like all the values populated without the user having to choose anything other than the employee id which is done on the form already.

Originally I figured that populating a single multiline with a lookup would be the best route. I picture it as the user enters an employee id and a multi line field is filled with however many different projects/department an employee may have. So if they have only two it shows the two or if they have 1o it lists all 10 projects/departments and time allocation percentage.

0 0

View 2 previous replies

SELECTED ANSWER

replied on November 8, 2016

Hi Mark, you definitely want to use a Table as it will work as you explained above.

When you create the Table select the Append Setting that appears in the Row area

Then in the Lookup rule, select the "as new row" to the right of the Fill area. The Data Source Values would be set to your DB Table's Fields.

When you choose the Employee name and the lookup runs, this will build a Dynamic Table just as you explained.

2 0

replied on November 9, 2016

Note: The table fields that you are populating into from the lookup should be Single Line or Numeric, not a Drop Down

1 0

replied on November 9, 2016

Thanks for this post but I'm using version 9.2 of forms and I don't see the options in your screen shots.

I guess I will have to upgrade.

LF Forms 9.2.JPG (53.92 KB)

| Download

1 0

replied on November 9, 2016

The level of Functionality provided in Forms 10 was a great leap in functionality. Highly recommend it.

1 0

replied on November 9, 2016 • Show version history

Thanks for your help Steve.

0 0

You are not allowed to follow up in this post.

Question

Question

Lookup multiple rows from database

Answer

Replies

Sign in to reply to this post.