Resources - Documentation

Last updated: 09-Dec-2021

Overview

Resources are interfaces for users to add to, interact with and modify database records. Resources have prebuilt interface components for a smooth user experience. Components include tables, detail windows, record insertion and edit forms, charts, geo-maps, data export/PDF generation, sharing and emailing data, filter menus and tabs to group related data together such as displaying a customers invoices along with their profiles.

Resources also provide a RESTful JSON API to perform database CRUD operations and any of the actions Sphere ERP UI is capable of from any client that supports REST.

Resources are thus created when you want to provide an interface to interact with the data in your database.

To create a resource, go to "Developer tools" in the main menu and click on resources, the click on add. Fill in the basic details and a basic JSON object for the config such as one containing only the config level "0" with a "crud_table". The resource details and configration can be changed at any time by previledged users to fit operational requirements.

Details

ItemDescription
Resource nameThe display name of the resource. Will be used in Sphere ERP UI elements such as the menu and window titles. Will also be available in Sphere ERP API responses to resource requests.
CategoryFor organisational purposes. Is used to categorise resource menu items in Sphere ERP UI. Will also be available in Sphere ERP API responses to dashboard requests. Can also be used as a filter item in the list of resources to quickly navigate to a desired resource configuration.
For branchesThe branches to which the resource will be displayed in the menu. If a branch is set here, then this resource will only be displayed on the menu if a user naigates to the particular branch in Sphere ERP UI or Sphere ERP API. If you wish to have the resource available in all branches, enter "all"
Resource uriA unique identifier of the resource. It is used in API requests for the resource.

NOTE:Use only lowercase letters and underscores (_). Do not start with an underscore.

EnabledIf checked, then the resource will be able to process requests. If it is not checked then any requests to the resource will return an error message of id "resource_disabled".
ListedIf checked and the resource is enabled, then a menu item for the resource will be available in Sphere ERP UI under "Resources" (also see "For branches" above) and dashboard Sphere ERP API requests
Tutorial videoYou can upload a tutorial video here for users to learn how to use a resource. It will be displayed when a user clicks on the info icon in a resource view.
DescriptionThis is a description of the resource and will be displayed when a user clicks on the info icon in a resource view.
ConfigA JSON object with properties to define a resource elements. See config below.

Config

The properties of a resource are configured using JSON with initial sensible defaults based on the "crud_table" database table structure. Different configurations can be defined for different access levels, users, user groups or staff positions.

Only the "crud_table" property is required. The other properties are assigned defaults based on the underlying table structure but can be modified by creating the properties.

