Skip to content

Automate your application

Note

This tutorial presupposes what done in Details: showing secondary data related to the row tutorial. If you have missed or skipped the previous tutorial or you want to experiment and go back, don't worry! You can import this .eap file to sync or restore with what you see in this documentation. Learn about resetting by using .eap files. If you don't use the .eap, what you see might differ slightly from the screenshots.

You will learn the fundamentals of workflow automations for eLegere by examples: Logical Columns, Custom Actions, eLegere Script, and Scheduled Jobs.

Logical Columns, Custom Actions, eLegere Script, and Scheduled Jobs introduce automated features in your application.

For example:

  • A column calculating values without recording on a Storage (i.e. Logical Columns as Calculated Field).
  • Creating a button on a row to perform a certain action (i.e. Logical Columns with a Custom Action).
  • Sending a push or email notification when an action in the application happens (i.e. Custom Actions).
  • Executing complex actions on one or more entries automatically (i.e. eLegere Script).
  • Scheduling an action at beginning of each week (i.e. Scheduled Jobs).

The tutorial teaches you how to create Logical Columns, Custom Actions, eLegere Script, and Scheduled Jobs by guiding you through some step-by-step examples.

How to calculate the Total Monthly Budget on Payment Methods

There are cases where you need to display data without storing them in a Storage. For instance, when you want to show a column with the sum or the difference between two or more columns. Such value doesn't require a dedicated field on a Storage to be displayed: the application can make the calculation in real time.

Logical Columns can cover this role among many: they calculate and display values in a table. They're useful to calculate and display data that you don't need to save in a Storage. Besides working as calculated fields, Logical Column can do much more and work in multiple ways. You will see later in the tutorial another example among many possibilities: using them to create buttons with Custom Actions. (See the later section How to Save the same Expense but with another Date if Recurring.)

You will learn in this section how to use Logical Columns as Calculated Fields and buttons for Custom Actions.

Suppose that you want to display in the Payment Methods application three extra columns:

In the cases above, you don't need to create three distinct fields in a Storage. The application can calculate the Monthly Expenses, the Monthly Income, and the Monthly Trend on the fly. You must create a Logical Column and insert a Formula that calculates the values to be displayed in real time.

Monthly Expenses

