Jump to: navigation, search

Difference between revisions of "Scout/Tutorial/3.8/Minicrm/Table Field"

< Scout‎ | Tutorial‎ | 3.8‎ | Minicrm
m (Deleting Rows)
 
(2 intermediate revisions by the same user not shown)
Line 84: Line 84:
 
{{warning|Permission denied|If you added [[{{BASEPAGENAME}}/Permissions|Permissions]] to your Minicrm, you will need to '''grant yourself the permission''' to create, read and update visits before you can use the menu!}}
 
{{warning|Permission denied|If you added [[{{BASEPAGENAME}}/Permissions|Permissions]] to your Minicrm, you will need to '''grant yourself the permission''' to create, read and update visits before you can use the menu!}}
  
Note how '''we did not add a column''' to the '''ParticipantsField'''. Before we do that, we need to write a '''PersonLookupCall'''. In the '''PersonLookupService''', use the following SQL statement:
+
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:
  
 
<source lang="java">
 
<source lang="java">
return "" +
+
  protected String getConfiguredSqlSelect() {
    "SELECT  P.PERSON_NR, " +
+
    return "" +
    "        P.LAST_NAME ||', '|| P.FIRST_NAME " +
+
        "SELECT  P.PERSON_NR, " +
    "FROM    PERSON P " +
+
        "        P.LAST_NAME ||', '|| P.FIRST_NAME " +
    "WHERE  1=1 " +
+
        "FROM    PERSON P " +
    "<key>  AND P.PERSON_NR = :key </key> " +
+
        "WHERE  1=1 " +
    "<text>  AND UPPER(P.LAST_NAME ||', '|| P.FIRST_NAME) LIKE UPPER(:text||'%') </text> " +
+
        "<key>  AND P.PERSON_NR = :key </key> " +
    "<all> </all> ";
+
        "<text>  AND UPPER(P.LAST_NAME ||', '|| P.FIRST_NAME) LIKE UPPER(:text||'%') </text> " +
 +
        "<all> </all> ";
 +
  }
 
</source>
 
</source>
  
Using this, we can create a '''ParticipantsColumn''' for the '''ParticipantsField'''. Make it a '''Smart Column''' and provide the '''PersonLookupCall''' we just created. Make sure the '''ParticipantsField''' table has '''Auto Resize Columns''' checked.
+
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 '''ParticipantsField''' table has '''Auto Optimized Width''' checked.
  
 
Result:
 
Result:
Line 106: Line 108:
  
 
What do we need to make this table editable? We need a way to '''add a new row'''.
 
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'''.
 
Add a new '''link button''' to the '''VisitForm''' and call it '''NewParticipantButton'''.
Line 242: Line 246:
 
== Deleting Rows ==
 
== Deleting Rows ==
  
A minor detail is missing: How do we remove participants? The '''ParticipantsTableField''' needs a delete menu: create a '''RemoveParticipantMenu''' and check '''Multi Selection Action'''. Set '''Key Stroke''' to "delete". Use the following for the '''execAction''':
+
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''':
  
 
<source lang="java">
 
<source lang="java">

Latest revision as of 14:09, 28 December 2012


Scout
Wiki Home
Website
DownloadGit
Community
ForumsBlogTwitter
Bugzilla
Bugzilla
Note.png
Scout Tutorial
This chapter talks about table fields: tables as part of forms.
This page is an optional addition to the Minicrm Step-by-Step Tutorial and uses it as a starting point.


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. :)

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       

If you want to take a look at the database using a command line tool, check this section of the tutorial.

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:

Minicrm Visits Rayo.png

(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:

Minicrm Visit Form Rayo.png

Warning2.png
Permission denied
If you added Permissions to your Minicrm, you will need to grant yourself the permission to create, read and update visits before you can use the menu!


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 ParticipantsField table has Auto Optimized Width checked.

Result:

Minicrm Participants.png

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.

Minicrm Editable Participants.png

Processing Service

Time to save things to the database! Switch to the VisitProcessService 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;
}
Note.png
Row Status
All rows are processed according to their status. If they don't contain a participant, we mark them as deleted. By default newly created rows are marked as inserted and without a participant the INSERT statement will fail.
Warning2.png
Missing Fields
As you can see, the form is missing two mandatory fields: one for the person responsible and one for the person contacted.


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;
}
Note.png
Bind Variables
The bind variables refer to session variables, fields, or columns. This is why we need to list the participants field as a bind base: this makes the participants column available.


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;
}
Note.png
Refactoring
Now would be a good time to move the saving of the participants field into a separate method of the process service and call it from create and store operations.
Warning2.png
It's complicated
If you have multiple columns in your table, you will have to careful. Maybe keep an invisible column with the primary. Remember to add another step where you update all the rows with the modified status. Or simply delete all entries and insert them again, ignoring the row status.


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.