Can multiple Forms Lookups append to a single table?

asked on September 26, 2017 • Show version history

Hello!

I've got a client who currently has a table in place to select students to go on field trips. When first discussing the functionality, they wanted to be able to select multiple entire classes, so the table now accepts variable numbers of rows, and displays one class per row.

The client has now stipulated that they would like an entire listing of all the students from the rows of selected classes to appear in a table on the same form to review which students will or will not go on the trip. This is easy to do with the first row, but we can only see one class worth of students at a time. Is it possible to append the results of all the class lookups into one table?

I've included an image of the general layout they'd like to achieve. Each Course + Section would be a lookup, that would populate the Students table, ideally appended one after another.

And while on the subject, secondary bonus question: Is it possible to do dynamic lookups with tables of variable rows? As in, one lookup for each row, and an indeterminate number of rows.

Thanks in advance!

layout.PNG (16.7 KB)

| Download

0 0

SELECTED ANSWER

replied on September 26, 2017

Hi Peter,

To answer the questions in title: multiple lookup rules cannot append to one single table column. The latter triggered one would overwrite the previous values. They could apply to different columns in one single table although it does not appear to apply to your case.

For the second question, when lookup rule match field is a field in table, and the target field is a field in another table, it would work like this: match row 1 in table 1 and fill row 1 in table 2, match row 2 and fill row 2, and so on. The lookup rule "as new row" option could not be used in this case.

But it is still possible to meet the client's demand by using formula and stored procedure:

First add a field with formula =Courses.Course, the value would be like course1,course2,...

Then add a stored procedure that reads this value and return all related students, possibly like this https://stackoverflow.com/questions/10333319/sql-in-variable-query

1 0

Question

Question

Can multiple Forms Lookups append to a single table?

Answer

Replies

Sign in to reply to this post.