In this section, you'll need to put to use everything you know about webforms. The concepts of client-side and server-side webforms query execution and their relationship is also introduced, so watch closely and hang on to your hat.
Making the edited deliveries disappear from the deliveries list
Simply put, you don't want to be in a situation where a user is able select to edit the same delivery task twice. When a delivery task is added to the editing section, you want it to pop out of the list of deliveries.
This is the functionality you'll be working towards:
The user decides to edit the details of the delivery task that is currently assigned to Evan Brady. He or she clicks the Edit button next to the delivery task entry. Note at this point, that the editing table below the task list is still empty: only its header appears in the form.
When the Edit button is clicked, the corresponding delivery task appears in the editing table. At the same time, the entry for the particular delivery task disappears form the delivery task list - note that the row count is now 24.
In webforms terms, you would like to apply a condition to the query in the
recordset of the taskList table control: if a given row
is present in the taskEditor table control, it should not be returned in
taskList.
In pseudo-code, the query in question looks like this:
SELECT <columns> FROM <reference table of deliveries> WHERE <id1>
NOT IN (SELECT <id1> FROM <rows>)
Where reftable is the
reference table of deliveries, id is a unique identifier column, and
rows is the currently displayed rows in the editing table
Sounds easy
enough. There is a catch, however. The syntax-compliant version of the pseudo-code above
would not work because it filters a complex query statement that returns a table value
result from the server (from the assignments reference table on the server)
with another complex query statement that returns a table value result from the client (from
the rows in taskEditor). Sadly, the server cannot access complex
browser-side query results: only scalar-value query results will get through to the server.
Bummer.
Don't despair, there is a way: the browser has no problem accessing the
server-side query. You need to pull the reference table query to evaluate on the client, and
somehow make the SELECT <columns> FROM <reference table of
deliveries>... part of the query evaluate on the client. But how?
Enter your
new best friend, the maker of helper variables: let. It will store anything
for you in one of its variables, if you ask nicely enough - even the table value result of a
server-side query statement. Then, you can filter this stored query result by the rows of
the taskEditor table control, as originally intended, and you're bound to
get the functionality you need.
This is just what the code below does.
... <table id="taskList" recordset='{SELECT t.driver, t.userid, t.assignment_id, t.client, t.load_address, t.load_date, t.due_date, t.unload_address, t.comments, t.photo_load, t.photo_unload, t.status FROM tasks t WHERE t.assignment_id NOT IN (SELECT te.assignment_id FROM taskEditor.rows te)}'record='task'><declarations> <let id='tasks' shape='table' value='{SELECT u.name driver, u.userid, a.assignment_id, a.load_type a.client, a.load_address, a.load_date, a.due_date, a.unload_address, a.comments, a.photo_load, a.photo_unload, a.status FROM assignments a JOIN drivers u ON a.usr = u.userid WHERE (driverFilter.selectedKey IS NULL OR a.usr == driverFilter.selectedKey) AND (clientFilter.selectedKey IS NULL OR a.client == clientFilter.selectedKey) AND (loadFilter.selectedKey IS NULL OR a.load_type == loadFilter.selectedKey) AND (statusFilter.selectedKey IS NULL OR a.status == statusFilter.selectedKey)}'/> </declarations>... </table> <table id='taskEditor' recordset='{SELECT "" as assignment_id, "" as userid, "" as client, "" as load_address, "" as unload_address, (dtl 2014-03-19T00:00:00) as due_date, "" as status WHERE FALSE}' record='edit'> ... <row><declarations> <let id='assignment_id' shape='scalar' value='{edit.assignment_id}'/> </declarations><cell> <dropdown id='editedDriver' choices='{SELECT u.userid, u.name FROM drivers u}' keyMap='{userid}' textMap='{name}' selectedKey='{edit.userid}'/> ...
See
what you did there? You simply cut the original query from taskList, and
pasted it as the value attribute inside a let named
tasks. Then, in taskList, you reference the query
result in tasks instead of the assignments reference
table, but get the exact same values. Now that there is no reference to any server-side
reference table in the taskList query statement, it is absolutely fine to
filter it using a WHERE clause that uses a client-side table.
As you
can see, there are, in fact, not one, but two let elements in the new code:
tasks and assignment_id. The second one is
needed because although in the new taskList query statement, you need a
reference to the assignment_id column inside the rows in the
taskEditor table (line 9), this column does not in fact show up in
taskEditor.
The newly set-up assignment_id
variable inside the taskEditor will not be displayed in the webform, but it
is nevertheless doubly crucial for the success of the form: you can reference it in the new
taskList query statement to make the disappearing magic happen, and it
identifies the delivery tasks to the workflow script that implements the user edits.
Save, publish, and access the form online. Move one or more rows from the top list into the editing section, and check that they disappear just like in the screenshots above.
Making the delivery comments popup link not show up when empty
The moment you put the Comments popup into the Rocky Jupiter Deliveries webform, the inconvenience of showing a popup link for empty comments came up, remember? While you're on a dynamic-filtering roll, let's take care of this issue once and for all.
Don't make the user think that there is a comment at the other end of a popup link when there is in fact none. Wrap the popup inside a conditional that specifies that it should only appear if there is something to display when the user clicks the link.
The
SQL LENGTH function (or
LEN
in some database flavors) returns the length of the value in
the location that you specify in the query. If the length of the comment for a given entry
in the assignments is not over 0, the popup will not appear.
Save, publish, and access the latest form online. Looks and feels so much better, no?
Figure 184. In the newest version of the webform, the Comments popup only show up if the delivery task has a comment in the first place
You've effectively finished the Rocky Jupiter Deliveries webform. All you need to do is add the usual validation to the editing part to prevent users breaking the reference table, and write the workflow script that processes the form submission, and makes the form functional. You could do this stuff blindfolded by now, so you'll be done with the final section in a jiffy.