Versions Compared

Old Version 8

changes.mady.by.user Hugo

Saved on

compared with

New Version Current

changes.mady.by.user Hugo

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

A plugin named "Database Query Process Tool" is also bundled together as a utility for loading records.

...

NameDescription
Iterator Method
  • Method to load the dataset.
  • Choose to load records either by using a Process Tool or from  Datalist
List
  • Choose list to retrieve dataset from.
  • This option is needed when "Iterator Method" is set to "Datalist".
Iterator Process Tool
  • Choose a process tool to retrieve the dataset from.

  • Process Tool must

return 

  • return a collection of map as dataset.

    Code Block
    languagejava
    titleSample Bean Shell Code
    linenumberstrue
    import org.joget.commons.util.LogUtil;
import java.util.Collection;
import java.util.Map;

Collection col = new ArrayList();

Map map = new HashMap();
map.put("id", "1");
map.put("name", "Alice");
col.add(map);

map = new HashMap();
map.put("id", "2");
map.put("name", "George");
col.add(map);

return col;
  • You may use "Database Query Process Tool" bundled with this plugin to enter a SQL query string.
  • This option is needed when "Iterator Method" is set to "Process Tool".
Record ID

Specify which column to be used as record ID / primary key from the dataset.

For example:-

  • id

Execute Process Tool

NameDescription
Process Tool
  • With the dataset loaded, the plugin will iterate through each record
,
  • and set the record or assignment as the context, and execute the process tool defined here.
For correct parsing of hash variable,
  • In the plugin configuration, when using hash variable, instead of using # to encapsulate, please replace # with $$ in your plugin for parsing to take place in the correct context. For examples:-
EXAMPLE -

    • Assignment mode, use $$form.request.name$$.

      Code Block
      languagejava
      titleExample
      linenumberstrue
      $$form.request.name$$
EXAMPLE -
EXAMPLE -

    • Code Block
      languagejava
      titleExample
      linenumberstrue
      $$form.request.name[@recordId@]$$

    • Retrieve values from iterator's dataset, use {columnName}.

      Code Block
      languagejava
      titleExample
      linenumberstrue
      {id}
      Code Block
      languagejava
      titleExample
      linenumberstrue
      {name}

Advanced

NameDescription
Debug ModeTurn this mode on to print out debugging information to the server log.
Delay Between Execution
  • Introduce a delay of 1 second between each record.
  • This option is useful in some use cases such as sending out email emails to prevent hitting the send limit per second.

...

  1. In this example, we are making use of Scheduler plugin to trigger the plugin at 7am daily. Set the plugin to "Iterator Process Tool (Record)".

  2. We can make use of "Database Query Process Tool" as the mean to pick up the records later. First, we will need to prepare the SQL to pick up bookings that are happening within 2 days from the time the job runs.

    Code Block
    languagesql
    linenumberstrue
    SELECT id, c_subject, c_name, c_room, c_date_from, c_date_from FROM app_fd_mrb_booking  
WHERE c_date_from > NOW() AND DATE(c_date_from) < DATE_ADD(NOW(), INTERVAL 2 DAY)
/*pick up bookings happening within 2 days from now to send reminder*/


    Take note that it is important that we return the "id" column as it will be used to set the record context for hash variable to work.

  3. This is the complete configurations.
  4. Choose "Process Tool", followed by "Database Query Process Tool"
  5. Set "id" in "Record ID" for the reason explained above.
  6. In "Execute Process Tool", choose "Email Tool".
  7. The email tool will be triggered once per record it iterates through.

  8. Observe the server log or check emails to verify.

    Code Block
    languagebash
    linenumberstrue
    INFO  15 Mar 2022 18:19:00 org.joget.marketplace.IteratorProcessToolRecord - Executing Iterator
