Monthly Balance

This plugin creates records that store the monthly totals (such as inventory or accounts receivable) of a specified aggregation year in fields for January to December, for each client or product. It automates tedious aggregation and reduces input errors and manual work. The monthly trend is shown as a list, so you can see the movement of inventory and accounts receivable at a glance.

See the features

By using this product, you are deemed to have agreed to the Terms of Service.

How to install ＋

SETTING

Setup

Prepare the aggregation fields

In the app in which this plugin is installed (the destination), place the following fields to save the aggregation results. The numbers correspond to the numbers in the image.

Fields of the destination app

① Identifier Field: The field for the client, product or other unit of aggregation. Place a lookup, radio button or drop-down field.
② Aggregation Year Field: Place one number field to save the aggregation year.
③ Monthly Summary Fields: Place twelve number fields to save the aggregation results for January to December.
④ Carryover Balance Field: Place one number field to save the balance carried over from the previous aggregation year.

The app in which the source data is registered (the source) needs the following fields. The numbers correspond to the numbers in the image.

Fields of the source app

① Identifier Field: The field that contains the same values as the identifier field of the destination app.
② Date Field: The field, such as the transaction date or occurrence date, that determines the month in which a value is aggregated.
③ Aggregation Target Field: The field, such as an amount or quantity, that is totaled for each month.

After placing the fields, click "Update App" in each app to apply the changes. Fields that have not been updated cannot be selected on the plugin settings screen.

You can also combine multiple identifier fields. For example, combining product and warehouse lets you manage inventory for each warehouse.

Specify the settings

On the plugin settings screen, specify the fields of this app.

Settings

Identifier Field (e.g., client or product): Specify the field used as the unit of aggregation. For the specified aggregation year, one record is created for each value that can be set in this field. For a lookup field, one record is created for each source record that matches the filter conditions.
Aggregation Year Field: Specify the field in which the aggregation year is saved.
Aggregation Start Month: Specify the month in which the aggregation year begins. The twelve months from the specified month make up one aggregation year.

If you specify a lookup field as the Identifier Field (e.g., client or product), its "Key field" must be set to a field in the datasource app that has "Prohibit duplicate values" selected.

Then specify the fields that save the aggregation results, the source apps and the button that runs the aggregation.

Settings

Monthly Summary Fields: Specify the fields that save the aggregation results for January to December. Regardless of the start month, the result for January is saved in the field specified for "January".
Carryover Balance Field: Specify the field that saves the carryover balance of the previous aggregation year plus that year's aggregation results for January to December.
Source App: Specify the app in which the source data is registered. You can add rows to specify multiple source apps.
Source App Settings: Click "Open Settings" and specify the fields of the source app.
Execute Button Label: Enter the label of the button that runs the aggregation.
Confirmation message before execution: Enter the confirmation message shown before the aggregation runs.
Executable List View: Specify the views in which the execute button is shown.
Users, departments, and groups allowed to run: To allow the aggregation based on the logged-in user, or the departments and groups the user belongs to, specify who is allowed.

If you change the Identifier Field (e.g., client or product), the source app settings are reset.

For example, if you aggregate the quantities of a purchasing app with addition and the quantities of a sales app with subtraction, you can manage the monthly inventory of each product.

Specify the source app settings

Click "Open Settings" for a source app to show the settings screen of the source app.

On the screen that appears, specify the fields of the source app.

Settings

Identifier Field (e.g., client or product): Specify the field of the source app that is matched against the identifier field of this app.
Additional Filter Condition: To narrow down the records to aggregate further, specify the conditions.
Date Field: Specify the date field that determines the month in which a value is aggregated.
Aggregation Target Field (e.g., sales amount or quantity shipped): Specify the field that is totaled for each month.
Aggregation Method: Select whether the value of the aggregation target field is added or subtracted. For values handled in the negative direction, such as quantities shipped or refunds, select subtraction.

If the aggregation target field is in a table, the other fields must be in the same table or outside tables. If the aggregation target field is outside tables, all other fields must also be outside tables.

How to use the tabs ＋

HOW TO USE

How to use

Select the aggregation year

A drop-down for selecting the aggregation year and the execute button appear in the specified view. You can select from the nine aggregation years before and after the current one. Initially, the aggregation year that includes today is selected.

Run the aggregation

Click the execute button to start the aggregation. During the aggregation, the name of the source app being processed and the progress are shown.

Check the results

When the aggregation finishes, records for the selected aggregation year are created for each value of the identifier field.

If records for the selected aggregation year already exist, the monthly summary fields and the carryover balance field are reset before the aggregation is run again.

About the plugin execution order ＋