{
    "import": "| resource uri |",
    "req_config": "| overiding resource config without levels |",
    "if_is_access_level": "| overiding resource config with |config_level| |",
    "if_is_child": "| overiding resource config with |config_level| |",
    "if_is_child_of": {
        "| resource_uri|": "| overiding resource config with |config_level| |"
    },
    "| config_level |": {
        "list_limit": "(default=50)",
        "d_title": "(string or SQL query)",
        "singular": "(string or SQL query)",
        "edit_constraint": "(WHERE clause of SQL query)",
        "delete_constraint": "(WHERE clause of SQL query)",
        "render_type": "(default=plain_table)",
        "deletable": "(1 or empty)",
        "capitalize_input": "(1 or empty)",
        "true_dates": "(1 or empty)",
        "custom_actions": "(coma separated list)",
        "phone_col": "(list_query result set column name)",
        "email_col": "(list_query result set column name)",
        "collapsed": {
            "master_cols": "(list of columns)",
            "detail_cols": "(list of columns)"
        },
        "master_sums": "(default=SELECT COUNT(id) AS N FROM |crud_table| WHERE {request.childkey}={request.parent} AND {where.clause})",
        "list_query": "(default=SELECT * FROM |crud_table| WHERE {request.childkey}={request.parent} AND {where.clause})",
        "detail_query": "(default=SELECT * FROM |crud_table| WHERE id={request.id})",
        "default_grouping": ""
    },
    "|config_level|": {
        "local_variables": {
            "< variable name (string) >": " variable value (string or SQL query that returns a variable) "
        },
        "|crud_table|": "|database table name|",
        "master_options": [
            "add",
            "sms",
            "email"
        ],
        "data_conn": {
            "host": "",
            "user": "",
            "pass": "",
            "db": ""
        },
        "list_sum": [],
        "order_field": [
            "column (default=id)",
            "direction (default=ASC)"
        ],
        "default_grouping": "(database column name)",
        "calculated_cols": [],
        "hide_field": {
            "master_cols": [
                "Field_name1",
                "Field_name2"
            ],
            "detail_cols": [
                "Field_name1",
                "Field_name2"
            ],
            "insertable_cols": [
                "Field_name1",
                "Field_name2"
            ],
            "editable_cols": [
                "Field_name1",
                "Field_name2"
            ]
        },
        "modifier": {
            "master_cols": [],
            "detail_cols": [],
            "insertable_cols": [],
            "editable_cols": []
        },
        "master_cols": {
            "copy": "keys separated by . to copy configuration section in current resource eg modifier.insertable_cols",
            "Field_name": {
                "inline_input": {
                    "type": "lookup|checkmark|autocomplete|any valid html input (default=text)",
                    "source": "< key-value pairs list or query that returns key-value pairs (for lookup)> OR < table name >-< value column >-< label column > (for autocomplete)"
                },
                "type": "(default=|inferred| or text) (posible values=litnum|date|text|datetime|num|file|picture|timeago|to_words|checkmark|gpsinput)",
                "value": "",
                "filter_alias": "",
                "trim": "",
                "is_link": "",
                "link_id": "(default=id)",
                "tip": "",
                "resource": "(default={request._resource})",
                "before": "",
                "after": "",
                "preset_filters": "coma separated list or query that returns a list",
                "precission": "(default=2)",
                "major": "",
                "minor": ""
            }
        },
        "detail_cols": {
            "copy": "keys separated by . to copy configuration section in current resource eg modifier.insertable_cols",
            "Field_name": {
                "inline_input": {
                    "type": "lookup|checkmark|autocomplete|any valid html input",
                    "source": "< key-value pairs list or query that returns key-value pairs (for lookup)> OR < table name >-< value column >-< label column > (for autocomplete)"
                },
                "type": "(default=|inferred| or text) (posible values=date|text|datetime|num|file|timeago|to_words|checkmark|gpsinput)",
                "value": "",
                "filter_alias": "",
                "trim": "",
                "is_link": "",
                "link_id": "(default=id)",
                "tip": "",
                "resource": "(default={request._resource})",
                "before": "",
                "after": "",
                "precission": "(default=2)",
                "major": "",
                "minor": ""
            }
        },
        "insertable_cols": {
            "copy": "keys separated by . to copy configuration section in current resource eg modifier.insertable_cols",
            "Field_name": {
                "type": "(default=|inferred| or text) (posible values=backdate|date|text|datetime|number|file|checkmark|gpsinput|lookup|autocomplete|richtext|source_code|textblock|time|password|uri|autonumber|)",
                "value": "",
                "default": "",
                "not_user_editable": "",
                "constraint": "",
                "constraint_value": "",
                "constraint_message": "",
                "constraint_op": "",
                "length": "(default=underlying column length)",
                "description": "",
                "before": "",
                "after": "",
                "lang": "",
                "max_dimension": "",
                "source": "(default=current column)",
                "config (for autonumber)": {
                    "lead": "(default=current resource uri",
                    "count": "(default=current crud_table)",
                    "limit": "",
                    "skip": "(default=0)",
                    "length": "(default=8)"
                },
                "config (for autocomplete)": {
                    "strict": "1",
                    "table": "(default=current crud_table)",
                    "cols": "|value,description,limit| (default=current column)"
                }
            }
        },
        "editable_cols": {
            "copy": "keys separated by . to copy configuration section in current resource eg modifier.insertable_cols",
            "Field_name": {
                "type": "(default=|inferred| or text) (posible values=backdate|date|text|datetime|number|file|checkmark|gpsinput|lookup|autocomplete|richtext|source_code|textblock|time|password|uri|autonumber|)",
                "value": "",
                "default": "",
                "not_user_editable": "",
                "constraint": "",
                "constraint_value": "",
                "constraint_message": "",
                "constraint_op": "",
                "length": "(default=underlying column length)",
                "description": "",
                "before": "",
                "after": "",
                "lang": "",
                "max_dimension": "",
                "source": "(default=current column)",
                "config (for autonumber)": {
                    "lead": "(default=current resource uri",
                    "count": "(default=current crud_table)",
                    "limit": "",
                    "skip": "(default=0)",
                    "length": "(default=8)"
                },
                "config (for autocomplete)": {
                    "strict": "1",
                    "table": "(default=current crud_table)",
                    "cols": "|value,description,limit| (default=current column)"
                }
            }
        },
        "children": {
            "label": {
                "resource": "resource_uri",
                "foreign_key": "column_name",
                "child_key": "column_name (default=current foreign_key)"
            }
        },
        "charts": [],
        "detail_top_charts": {
            "label": {
                "link": "|resource uri|",
                "cstacked": "1",
                "data_conn": {
                    "host": "",
                    "user": "",
                    "pass": "",
                    "db": ""
                },
                "dataset_list": "",
                "data": "",
                "type": "",
                "stack": "",
                "link_id": "",
                "remove": "",
                "resource": ""
            }
        },
        "detail_bottom_charts": [],
        "actions": {
            "label": {
                "condition": "< string, column name in current data set OR SQL query >, < string or SQL query >, < operator (possible values=|=,>,<,!=,<=,>=|) (default='=') >",
                "data": [
                    "< list of strings, column names in current data set or SQL queries >"
                ]
            }
        },
        "inline_actions": {
            "label": {
                "condition": "< string, column name in current data set OR SQL query >, < string or SQL query >, < operator (possible values=|=,>,<,!=,<=,>=|) (default='=') >",
                "data": [
                    "< list of strings, column names in current data set or SQL queries >"
                ]
            }
        },
        "list_actions": {
            "label": {
                "condition": "< string OR SQL query >, < string or SQL query >, < operator (possible values=|=,>,<,!=,<=,>=|) (default='=') >",
                "data": [
                    "< list of strings or SQL queries >"
                ]
            }
        },
        "automations": {
            "on_master_view": [
                {
                    "action": "run_function|run_query|create_log|send_sms|send_email",
                    "function": "",
                    "query": "",
                    "log": "",
                    "message": "",
                    "subject": "",
                    "recipient": ""
                }
            ],
            "on_insert": [],
            "on_update": [],
            "on_details_view": []
        }
    }
}