The Logical Column Monthly Expenses should display the value of all the expenses made with that payment method for the current month.

  1. Access the Design Mode of Payment Methods.

  2. In the Master sub-panel, click on the +Logical Column button tab next to the Master Table Name.

    As alternative, you can navigate to the Logical tab and click on the +Logical Column.

    You will access the sub-panel for the Logical Columns.

  3. In the Add Logical Column window, you can choose:

    • (1) The type of the Logical Column. There are three types available:

      In this tutorial, you will use the Calculated type.

    • (2) The position after or before a column. (You can change the order and the appearance of Logical Columns in the Master and Layout sub-panels at any time.)

    • (3) The column of reference to put the Logical Column after or before .

      Choose the options described in the steps below:

      • Choose the Calculated type for the Logical Columns.
      • Choose as Position After the column of reference in the table from the drop-down list.
      • Choose Icon as column of reference for the position.
      • Click on the Confirm button.

  4. You have created a Calculated-type Logical Column. Now, you must choose:

    • The name of the Logical Column in the table.
    • The type of data that the Logical Column will manage.

    The Logical column will have a name that explains its function and will make numeric calculation.

    • (1) Type in the Logical Column's textbox under Name Monthly Expenses.
    • (2) Choose from the drop-down list under the Logical column the Numeric type data.

  5. You must now configure the behavior of the Logical Column through a Formula. The Logical Column should calculate the total of the expenses in the Transactions storage and display the amount in the column.

    • (1) Click on the Formula tab in the sub-panel.
    • (2) In correspondence to the row of Monthly Expenses, click on the Create Formula button ().

    The Formula Editor will appear. Copy & paste or type the following formula:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
    =SUM(
    #Transactions; 
    #Transactions.BudgetValue;
    =AND(
        =EQ(
            #Transactions.PaymentMethodId;
            $CURRENTSTORAGE.IdPaymentMethod
        );
        =EQ(#Transactions.IncomeExpensesTypeId; 1);
        =GTE(
            #Transactions.BudgetDate;
            =CONVERT(
                =FORMATDATE($CURRENTDATE; "yyyyMM01");
                "INT"
            )
        )
    )
)

    The =SUM() formula sums all the values of the field BudgetValue. The =AND() formula is the third parameter of =SUM(). =AND() adds three criteria to pick up the entries for the calculation:

    • The value of PaymentMethodId field in Transactions Storage must have the same ID of the entry in Payment Methods
    • The field IncomeExpensesTypeId must have value 1. I.e. The entries must be "Expenses". "Expenses" has the ID 1 on the Storage Income_Expense_Type.

    • The entry must have a date equal or greater the first day of the month ("yyyyMM01"). In this way, the formula picks up only the expenses of the current month.

    • Click on the Confirm to validate the formula.

  6. You have assigned the formula for the calculation to the Logical Column. Besides the exceptions explained before, Logical Columns behave like any other column. So, you can configure the appearance and the format of Logical Columns.

    Go back to the Main tab of Master and choose for the Monthly Expenses column the View Format 1.234.10 €. Design Mode enables by default the Is Visible in Grid option when you create a Logical Column.

  7. Choose a color for the Monthly Expenses column as you did in Customizing the Application's Appearance in the Layout sub-panel.

  8. Save all the changes in Design Mode.

Congratulations, you have added a Logical Column to calculate the expenses of the month in Payment Methods.

Monthly Income

The Logical Column Monthly Income should display the total income received through a payment method for the current month.

To build a Logical Column for the Monthly Income, the procedure is the same as the section Monthly Expenses above. There is an exception for the Formula to insert in the Logical Column.

  1. Access the Design Mode of Payment Methods.

  2. Create in the Logical tab of the Master sub-panel a new Calculated Logical Column. Place it Before Monthly Expenses.

  3. Name it Monthly Income, and assign Numeric as data type.

  4. Go to the Formula tab in the Master sub-panel.

    Insert the following formula for the Monthly Income in the Formula Editor:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
    =SUM(
    #Transactions; 
    #Transactions.BudgetValue;
    =AND(
        =EQ(
            #Transactions.PaymentMethodId;
            $CURRENTSTORAGE.IdPaymentMethod
        );
        =EQ(#Transactions.IncomeExpensesTypeId; 2);
        =GTE(
            #Transactions.BudgetDate;
            =CONVERT(
                =FORMATDATE($CURRENTDATE; "yyyyMM01");
                "INT"
            )
        )
    )
)

    The formula above works like the formula of Step 6 in the previous section Monthly Expenses, but there is a difference in one of the formulas inside =AND().

    1
     =EQ(#Transactions.IncomeExpensesTypeId;2)

    The =EQ() formula specifies that the value of IncomeExpensesTypeId field must be 2. I.e. The entries must be "Income". "Income" has the ID 2 on the Storage Income_Expense_Type.

    The formula will pick up and sum only the entries "Income" from the Transactions Storage.

  5. In the Main tab of the Master sub-panel, choose the format 1.234.10 € for Monthly Income.

  6. Choose a color for the header of Monthly Income in the Layout sub-panel.

Congratulations, you have created a Logical Column to calculate your monthly income.

Monthly Trend

The Logical Column Monthly Trend should display the monthly expense trend divided by payment method.

You have a column for the Monthly Expenses and one for the Monthly Income. The budget trend for the month will be the difference between Monthly Income and Monthly Expenses. You will learn how to refer to Logical Columns in formulas.

The procedure is similar to Monthly Expenses and Monthly Income, but the formula changes.

  1. Access the Design Mode of Payment Methods.
  2. Create in the Master sub-panel a new Calculated Logical Column. Place it After Monthly Expenses.
  3. Call the Logical Column Monthly Trend and choose Numeric as data type as you did for Monthly Expenses and Monthly Income.

  4. Go to the Formula tab of Master sub-panel and insert the following formula for the Monthly Trend Logical Column.

    1
    $$Monthly_Income - $$Monthly_Expenses

    Use the syntax $$Logical_Column_Name when you want to refer to the value of a Logical Column.

    Tip

    If you have forgotten the orginal name of the Logical Column: in the Master > Logical > Logical Column click on the icon. The action copies the original name of the Logical Column. You can copy it in the formula.

    The formula makes the difference between the calculated values of Monthly Income and Monthly Expenses.

    Click on the Confirm button to validate the formula.

  5. In the Main tab of the Master sub-panel, choose the format 1.234.10 € for Monthly Trend as you did for Monthly Expenses and Monthly Income.

  6. Save all the changes in Design Mode.

Info

If you like, you can customize the appearance and behavior Monthly Trend as any other column. For example: follow the same steps found in Changing the Table Cells Color according to the Value section to color the cell according to the positive or negative value.

Congratulations, you have created a Logical Column that calculates the remaining budget for the month.

How to Send Notifications with the Current Balance after a New Expense

A convenient addition to Personal Budget application is an automated email with the current balance after an expense. In this way, after each record, Personal Budget will send an email to inform you about your balance.

You will learn in this section about Notifications and Custom Actions.

Custom Actions () are actions beyond the standard functions of eLegere that you can configure and run in an application. eLegere's Custom Actions' panel in Design Mode offers multiple action templates that you can choose and customize.

You must configure a Send Email Custom Action template to send a notification with the updated balance each time you save an entry.

  1. Access the Design Mode of Personal Budget.

  2. Click on the Custom Actions button on the right of the tool bar.

  3. You will access the Custom Actions menu. In the Custom Actions menu, you can configure custom actions to be performed under multiple circumstances for an application. Notification are an example and you are going to configure a Send Email Custom Action.

    Click on the Enable Module toggle-switch to enable to addition of Custom Actions.

  4. Click on the New Action drop-down list and choose Send Email as new Custom Action to be added.

  5. The Send Email custom action will show up in the Personal Budget's Custom Action list on the left. And a sub-panel for the configuration will appear in the center. New Custom Actions are enabled by default.

    Customize the Custom Action appearance in the Info menu:

    • (1) Change the name in the textbox Name to Current Balance.
    • (2) Choose an icon for the Custom Action. (For example , search piggy-bank).

  6. Disable the Manual toggle switch and choose from the drop-down list the option POST ROW INSERT.

    Personal Budget must send an email notification with the current balance after a new entry without further actions. You must disable Manual and choose POST ROW INSERT for this reason.

  7. In the Parameters menu, insert:

    • In the field Sender, the default elegere email address elegere@vesenda.com (or any other address available from the list).
    • In the field To, the address to receive the email notifications (e.g. j.doe@vesenda.com).

  8. In the Subject, copy & paste or type the following text and Formula: 

    1
    Current Balance [=FORMATDATE($CURRENTDATE; "dd-MM-yyyy hh:mm")]
    =FORMATDATE() takes the current date through the variable $CURRENTDATE and yields the date and the time formatted according to the second parameter.

    Warning

    Outside the Formula Editor, you must type between squared brackets ([]) formulas to be embedded inside a text.

  9. Insert the following text as Body of the email notification.

    1
2
3
4
5
    You have recorded a new expense.

The Current Balance is:

[=FORMATNUMBER(=SUM(#Transactions;#Transactions.BudgetValue;=EQ(#Transactions.IncomeExpensesTypeId;2)) - =SUM(#Transactions;#Transactions.BudgetValue;=EQ(#Transactions.IncomeExpensesTypeId; 1));"#,###. 00 €")]

    A brief explanation of the formula embedded in the text between square brackets:

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
    =FORMATNUMBER(
    =SUM(
        #Transactions;
        #Transactions.BudgetValue;
        =EQ(
            #Transactions.IncomeExpensesTypeId;
            2
            )
        )  
        -
        =SUM(
            #Transactions;
            #Transactions.BudgetValue;
            =EQ(
                #Transactions.IncomeExpensesTypeId; 
                1
                )
            );
    "#,###. 00 €"
)

    The formula =FORMATNUMBER() formats with two decimals the difference between two =SUM() formulas. The =SUM() formulas calculate the value of the income and the expenses recorded in Personal Budget.

  10. Save all the changes in Design Mode.

You have completed the configuration of the Send Email Custom Action. Every time you insert an income or an expense in Personal Budget and save it, you will receive an email with your balance.

How to Save the same Expense but with another Date if Recurring

You have learnt in the previous section about a simple Custom Action like Send Email. But you can design and automate more complex operations by combining Custom Actions, eLegere Script, and Logical Columns.

Every month you will probably have recurring income and expenses in Personal Budget: paycheck, mortgage or rent, food, bills, subscriptions, and so on. Copying them by hand each time is a tedious task. Having a Custom Action to duplicate an entry with a click would be helpful. You can create such button in your application by using eLegere Script.

eLegere Script is a language to code functions and extend what an eLegere application can do. Whatever you can code with eLegere Script, your application can execute it.

Tip

eLegere Script is an advanced topic. You don't have to deepen its knowledge for this part of the documentation: understanding Custom Actions and what eLegere Script can do for you is enough. The scripts you need are provided to copy & paste.

In this section, you have to:

  • Create a eLegere Script Custom Action
  • Create a Logical Column to launch the Custom Action in Personal Budget's Master table.

Creating the Repeat Entry Custom Action

  1. Access the Design Mode of Personal Budget
  2. Access the Custom Actions panel by clicking on the icon in the right corner of the tool bar.

  3. In the Custom Actions panel, choose and add a eLegere Script from the New Action drop-down list.

  4. Customize the Custom Action's information in the Info section.

    • (1) Type Repeat Entry as Name.
    • (2) Assign an icon to Repeat Entry, for example (search copy).

  5. Copy & Paste or type the following code in the Body of eLegere Script editor in the Parameters section.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
    // You extract the required data from the actual application's context.
const { ApplicationId, DomainId, NewItem, SessionToken, Url } = context;

// You extract the actual Date and Time to update the duplicate's BudgetDate and BudgetTime fields.
const currentDate = elegere.datetimeToNumber(new Date());
const elegereCurrentDate = parseInt(`${currentDate}`.substr(0, 8));
const elegereCurrentTime = parseInt(`${currentDate}`.substr(8));

// This block creates the new entry by copying the row and updating Date and Time.
const toRepeatEntry = {
    BudgetName: NewItem.BudgetName,
    BudgetValue: NewItem.BudgetValue,
    BudgetDate: elegereCurrentDate,
    BudgetTime: elegereCurrentTime,
    Notes: NewItem.Notes,
    PaymentMethodId: NewItem.PaymentMethodId,
    TransactionsProductServiceId: NewItem.TransactionsProductServiceId,
    IncomeExpensesTypeId: NewItem.IncomeExpensesTypeId,
};

// This block specifies that the new entry must be recorded in the current application.
const budgetingApp = await elegere.getApplicationFromToken(
    ApplicationId,
    DomainId,
    Url,
    SessionToken
);

// This function saves the new entry.
const saveResult = await budgetingApp.saveOne(toRepeatEntry);

  6. Enable the Refresh toggle switch.

    In this way, the application will update the Master table without clicking on the Refresh button () in the top-bar.

  7. Save all the changes in Design Mode.

You have created the Repeat Entry Custom Action!

Launching a Custom Action Manually

In an application, you can launch a Custom Action by:

  1. Clicking on the Options icon () of the table row.
  2. Clicking on the Action button ().
  3. Choosing the Custom Action to execute from the window (in this case, Repeat Entry).

In the next section you will learn how to assign Custom Actions to Logical Columns. Logical Columns as buttons for Custom Actions speed up the launch.

Assigning Repeat Entry to a Logical Column

You can run any Custom Actions without the need of a Logical Column. But Logical Columns enables to configure a dedicated button to launch specific Custom Actions.

You must now configure a Logical Column that executes the action when clicked.

  1. Move back to the Table panel in Design Mode.

  2. In the Master sub-panel, click on the Logical Tab.

  3. Click on the +Logical Column button and create a new Logical Column with the following options:

    1. Action Logical Column Type
    2. Position Before the Name column.

  4. You must now assign the Custom Action to the Logical Column.

    Click on the drop-down list Select Action and choose Repeat Entry as Custom Action.

  5. Save all the changes in Design Mode.

Personal Budget has now a button for each entry.

Clicking the icon triggers Repeat Entry for the row selected. Repeat Entry duplicates the entry and updates the Date and the Time.

Congratulations, you have created a custom button for your application!

Weekly Report of the Budget

A way to automate actions or notifications at certain times of the day, week, month or year are the Scheduled Jobs. In this section you will learn about Scheduled Jobs.

Scheduled Jobs are automatic programmed actions and scripts that run at specific time of the day, week, month or year.

For instance, a handy feature would be a weekly reminder of:

  • Total expenses made the past week.
  • The current balance.

You can configure as Scheduled Job an email reminding you each Monday about what you have spent last week and your balance.

This time, you will use a menu external to Design Mode: the Scheduler panel in Home.

  1. From the Home, click on the Scheduler tab in the left taskbar.

  2. Click on the button +Add Job on the upper-right corner of the panel

  3. The Add Job window lists multiple templates of Scheduled Jobs. You need to setup an email notification.

    • (1) Choose Send Email as template.
    • (2) Click on the Confirm button.

  4. The window to configure the Send Email will appear.

    Fill the fields of the Job tab as follows:

    • (1) Type Weekly Balance in the Name textbox.

    • (2) As Type, choose Recurring from the drop-down list and check the Enabled option.

    • (3) As Frequency Type, choose Weekly and select only Monday as checkbox.

    • (4) Choose in Occurs once at option a time to send the email notification in Daily Occurrence. For example 12:00.

    • (5) As Start Date, choose the actual date and leave End Date blank.

  5. Click on the Email Composer tab.

    You will access the Email Composer tab where you can configure the notification.

  6. Insert in the To field the email addresses to send the notification. For example j.doe@vesenda.com.

  7. Insert the following text in the Subject field.

    1
    Weekly Balance Report [=FORMATDATE($CURRENTDATE; "Monday dd-MM-yyyy")]

    The Formula in the squared brackets formats the current date in the email subject as "Monday dd-MM-yyyy". E.g. "Monday 29-08-2022".

  8. Copy & Paste or type the following text in the Body:

    1
2
3
4
5
6
7
8
9
    It's Monday and eLegere informs you about your Weekly Balance!

The Current Balance this week is: 

[=FORMATNUMBER(=SUM(#Transactions;#Transactions.BudgetValue;=EQ(#Transactions.IncomeExpensesTypeId;2)) -  =SUM(#Transactions;#Transactions.BudgetValue;=EQ(#Transactions.IncomeExpensesTypeId; 1)); "#,###. 00 €")]  

The past week you have spent: 

[=FORMATNUMBER(=SUM(#Transactions;#Transactions.BudgetValue;=AND(=EQ(#Transactions.IncomeExpensesTypeId;1);=GTE(#Transactions.BudgetDate;=CONVERT(=FORMATDATE($CURRENTDATE; "yyyyMMdd");"INT") - 7);=LT(#Transactions.BudgetDate;=CONVERT(=FORMATDATE($CURRENTDATE; "yyyyMMdd");"INT")))); "#,###. 00 €")]

    A brief explanation of the embedded formulas.

    The first formula calculates the total balance at the time of the notification.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    =FORMATNUMBER(
    =SUM(
        #Transactions;
        #Transactions.BudgetValue;
        =EQ(#Transactions.IncomeExpensesTypeId;2)
        )-=SUM(
            #Transactions;
            #Transactions.BudgetValue;
            =EQ(#Transactions.IncomeExpensesTypeId;1)
            ); 
        "#,###. 00 €"
)

    =FORMATNUMBER() formats with two decimals the difference between the values of the two =SUM(). The first =SUM() formula sums the values of BudgetValue field of entries "Income". The second =SUM() sums the values of BudgetValue field of entries "Expenses".

    The second formula makes the sum of all the entries classified as "Expenses" in the past 7 days.

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
    =FORMATNUMBER(
    =SUM(
        #Transactions;
        #Transactions.BudgetValue;
        =AND(
            =EQ(#Transactions.IncomeExpensesTypeId;1);
            =GTE(
                #Transactions.BudgetDate;
                =CONVERT(
                    =FORMATDATE(
                        $CURRENTDATE; 
                        "yyyyMMdd");
                    "INT") - 7);
            =LT(
                #Transactions.BudgetDate;
                =CONVERT(
                    =FORMATDATE(
                        $CURRENTDATE; "yyyyMMdd");
                        "INT"
                    )
                )
            )
        ); 

    "#,###. 00 €"
)
    The =FORMATNUMBER() formats with two decimals the value of the =SUM() formula. =SUM() has a =AND() formula as third parameter pointing what entries to consider. =AND() tells to pick up values satisfying all its three conditions:

    • Entries classified as "Expenses".
    • Entries with a BudgetDate greater than the actual date minus 7 days (i.e one week).
    • Entries with a BudgetDate lower or than the actual date.

  9. Save the changes by clicking the Confirm button. The new Scheduled Job will appear in the list.

Each Monday, you will receive an email with the current balance and the expenses of the last week!

At this point, you have an excellent eLegere application to manage your finance.

Success

If you like, check Using eLegere in a Team to learn how to use eLegere collaboratively. Using eLegere in a Team explains how to expand Personal Budget into Family Budget: an application that multiple family members with different access privileges share.