Use Case – Gantt Chart

In order to clarify the purpose of the automation module and how to use it, let’s implement an example of its application.
In this example, we are going to add a Gantt chart through automation in an interface called (Project Management), which in this example is used for project management.

Project Management

In the first tab of this interface called (Projects) are where the projects are added and the information related to them is filled.
The second tab called (Tasks) is where the tasks of each project are filled in, such as Action, Assigned to, Start Date, Till when, Status, among other information.

Tasks

The purpose of automation in this case is to export the tasks of the selected project to another Excel spreadsheet that implements a Gantt chart, thus allowing a more comprehensive view of the progress of the project, as can be seen in the figure below.

Example of how automation will operate

The steps for creating this automation will be described below.

1. Go to the wizard (Automation) located in the tab (Design Tools)
2. Add a new record by clicking on (New) and fill in the fields as shown in the example below

*Note 1: The field called “Screen” is a drop-down list containing all the interfaces already created in the application. Automation will be linked to the interface selected in this field.

*Note 2: Filling in the field called “Save Data From Sheet Number” with the value 2 causes the data exported to the new worksheet to be saved from the second tab. In this example, this was done because the Gantt chart will be implemented in the first tab, that is, in tab number 1.

*Note 3 (Optional): Mark the checkbox called (Create Shortcut Button) and fill in the field (Shortcut Button Text). This will create a shortcut button on the screen selected in the field called (Screen).

3. Click on (Save) to save the data

4. The next step is to select the data to be exported. Click on the tab (Data Source) and then on the button (New)
5. Fill in the field (Note) and then click on the button with the figure of a magic wand
6. Move to the list called (Selected Fields) the fields that will be exported
7. Sort the data according to the key field of the table (Tasks), in this case the field (ID). This will cause the data to be sorted according to the order in which it was added by users

8. Filter only selected project data
   1. Click on the filter button to open the wizard screen
   2. Click the (Decision) button

In field (A) select the primary key of the table (Projects), in this case the field called (ID).
Under (Operators) click (=)
In field (B) select the function CurrentFieldValue(Table;Field)

This function will fetch the value of the field selected in (A), in this case the field (ID) when executing the query. See the example below.

*The objective is to export only the tasks of the selected project to the Gantt chart. Without the filter, all tasks from all projects will be exported.

9. Route the objects and then click the button (Save)

When returning to the previous screen, click on the button (Apply and Close). Returning to the automation screen, click on the button (Save).

10. Go to the interface in which the automation is associated, in this case the called interface (Project Manager) and execute the automation.

*Note that a new worksheet was opened and data from the current project was exported to tab number 2. Whenever the automation is executed, the data will be exported in the same order as defined in the query.

11. Configure the new worksheet according to your goals

In our example, we will implement a Gantt chart on tab 1.

12. Open the VBA editor and add the necessary code to copy the data from worksheet 1 to worksheet 2

13. Save the spreadsheet using the option MACRO-ENABLED WORKBOOK (.XLSM)

14. Return to the Smart Builder Tool system, go to the automation screen and click on (Edit)

15. In the field (File Name) click on the open file button and select the formatted worksheet

!Remember to exclude all data from the tabs where the data were exported (in this example the tab named ScheduleData), as updated data will be exported whenever the automation is run.

16. In the field (Run Macro On Worksheet) paste the name of the function that must be executed after exporting the data

*Note: In our example, we pasted the name of the function (LoadDataIntoGanttSheet), responsible for copying the data exported from the worksheet (ScheduleData) to the worksheet (Gantt)

17. Save and that’s it! Automation is configured.

*Each time an automation worksheet is opened, whether new or previously formatted, a tab called (SBT Info) is automatically created. Relevant system information is exported to this tab, such as the database path. This allows, for example, to create automations for importing data, among others.

SBT Info tab