import (@resource config)

Copy over another resource's configration into the current resource. The imported configuration can then be overriden with locally defined properties.

Possible values

Any valid resource uri other than the current resource.

if_is_child (@resource config)

Override the resource configuration if the resource is requested as a child of another resource. Such as when a child tab of a resource is clicked in the Sphere ERP web UI. This configuration could be used to hide certain elements or provide some default values to input fields when requested as a child.

Possible values

All properties and nested properties of a resource configuration except 'if_is_child' and 'import'

config level (@resource config | required)

This property key is the level to which a configuration applies. Is used to provide different configurations to different users depending on their user names, user group names, staff positions or resource access levels.

Possible key values (ordered by priority)

A user name, a user group name, a staff position string, or any string/integer used in the 'resource_access_levels' configuration of a user or user group. If no matching configuration is found in the order of priorities, the configuration labelled as '0' will be applied.

crud_table (@resource config > config level | required)

If the resource allows adding records; and or; editing records; and or; deleting records then the database INSERT, UPDATE and DELETE operations will be performed against this table. Reads and default input or display field configurations will also performed or inferred against this table by default except when a different table is defined in the particular read operation such as when using master_sums; and or; list_query; and or; list_sum and or; detail_query

Possible values

Any valid table name in the underlying schema to which the resource is connected.