Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Scout/Tutorial/3.9/Minicrm/Table Field
Scout |
Wiki Home |
Website |
Download • Git |
Community |
Forums • Blog • Twitter • G+ |
Bugzilla |
Bugzilla |
Contents
What is this chapter about?
We will build a new table page for the Minicrm. It will list visits (or appointments, if you want). We will build a new form to edit visits. This form will contain a table field listing the people participating.
This chapter assumes that you're pretty proficient at creating tables, forms and services. No more hand-holding. :)
If you want to take a look at the database using a command line tool, Modify a derby database of the tutorial.
Database
The Apache Derby example database already contains the following tables:
VISIT VISIT_PARTICIPANTS --------------- ------------------ VISIT_NR VISIT_NR EVT_FROM PERSON_NR EVT_TO RESPONSIBLE_NR CONTACT_NR
Table Page
Start with VisitTablePage and a few columns: VisitNrColumn, StartColumn, EndColumn and OrganizerColumn. Check Has Time for both StartColumn and EndColumn; set their width to 200.
Result:
(Note: We switched the Swing client to the Rayo Look & Feel.)
Outline Service
Use the StandardOutlineService and add a new operation getVisitTableData:
@Override public Object[][] getVisitTableData() throws ProcessingException { return SQL.select("" + "SELECT V.VISIT_NR, V.EVT_FROM, V.EVT_TO, P.LAST_NAME ||', '|| P.FIRST_NAME " + "FROM VISIT V LEFT OUTER JOIN PERSON P " + "ON V.RESPONSIBLE_NR = P.PERSON_NR"); }
Have the VisitTablePage use this operation. Create an execLoadTableData operation:
@Override protected Object[][] execLoadTableData(SearchFilter filter) throws ProcessingException { return SERVICES.getService(IStandardOutlineService.class).getVisitTableData(); }
Form
Create a VisitForm with a GroupBox (this just looks better). Inside the group box, add two fields: StartField and EndField, both of them dates.
Finally, add a ParticipantsField of type TableField. Use Grid H 4. More if you typically have large meetings. :)
Add a NewVisitMenu and a EditVisitMenu to the VisitTablePage.
Don't forget to pass the selected visit to the ModifyHandler in your edit menu:
@Override protected void execAction() throws ProcessingException { VisitForm form = new VisitForm(); form.setVisitNr(getTable().getVisitNrColumn().getSelectedValue()); form.startModify(); form.waitFor(); if (form.isFormStored()) { reloadPage(); } }
Result:
Note how we did not add a column to the ParticipantsField. Before we do that, we need to write a PersonLookupCall (in Shared -> Lookup Calls). In the PersonLookupService (in Server -> Lookup Services), use the following SQL statement:
protected String getConfiguredSqlSelect() { return "" + "SELECT P.PERSON_NR, " + " P.LAST_NAME ||', '|| P.FIRST_NAME " + "FROM PERSON P " + "WHERE 1=1 " + "<key> AND P.PERSON_NR = :key </key> " + "<text> AND UPPER(P.LAST_NAME ||', '|| P.FIRST_NAME) LIKE UPPER(:text||'%') </text> " + "<all> </all> "; }
Using this, we can create a ParticipantsColumn for the ParticipantsField (in Client -> Forms -> VisitForm -> MainBox -> ParticipantsField -> Table -> Columns). Make it a Smart Column and provide the PersonLookupCall we just created for Lookup Call property. Make sure the ParticipantsColumn has Auto Optimized Width checked.
Result:
Make it Editable
What do we need to make this table editable? We need a way to add a new row.
First, make sure the ParticipantsColumn has Editable property checked.
Add a new link button to the VisitForm and call it NewParticipantButton.
Use this execClickAction:
@Override protected void execClickAction() throws ProcessingException { getParticipantsField().getTable().addRowByArray( new Object[]{null}); }
If you try it, you'll note that things don't look too nice. The following screenshot adds Grid W 2 for the ParticipantsField to force column spanning and Label Visible was unchecked since the column name is the same as the label. In addition to that, check Mandatory and Has Time for both StartField and EndField.
Service
Time to save things to the database! Switch to the VisitService that was created for you back when you created the form.
Create:
@Override public VisitFormData create(VisitFormData formData) throws ProcessingException { if (!ACCESS.check(new CreateVisitPermission())) { throw new VetoException(TEXTS.get("AuthorizationFailed")); } // FIXME: use a sequence! SQL.select("SELECT MAX(VISIT_NR) + 1 " + "FROM VISIT " + "INTO :visitNr", formData); if (formData.getVisitNr() == null) formData.setVisitNr(1L); // FIXME: need to add a smartfield for the responsible and the contact person SQL.insert("" + "INSERT INTO VISIT (VISIT_NR, EVT_FROM, EVT_TO, RESPONSIBLE_NR, CONTACT_NR) " + "VALUES (:visitNr, :start, :end, 0, 0)", formData); // empty names are deleted for (int i = 0; i < formData.getParticipants().getRowCount(); i++) { if (formData.getParticipants().getParticipants(i) == null) { formData.getParticipants().setRowState(i, ITableHolder.STATUS_DELETED); } } SQL.insert("" + "INSERT INTO VISIT_PARTICIPANTS (VISIT_NR, PERSON_NR) " + "VALUES (:visitNr, :{participants}) ", new TableHolderFilter(formData.getParticipants(), ITableHolder.STATUS_INSERTED), formData); return formData; }
Load:
@Override public VisitFormData load(VisitFormData formData) throws ProcessingException { if (!ACCESS.check(new ReadVisitPermission())) { throw new VetoException(TEXTS.get("AuthorizationFailed")); } SQL.select("" + "SELECT EVT_FROM, EVT_TO " + "FROM VISIT " + "WHERE VISIT_NR = :visitNr " + "INTO :start, :end", formData); SQL.select("" + "SELECT PERSON_NR " + "FROM VISIT_PARTICIPANTS " + "WHERE VISIT_NR = :visitNr " + "INTO :participants ", formData.getParticipants(), formData); return formData; }
Store:
@Override public VisitFormData store(VisitFormData formData) throws ProcessingException { if (!ACCESS.check(new UpdateVisitPermission())) { throw new VetoException(TEXTS.get("AuthorizationFailed")); } // FIXME: need to add a smartfield for the responsible and the contact person SQL.update("" + "UPDATE VISIT SET " + " EVT_FROM = :start, " + " EVT_TO = :end " + "WHERE VISIT_NR = :visitNr" , formData); // empty names are deleted for (int i = 0; i < formData.getParticipants().getRowCount(); i++) { if (formData.getParticipants().getParticipants(i) == null) { formData.getParticipants().setRowState(i, ITableHolder.STATUS_DELETED); } } SQL.delete("" + "DELETE FROM VISIT_PARTICIPANTS " + "WHERE VISIT_NR = :visitNr " + "AND PERSON_NR = :{participants}" , new TableHolderFilter(formData.getParticipants(), ITableHolder.STATUS_DELETED) , formData); SQL.insert("" + "INSERT INTO VISIT_PARTICIPANTS (VISIT_NR, PERSON_NR) " + "VALUES (:visitNr, :{participants}) " , new TableHolderFilter(formData.getParticipants(), ITableHolder.STATUS_INSERTED) , formData); return formData; }
Deleting Rows
A minor detail is missing: How do we remove participants? The ParticipantsField (in VisitForm) needs a delete menu: create a RemoveParticipantMenu and check Multi Selection Action. Set Key Stroke to "delete". Use the following for the execAction:
@Override protected void execAction() throws ProcessingException { for (ITableRow r : getSelectedRows()) { deleteRow(r); } }
This should work well with the store operation we created previously.