INFO  15 Mar 2022 18:19:00 org.joget.marketplace.DatabaseQueryProcessTool - Query: SELECT id, c_subject, c_name, c_room, c_date_from, c_date_from FROM app_fd_mrb_booking  _WHERE c_date_from > NOW() AND DATE(c_date_from) < DATE_ADD(NOW(), INTERVAL 2 DAY)_/*pick up bookings happening within 2 days from now to send reminder*/
INFO  15 Mar 2022 18:19:00 org.joget.marketplace.DatabaseQueryProcessTool - Rows returned: [{c_subject=Product Discussion, c_room=44cefb42-289f-46db-b90c-6430224e8506, c_date_from=2022-03-16 09:00, c_name=Hugo, id=2960b356-161d-451a-8c2d-85fdebf2e111}, {c_subject=Project Debrief, c_room=44cefb42-289f-46db-b90c-6430224e8506, c_date_from=2022-03-17 09:00, c_name=Alexa, id=2960b356-161d-451a-8c2d-85fdebf2e121}]
INFO  15 Mar 2022 18:19:00 org.joget.marketplace.IteratorProcessToolRecord - Iterator returned: 2 items: [{c_subject=Product Discussion, c_room=44cefb42-289f-46db-b90c-6430224e8506, c_date_from=2022-03-16 09:00, c_name=Hugo, id=2960b356-161d-451a-8c2d-85fdebf2e111}, {c_subject=Project Debrief, c_room=44cefb42-289f-46db-b90c-6430224e8506, c_date_from=2022-03-17 09:00, c_name=Alexa, id=2960b356-161d-451a-8c2d-85fdebf2e121}]
INFO  15 Mar 2022 18:19:00 org.joget.marketplace.IteratorProcessToolRecord - Iterating item: 1 - Record: 2960b356-161d-451a-8c2d-85fdebf2e111
INFO  15 Mar 2022 18:19:00 org.joget.marketplace.IteratorProcessToolRecord$1 - Executing tool: executeProcessTool - org.joget.apps.app.lib.EmailTool
INFO  15 Mar 2022 18:1719:00 org.joget.marketplace.IteratorProcessToolRecord$1 - Executed tool: executeProcessTool - org.joget.apps.app.lib.EmailTool
INFO  15 Mar 2022 18:1719:00 org.joget.apps.app.lib.EmailTool - EmailTool: Sending email from=hugolim@outlook.com, to=hugo@joget.org, cc=, bcc=, subject=Booking Product Discussion
INFO  15 Mar 2022 18:1719:02 org.joget.apps.app.lib.EmailTool - EmailTool: Sending email completed for subject=Booking Product Discussion
INFO  15 Mar 2022 18:1719:05 org.joget.marketplace.IteratorProcessToolRecord - Finished item 1 - Record: 2960b356-161d-451a-8c2d-85fdebf2e111
INFO  15 Mar 2022 18:1719:05 org.joget.marketplace.IteratorProcessToolRecord - Iterating item: 2 - Record: 2960b356-161d-451a-8c2d-85fdebf2e121
INFO  15 Mar 2022 18:1719:05 org.joget.marketplace.IteratorProcessToolRecord$1 - Executing tool: executeProcessTool - org.joget.apps.app.lib.EmailTool
INFO  15 Mar 2022 18:1719:05 org.joget.marketplace.IteratorProcessToolRecord$1 - Executed tool: executeProcessTool - org.joget.apps.app.lib.EmailTool
INFO  15 Mar 2022 18:1719:05 org.joget.apps.app.lib.EmailTool - EmailTool: Sending email from=hugolim@outlook.com, to=hugo@joget.org, cc=, bcc=, subject=Booking Project Debrief
INFO  15 Mar 2022 18:1719:06 org.joget.apps.app.lib.EmailTool - EmailTool: Sending email completed for subject=Booking Project Debrief
INFO  15 Mar 2022 18:1719:10 org.joget.marketplace.IteratorProcessToolRecord - Finished item 2 - Record: 2960b356-161d-451a-8c2d-85fdebf2e121
  9. Once verified working, it is advisable to turn off the debug mode.