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.

{
    "conditionals": {
        "0": {
            "condition": "< string or SQL query that evaluates to 1 if true >",
            "config": "< JSON configs with |config_level| >"
        }
    },
    "import": "| resource uri |",
    "req_config": "| overiding resource config without levels |",
    "if_is_access_level": "| overiding resource config without |config_level| |",
    "if_is_child": "| overiding resource config with |config_level| |",
    "if_is_not_child": "| overiding resource config with |config_level| |",
    "if_is_child_of": {
        "| resource_uri|": "| overiding resource config with |config_level| |"
    },
    "| config_level |": {
        "prefilter_inputs": {
            "< input name >": {
                "type": "< all valid html input types|lookup|autocomplete|checkmark > (default=text)",
                "default": "< string or SQL query that returns a value >",
                "source": "< key-value pairs list or query that returns key-value pairs (for lookup)> OR < table name >-< value column >-< label column > (for autocomplete)"
            }
        },
        "hidden_views": "list of views to hide",
        "detail_window_title": "string or SQL statement can use {this_row.< column name >}",
        "static_detail_limit": "",
        "detail_query_columns": "list of columns to select in select query for detail view (default=*)",
        "list_query_columns": "list of columns to select in select query for master view (default=*)",
        "crud_table": "|database table name|",
        "list_limit": "(default=50)",
        "d_title": "(string or SQL query)",
        "list_groupings": "(string)",
        "debug": "1",
        "static_list_limit": "SQL phrase",
        "static_list_limit_having": "SQL phrase",
        "list_autosums": {
            "no_conflict": "< optional, possible values=1 >",
            "label": {
                "value": "column in record set",
                "type": "sum|max|min|avg (default=sum)"
            }
        },
        "singular": "(string or SQL query)",
        "render_type": "(default=plain_table)",
        "edit_constraint": "(WHERE clause of SQL query)",
        "delete_constraint": "(WHERE clause of SQL query)",
        "delete_constraint_message": "string",
        "default_grouping": "|database column name|",
        "landing_view_label": "string",
        "deletable": "(1 or empty)",
        "capitalize_input": "(1 or empty)",
        "true_dates": "(1 or empty)",
        "custom_actions": "(coma separated strings)",
        "phone_col": "list_query result set column name)",
        "email_col": "list_query result set column name)",
        "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})",
        "views": "| overiding resource config without |config_level| (can use 'condition' declaration string - refer to 'actions > label > condition') - can use: 'is_default' can use 'autoviews' OR 'enum_views'=column in crud table",
        "table_joins": {
            "table name": {
                "crud_table": "table on left side of join (default=config > crud_table)",
                "crud_key": "column from current crud_table (current crud table is on the left side of the join)",
                "link_key": "columns from table to join",
                "mode": "LEFT | RIGHT | INNER | CROSS (default=LEFT)",
                "operator": "(default='=')",
                "alias": "string (optional)"
            }
        },
        "master_cols": {
            "copy": "keys separated by . to copy configuration section in current resource eg modifier.insertable_cols. Is applicable only inside a |modifier| object.",
            "Field_name": {
                "true_date": "posible values=1 (indicates the values in this field are store as dates in underlying schema for faster filter operations by taking advantage of indexes)",
                "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|autocomplete|linkurl)",
                "value": {
                    "( possible values ": {
                        " keys here are for case of array)": null
                    },
                    "value": "string OR SQL phrase"
                },
                "filter_alias": "",
                "trim": "",
                "is_link": "",
                "link_id": "(default=id)",
                "link_type": "(chart|resource default=resource)",
                "link_col": "(name of column from linked resource crud table | optional)",
                "dynamic_resource": "column in current dataset, query that returns a string or string",
                "tip": "",
                "resource": "(default={request._resource})",
                "before": "",
                "after": "",
                "preset_filters": "coma separated list or query that returns a list",
                "precission": "(default=2)",
                "major": "",
                "minor": "",
                "config (for autocomplete)": {
                    "crud_table": "table on left side of join (default=config > crud_table)",
                    "table": "(default=current crud_table)",
                    "full_render": "< optional, possible values=1 >",
                    "cols": "|value,description| (default=current column)"
                }
            }
        },
        "detail_cols": {
            "copy": "keys separated by . to copy configuration section in current resource eg modifier.insertable_cols. Is applicable only inside a |modifier| object.",
            "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|linkurl)",
                "value": "",
                "filter_alias": "",
                "trim": "",
                "is_link": "",
                "link_id": "(default=id)",
                "link_type": "(chart|resource default=resource)",
                "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|)",
                "sticky": "1 (all posted records in form will share default value or autonumber)",
                "value": "",
                "default": "",
                "not_user_editable": "",
                "constraint": "",
                "constraint_value": "",
                "constraint_message": "",
                "constraint_op": "(ORDER OF COMPARISON=|constraint_value | constraint_op | constraint|)",
                "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)",
                "condition | optional": {
                    "0": "data field in current resource data or SQL query",
                    "1": "data field in current resource data or SQL query",
                    "2": "operator"
                }
            }
        },
        "detail_top_charts": {
            "label": {
                "label": {
                    "link": "|resource uri|",
                    "cstacked": "1",
                    "data_conn": {
                        "host": "",
                        "user": "",
                        "pass": "",
                        "db": ""
                    },
                    "dataset_list": "",
                    "data": "",
                    "type": "",
                    "stack": "",
                    "link_id": "",
                    "remove": "",
                    "resource": ""
                }
            }
        },
        "actions": {
            "label": {
                "condition": "< string, column name in current data set OR SQL query >, < string or SQL query >, < operator (possible values=|=,>,<,!=,<=,>=|) (default='=') >",
                "data": {
                    "0": "< list of strings, column names in current data set or SQL queries > | list of arrays with key at position 0 and value at position 1 can use {this_row.< column name >}"
                },
                "prompt_confirm": ""
            }
        },
        "context_actions": {
            "label": {
                "condition": {
                    "0": "string, column name in current data set OR SQL query",
                    "1": "string or SQL query",
                    "2": "operator (possible values=|=,>,<,!=,<=,>=|) (default='=')"
                },
                "data": {
                    "0": "< list of strings, column names in current data set or SQL queries > | list of arrays with key at position 0 and value at position 1"
                }
            }
        },
        "select_list_actions": {
            "label": {
                "condition": {
                    "0": "string, column name in current data set OR SQL query",
                    "1": "string or SQL query",
                    "2": "operator (possible values=|=,>,<,!=,<=,>=|) (default='=')"
                },
                "data": {
                    "0": "< list of strings, column names in current data set or SQL queries > | list of arrays with key at position 0 and value at position 1"
                }
            }
        },
        "inline_actions": {
            "label": {
                "condition": {
                    "0": "string, column name in current data set OR SQL query",
                    "1": "string or SQL query",
                    "2": "operator (possible values=|=,>,<,!=,<=,>=|) (default='=')"
                },
                "data": {
                    "0": "< list of strings, column names in current data set or SQL queries > | list of arrays with key at position 0 and value at position 1"
                }
            }
        },
        "list_actions": {
            "label": {
                "condition": "< string OR SQL query >, < string or SQL query >, < operator (possible values=|=,>,<,!=,<=,>=|) (default='=') >",
                "data": {
                    "0": "< list of strings or SQL queries > | list of arrays with key at position 0 and value at position 1"
                }
            }
        },
        "automations": {
            "on_master_view": {
                "0": {
                    "action": "run_function|run_query|create_log|send_sms|send_email",
                    "function": "",
                    "query": "",
                    "log": "",
                    "message": "",
                    "subject": "",
                    "recipient": ""
                }
            }
        },
        "collapsed": {
            "master_cols": "(list of columns)",
            "detail_cols": "(list of columns)"
        },
        "local_variables": {
            "|variable name|": "variable value (string or SQL query that returns a variable)"
        },
        "hide_field": {
            "master_cols": {
                "0": "Field_name1",
                "1": "Field_name2"
            },
            "detail_cols": {
                "0": "Field_name1",
                "1": "Field_name2"
            },
            "insertable_cols": {
                "0": "Field_name1",
                "1": "Field_name2"
            },
            "editable_cols": {
                "0": "Field_name1",
                "1": "Field_name2"
            }
        },
        "data_conn": {
            "host": "",
            "user": "",
            "pass": "",
            "db": ""
        },
        "master_options": {
            "0": "add",
            "1": "sms",
            "2": "email"
        },
        "order_field": {
            "0": "column (default=id)",
            "1": "direction (default=ASC)"
        }
    },
    "detail_bottom_charts": [],
    "charts": [],
    "user_inputs": [],
    "run_options": [],
    "on_insert": [],
    "on_update": [],
    "on_details_view": [],
    "on_delete": [],
    "master_cols": [],
    "detail_cols": [],
    "insertable_cols": [],
    "editable_cols": [],
    "list_sum": [],
    "calculated_cols": []
}

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.