The data that is displayed in the Order Grid is based on the view v_order_master_view, which is rebuilt every time changes are made to the tables order_view_tables or order_view_columns. If you want to add custom fields to the ordergrid or make any changes on the way the data is displayed you should make changes to these tables.
The data that is displayed in the Order Grid is based on the view v_order_master_view, which is rebuilt every time changes are made to the tables order_view_tables or order_view_columns. If you want to add custom fields to the ordergrid or make any changes on the way the data is displayed you should make changes to these tables.
When you add custom data to these tables it is a good practice to start with the id=1000, then we add new columns to standard your custom data won’t be overwritten during updates
order_view_tablesThis control table isused to construct the view v_order_master_view automatically so we need information in here on how to join the new table to some other table that is already listed.
id (int) - required: The id of the table, not an identity columntable_name (nvarchar) - required: The name of the table in the databasejoin_column (nvarchar) - required: the column in the new table you want to join onjoin_table_id (int) - required: the id (in this table) of the table/view you want to join on. join_table_id < id, we must refer to a table already listedjoin_table_column (nvarchar) - required: the name of the column in the join tableorder_view_columnsThis control table lists up columns that should be available in the order grid, either in the grid or in the info tab
id (int) - required: The id of the table, not an identity columnorder_view_table_id (int) - required: refers to the id column in the order_view_tables tablecolumn_name_in_table (nvarchar) - required: The name of the column in the SQL table that stores the datacolumn_name (nvarchar) - required: The name of the column in the auto generated view. Can’t always be the same as column_name_in_table since it needs to be uniquecaption (nvarchar) - required: The name that is shown in the system. You can either hard code the caption or enter a translation key that references the translations in the systemdescription (nvarchar) - not required: description of the purpose of the columninfo_tab_group_id (int) - required: Manages how the columns are grouped together in the info tab. The groups are listed in the table order_view_info_tab_groupseditable (bit) - required: 1, if the column should be editable; 0, otherwise. if 1 you also need to modify the procedure orders_update_values_customavailable_in_grid (bit) - required: 1, if the column should be available for the user in the grid; 0, otherwise. We try to minimize the number of available columns, if we have all the columns available in the grid it can affect performanceavailable_in_info_tab (bit) - required: 1, if the column should be available for the user in the info grid; 0, otherwisefilterable (bit) - required: This should only be 1 for the column order_id. Can not be customized after version 5.1.2serchable (bit) - required: This controls which columns are being searched by in the search box. If you want to add a searchable field you also need to customise the procedure filter_select_customization, you need to consider performance if you are adding multiple columns as serchable fields.calculated (bit) - required: 1, then the column is calculated based on other columns; 0, otherwise. If 1 you need to put a formula in the equation column and the order_view_table_id should be NULL.equation (nvarchar) - not required: Formulas you can put in a select statement you can put in here (case, nullif, division, multiplication…). Name of columns need to be inside brackets. Example: [unit_qty_chg]*[unit_weight]column_format (nvarchar) - required: How the column should be formatted. For more information and available options, see Settings -> System -> Format Tester in the AGR app.column_aggregation (nvarchar) - required: lists the possible aggregation options with a comma in between. The first one listed is the default aggregation type. Possible values: sum, count, min, max, avg, distinctdescription (nvarchar) - not required: description of the purpose of the columncaption (nvarchar) - required: The name that is shown in the system. You can either hard code the caption or enter a translation key that references the translations in the systemdescription (nvarchar) - not required: description of the purpose of the columnorder_view_agg_columnsThe info in this table refers to the aggregated info at the top of the info tab
id (int) - required: The id of the table, not an identity columnorder_view_column_id (int) - required: refers to the id of the column in the order_view_columns tableorder_view_column_name (nvarchar) - required: refers to the name of the column in the order_view_columns tablecolumn_name (nvarchar) - required: The name of the column. Must be unique.caption (nvarchar) - required: The name that is shown in the system. You can either hard code the caption or enter a translation key that references the translations in the systemdescription (nvarchar) - not required: description of the purpose of the columninfo_tab_group_id (int) - required: the groups are listed in the table order_view_info_tab_groups. The aggregation columns are in group 3 by defaultaggregation (nvarchar) - required: the type of aggregation. possible values: sum, count, min, max, avgvisible (int) - required: 1 if the column should be visible in the system; 0 otherwise.column_format (nvarchar) - required: How the column should be formatted. For more information and available options, see Settings -> System -> Format Tester in the AGR app.Here is an example of how to add a column to the Order Grid. We want sale information to be available in the Order Grid so we decide to use the report column sale_qty_last_12_months.
order_view_tablesINSERT INTO dbo.order_view_tables
([id],[table_name],[join_column],[join_table_id],[join_table_column])
VALUES (1000,'mbe_item_period_calcs','item_id',7,'id')
INSERT INTO [dbo].[order_view_columns]
([id],[order_view_table_id],[column_name_in_table],[column_name],[caption],[description],[info_tab_group_id],[editable],[available_in_grid],[available_in_info_tab],[filterable],[searchable],[calculated],[equation],[column_format],[column_aggregation])
VALUES (1000,1000,'sale_qty_last_12_months','sale_qty_last_12_months','Sale Qty Last 12 Months',null,2,0,1,1,0,0,0,null,'decimal:0','SUM,MIN,MAX,AVG')
In the standard setup you can choose to constrain orders (link to user manual constraining orders?) by Pallets, Qty etc. Sometimes the customer wants to constrain by another property, for example Value in other currency or something else. The custom constraining only supports unit based constraining - no customisation to the days constraining is available.
Step 1
Add constraint to the table order_constraint_elements. You can have up to 5 custom constraints and name would be custom_constraints_1, ….. , custom_constraints_5
order_constraint_elementsid (int): Automatically generated idtable_name (nvarchar): Name of the source tablecolumn_name (nvarchar): Name of the column in the source tableitem_join_column (nvarchar):created_at (datetime) - requiredupdated_at (datetime) - requireddescription (nvarchar): Description of the mbe columnname (nvarchar)caption (nvarchar): The name that is shown in the system. You can either hard code the caption or enter a translation key that references the translations in the systemExampleINSERT INTO dbo.order_constraint_elements ([id], [table_name], [column_name], [item_join_column], [created_at], [updated_at], [name], [caption]) VALUES (7,'mbe_item_custom','custom_constraints_1','item_id',getdate(),getdate(),'custom_constraints_1',
'[CONSTRAINT NAME]')
Step 2
The data for custom constraint need to be inserted into mbe_item_custom. The data for custom_constraints_1 would then go to columns item_id and custom_ constraints_1.
In the dbo.orders table in the agr database there is a column called status. That column can have 4 different values within it, and each represents a different status of the order it applies to. The following list contains the different statuses:
When Order Proposals are ready in AGR they are confirmed and ready to be sent to the ERP for final processing. The view v_orders_to_transfer in the AGR production database is populated with all orderlines waiting to be transferred to the ERP.
v_orders_to_transfer order_id (int) - required: The AGR generated id for the order proposalitem_no (nvarchar) - required: The item’s numberlocation_no (nvarchar) - required: Location number, either warehouse or storeorder_from_location_no (nvarchar) - required: The location where the item is being ordered from; whether an order is coming from own warehouse/store (location_no) or an external supplier (vendor_no)size (nvarchar) - not required: Size of the itemcolor (nvarchar) - not required: Color of the itemstyle (nvarchar) - not required: Style of the itemuser_id (int) - not required: The AGR user that created the order proposalunit_qty_chg (decimal) - required: The quanity AGR is proposing to orderest_delivery_date (date) - required: The date AGR expect to receive the stockorder_type (varchar) - required: PURCHASE for purchase orders and TRANSFER for transfer ordersAfter the order has been imported to the ERP the connector needs to update the status of the order proposal in the AGR database (mark it as transferred).
UPDATE orders SET status = 2 WHERE id = @order_id
AGR recommends that a job is setup within the ERP system that runs frequently during business hours (example every 15 minutes) that looks in the v_orders_to_transfer and retrieves all confirmed orders periodically and updates the status of the retrieved orders.