Falcon Space. Working with tables

Component description Table

View an example of the table component

The table component allows you to display some list data as a table and manipulate data in the table (add, edit, delete, group operations, etc.).

Do not confuse the database table and the table component. They are not related in any way. When creating columns for the table component, no changes occur in the database structure (DB tables or DB table columns are not created). 

To create a table, you need: 

  1. On the page, print a snippet with the table and code (for example, code). 
    <div class="as-table" data-code="{code}"></div>
  2. Create a table with the code code in the table section. 
  3. Edit table parameters (these parameters repeat the parameters of the as.crud2 component).
  4. Create all the necessary columns (the column parameters are very similar to the column parameters of the table as.crud2).
  5. Implement all necessary stored procedures (naming-crud_{code}_functionName): 
    1. GetItems-filtering and retrieving table data
    2. EditField-editing a field in a table row
    3. DeleteItem-deleting a data string
    4. FastCreate-create a table row based on a single field (e.g. Name or Code). 
    5. methods for getting reference lists. 

Table parameters







Note:
The API Source parameter is the comma-separated codes of outgoing API requests that are executed before GetItems and responses are passed through the
@filters variable (necessarily of the ExtendedDictionaryParameter type). In Key , the name of the outgoing API code. In Value2-response from an external source (in JSON or XML).
Learn more about the universal API

Example of an external source for the table output API.

Parameters of table columns




Note

  • IMPORTANT. Columns must be named in lowerCamelCase, for example parentName (not PARENT_NAME, ParentName).
  • Instead of storing a list of column values, you can specify JSON to output a static list 
    (for example, for Boolean elements, you can specify {1: Yes;} ) 
  • the Table page has a button Demo for tables. It is available for those tables that are not linked to the parent tables 
    (otherwise, you will need an additional parameter in the URL for the table to work correctly).
  • The table must have a column with the id code and  PK=Yes (Visible=None).
  • IMPORTANT. If the column is not specified in the GetItems output , it will not be displayed on the page and there will be no error. 
    In this way, you can vary the table columns depending on the user and other parameters. 
  • The id parameter with PK=true should ALWAYS come first. If possible, do not use ord< 0.


Configuring stored procedures for a table

The main stored procedures getItems, updateField, deleteItem, fastCreate are done via the control panel.
It is extremely important to follow the General style and type of template procedures (they have the crud_example_ prefix).

 

GetItems

Issues the following SELECT statement:

  1. Data table
  2. Number of lines
  3. Changed table parameters (title header, subtitle hint, toolbarAdditional panel, etc. See below)
  4. Output of columns in the table's basement (usually aggregating data from the #result table). 
    Make sure to name the columns in this query and specify them in the same order as your columns in the output.
    IMPORTANT! If it is viewType= "gantt"(Gantt), 'chartbar' or "canban"; - that is, the query is data in time by elements. 
  5. Preset filter settings (i.e. named links that set a certain type of filter with one click, such as Frozen leads). 
CREATE PROCEDURE [dbo].[crud_newTableTest_getItems]
	@filters CRUDFilterParameter READONLY, -- We never change the parameters
	@sort sql_variant,
	@direction nvarchar(8),
	@page int,
	@pageSize int,
	@username nvarchar(32)
AS
BEGIN
-- basic table setup procedure

/*the resulting table (we describe the fields here (as in the table columns))
special fields for it: icon_age, prev_age, prevcolor_age, color_age,
backcolor_age, desc_name, color, barPercent_age,
barClass_age, badge_age*/
declare @result TABLE (id int, name nvarchar(256), code nvarchar(64) )


/* extracting additional parameters from the URL (mainly used for filters or
if you need to get itemID, it is located by [key] = 'itemID')*/
declare @filterName nvarchar(128)
select @filterName = Value from @filters where [Key] = 'name'

insert into @result
select top 2 id, 'name', code
from as_trace
--in addition to the result set condition, we apply filters
where (isnull(@filterName,") = " or code like '%'+@filterName+'%')

-- 1 SELECT-the data itself
select * from @result
/*sort
IMPORTANT! If we sort by a field of the specified type the sorting will be performed
this type, for example, if we enable sorting by date, but in the field
if the nvarchar type is selected, then the sorting will be based on it. To and
to avoid this we create an additional field of the desired type only in the procedure
(don't output it), and insert it into the then expression */
order by
case when @sort = 'name' and @direction = 'down' then name end desc,
case when @sort = 'name' and @direction = 'up' then name end asc,
case when @sort = 'code' and @direction = 'down' then code end desc,
case when @sort = 'code' and @direction = 'up' then code end asc
OFFSET @PageSize * (@Page - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;

-- 2 SELECT-number in the table
select count(*) from @result

-- 3 SELECT Advanced table settings
/*Select " Title,
"ToolbarAdditional,
"GroupOperationsToolbar,
"FastCreateLinkText," FastCreateDialogHeader,
'' FastCreateDialogPlaceholder,
0 HideTitleCount,
0 DisableCellTitle,
'10px' FontSize,
'{filterCode}' FilterMakeup,
1 InstantFilter,
*/
--'gantt' ViewType,
-- GanttScale, GanttNavigate, GanttItemForm, GanttItemFormTitle
-- KanbanItemForm, KanbanItemFormTitle


-- 4 SELECT Data for page footer or data for Gantt/Kanban (if ViewType is set to 3 SELECT)

-- 5 SELECT Data for preset filters.
/*
select * from (
select 'Clients' Title, 'Current clients' Tooltip, 'clients' code, 'success' type, 'status=53.54' Filters, 1 Ord
union
select 'network leads' Title, 'Potential clients' Tooltip, 'leadsNetwork' code, 'info' type, 'Filter=1,2,3||Filter2=1,2,3||status=53||keepConnect=1' Filters, 2 Ord
union
select ' Unborn leads'Title, 'Those on a long pause' Tooltip, 'frozen' code, 'primary' type, 'status=53,54' Filters, 3 Ord
) t1 order by t1.ord

*/

END
Nuances for sorting and filtering in GetItems
  1. @filter is passed as input to the stored procedure-it contains all URL parameters and as-crud filter parameters. 
    Extracting them in the above way and using them in the first select query.
    This collection also includes all values passed as a data parameter to the as-table snippet markup
Example of markup:
<div class="as-table" data-code="dashboardPanels" data-elementid="566" data-name="name1"></div>

Example of calling a parameter:
 declare @filterElementID int
select @filterElementID = try_cast(Value as int) from @filters where [Key] = 'elementID'
 2. Sorting in GetItems
select * from @result --parameters in the settings column of the table: Sort-YES
order by
--each parameter is specified twice, in forward and reverse order
--if you use HTML markup, the sorting should be done in a clean way
--value (hide_title)
case when @sort = 'name' and @direction = 'down' then name end desc,
case when @sort = 'name' and @direction = 'up' then name end asc,
case when @sort = 'code' and @direction = 'down' then code end desc,
case when @sort = 'code' and @direction = 'up' then code end asc
OFFSET @PageSize * (@Page - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;

Note:

  • In GetItems - if a column has null in 1 row, the column is not output (i.e. you can dynamically hide columns by assigning null to the entire column).
    Be sure to use isnull(field,") field so that the column is always explicitly output. If the column is not displayed in the table, first check that you do not have null in this column.   
  • You can use@filters ExtendedDictionaryParameter (Key, Value2) in the GetItems procedure instead of@filters CRUDFilterParameter (Key,Value). This is necessary when data comes from an external source via the API (and response data is passed to the collection from an external source).  Learn more about the universal API
Additional settings for displaying cells in the table

You can also specify additional columns with special prefixes to improve the output of cells: 
  • icon_age nvarchar(256) - adds an icon to the age cell (e.g. 'fa fa-upload')
  • prev_age float-indicates a percentage increase over the previous indicator (for the age column). 
  • prevcolor_age  nvarchar(256) - column increment color (relevant when there is a prev_ parameter. Example: #aaa)
  • color_age nvarchar(30) - color of the cell text in the age column. Affects only the text, but not the links in the cell (see internal output markup). 
  • backcolor_age nvarchar(30) - cell color age
  • desc_name - additional description under the age cell value
  • color-colors the background of the string to the specified color (for example,“#aabbcc”)
  • rowLink-sets a link for the entire table row
  • barPercent_age, barClass_age-setting the display of the progress bar percentage from 0 to 100 and the color of the progress bar (success, danger, warning, info, primary)
  • badge-colors the specified column in the badge (icon-panel) element (e.g. badge_title =‘success’ makes a green badge for the title field). 
    Standard bootstrap 4 names are used (light, secondary, warning, etc.).
  • sub_age-specify substring markup. +/- Is added to the cell to expand the row (the specified markup is shown in it).
    Here you can also set the markup of components (table, form, etc.). 
  • modal_age-specify the markup to output in the modal window. A button-icon for displaying markup in the modal window is added to the cell. 
    here you can also set the markup of components (table, form, etc.). 
    You can set the header for the modal window using the pseudo column
    modalTitle_age
  • colTitle_code1-for the code1 column, the column will be renamed to the specified value
Note: 
For pseudo fields (desc_colName), you do not need to create real fields in the table.
They are simply written in the output SELECT 1 query in GetItems.

Example
: we need to color the cells for the title column (title is the code of an existing column in the table).
  1. in GetItems, in @result (or #result), declare the backcolor_title nvarchar column (128)
  2. in select, add the output of this field:‘#f00’ backcolor_title
  3. We check that the cell background is set correctly on the page. 
 


Additional table settings in GetItems SELECT 3
  1. Title-table title
  2. ToolbarAdditional-layout of the button panel for the table
  3. GroupOperationsToolbar-layout of the button panel for group operations
  4. EmptyText-text that is displayed when nothing is found
  5. FastCreateLinkText-link text Create….
  6. FastCreateDialogHeader-title of the Create dialog box
  7. FastCreateDialogPlaceholder-hint when creating in the Create dialog box
  8. HideTitleCount - hide the display of the number of lines in the title.
  9. FontSize-specify the font size for the table (e.g. 10px, 0.85 em)
  10. FilterMakeup - custom filter markup. Filter parameters (columns with the specified filter type) are passed to it via {colName}. 
  11. InstantFilter - if 1, when changing the filter values, the filter is immediately applied to the data. 
  12. Compact - if 1, the table will be compressed with reduced margins.
  13. ZoomCells - if 1, the cells will increase when you hover (this is true when the font size is small and there is a lot of data in the page). 
  14. EnableExcelExport, EnablePrint - if 1, the buttons for uploading the table to Excel or sending it to print appear
  15. EditableMode-sets the type of element editing. inline-inside the table. popup-editing as a small popup window. 

  16. DisableFrozenHeader - disable the ability for a table to display the table header at the top when scrolling 
    (relevant when there are several tables on the page or there are nested tables). 
  17. DisableCellTitle-disable cell suggestions (duplicate the column name and its hint).
  18. ViewType - data output type (table output by default. Options - kanban, gantt, map, timeline, card, chartbar, chart, progress, calendar).
  19. GanttScale, GanttNavigate, GanttItemForm, GanttItemFormTitle-Gantt chart output settings (see the separate Gantt section in the tables).
  20. KanbanItemForm, KanbanItemFormTitle - settings for Kanban panels (see the separate section about Kanban in the tables). 
  21. MapShowUserLocation, MapUserImageUrl, MapShowAllRegions, MapShowLocationInRange-map output settings.
    (see the separate section about using maps in tables). 
  22. CardGroupType-card output type: deck (in 1 line of the card), columns (split by columns), 
    group (all cards are in 1 line and look like a single panel).. In most cases, columns will do.
  23. The parameters of the graphs (for ViewType=chart).ChartType, ChartTitle, ChartWidth, ChartHeight, ChartLineCommaLabels - chart output settings (see the separate section about chart settings).
  24. FilterMakeup-HTML markup for the filter. Column filters are specified here via {colCode} 
    (the column must have the filter data type set).  
  25. Collapse 0,1,2. Used for collapsing the table by name. 0 - no collapse. 1 - there is a collapse, and it is shown in open format when loading. 2-there is a collapse, when loading collapsed.
  26. HeaderTag - h1-h6 - specifies which tag to use for the header (by default, h1). 

UpdateField  

SELECT 1. Returns the Msg, Result, RefreshContainer model (the area on the page to update). If Result = '1', the operation was completed successfully.
SELECT 2. The second query returns Calling external actions.
SELECT 3 sets other fields to update. Key - the field code, Value - the new value for it. To change fields in the table's basement, footer-{code}

CREATE PROCEDURE [dbo].[crud_newTableTest_updateField]
	@itemID int, -- don't change parameters
	@field nvarchar(64),
	@value nvarchar(max),
	@username nvarchar(64)
AS
BEGIN
-- update the fields of the table
-- for casting types, use try_cast(@value as int), for date try_convert(date, @value, 104)

if (@field = 'XXX') begin -- check the code for the field and change it
	update as_trace set code = @value where id = @itemID
end else if(@field = 'YYY') begin
	update as_trace set code = @value where id = @itemID
end else begin
	select 'Invalid property code' Msg, 0 Result
	return
end

-- SELECT 1 Output the result
select '' Msg, 1 Result, '' RefreshContainer
                        --  if you need to pass an error, specify:
                        -- 'Error text' Msg, 0 Result

-- SELECT 2 External action (see the forms about External action)
select 1

-- SELECT 3 Columns to update (when you need to change other columns or table footer)

Select 'total' [Key], '' Value
union
Select 'footer-total' [Key], '' Value


END

DeleteItem

Вreturns the model Msg, Result

CREATE PROCEDURE [dbo].[crud_newTableTest_deleteItem]
	@itemID int,
	@username nvarchar(32)
AS
	-- deleting a table row
	SET NOCOUNT off ;


	 -- checks before deletion
	 declare @count int
     select @count = count(*) from as_en_entityInstanceFields
     where fieldID = @itemID

     if(@count>0) begin
		select 'Links are available on the field in instances ('+cast(@count as nvarchar)+' шт.)' Msg, 0 Result
        return
     end

	-- delete of element
	delete from as_trace where id = @itemID

	if (@@ROWCOUNT> 0) begin
		select '' Msg, 1 Result
	end else begin
		select 'The record was not deleted from the database' Msg, 0 Result
	end


Note:
If the stored procedure returns the result {Result: false, Msg: 'Error'},
the field will not be updated and an editing error will be output for the user. 

FastCreate

Returns the result model Msg,Result,RedirectUrl

CREATE PROCEDURE [dbo].[crud_newTableTest_fastCreate]
	@filters CRUDFilterParameter READONLY,
	@text nvarchar(256),
	@username nvarchar(32)
AS
BEGIN
	-- creating an entity in a table by 1 field @text
	-- retrieving parameters from a URL
	declare @filterItemID int
	select @filterItemID = try_cast(Value as int) from @filters where [Key] = 'itemID'


	-- checks
	declare @id int
	select @id = id from as_trace where code = @text
	if(@id>0) begin
		select 'An element with this code already exists in the table' Msg, 0 Result
		return
	end


	-- adding an item to a table
	insert into as_trace(header, code)
	values(@text, @text )
	select 'The element is created' Msg, 1 Result

END

If you wish to quickly add a selection of data from the directory (through search in the autocomplete text field instead)
that must be set to SELECT 3 FastCreateSearch GetItems parameter = 1 and implement additional procedure to the following: 


-- the name contains the _search prefix
CREATE PROCEDURE [dbo].[crud_productsForSupplier_fastCreate_search]
/*At the input, the search string username, itemID is left for
                        compatibility and unused*/
   @q nvarchar(256),
   @itemID nvarchar(128),
   @username nvarchar(256)
AS
BEGIN
	CREATE TABLE dbo.#result (id int, [text] nvarchar(256), [desc] nvarchar(1024), [type] nvarchar(256), [date] date)

	insert into #result
	select id, name, '', '', getdate()
	  from as_cat_products p

	where name like '%'+@q+'%' and id>2
	-- on exi - model id, text, desc, type, date
	select * from #result
	drop table #result
END
go

The FastCreate procedure eventually returns the numeric foreign key of the selected entity, and its text name. 
If you need Textarea (multi-line field) in FastCreate, then put the FastCreateTextarea 1 parameter in GetItems in SELECT 3

Note: 
  • If the output parameter RedirectUrl is non-empty, then after saving it successfully, it will redirect to the specified page. 
  • The itemID is passed to the search procedure . it is filled in from the data-itemID attribute from the table snippet. 


References for columns with select (for filtering or editing)

The procedure name is set automatically as crud_{tableCode}_{colCode}_dict and the procedure is edited via the column Management dashboard. 

CREATE procedure [dbo].[crud_newTableTest_id_dict]
	@tableCode nvarchar(32),
	@col nvarchar(32),
	@username nvarchar(32)
as
begin
	-- data source for list of values (Value, Text)
	select 0 Value, 'Не выбрано' Text
	union
	select top 5 id Value, code Text
	from as_trace


	/*
	for bool types:
	select 0 Value, 'Нет' Text
    union
    select 1 Value, 'Да' Text

	*/
end
Itemid and Col are passed in case the universal procedure is used. The output model is Value, Text, and Color.

Note. 
  1. An optional parameter can be passed to the dict procedure @parameters ExtendedDictionaryParameter in which additional parameters are passed (langID, falconGuid, and itemID, passed to the data-itemID on the table component).
  2. Previously, the field was used to store the procedure name. It remained working for compatibility. 
    This field is not output now, and you need to use new editing procedures via the dashboard instead. 
  3. If you need an element Not selected, you can do  union select 0 Value,’Tex
  4. If Color is set, the element (check Mark, List, radio Button) will have a background (CSS background-color) with the specified color

Filter types for table column
  • The integer int
  • The fractional number float
  • The line string
  • The list select
  • The mark bool
  • The date date
  • The range of dates daterange
  • The flags checkboxes
  • The range of numbers intrange
  • The choice radio radio
  • The choice chosen chosen
  • The choise multiple chosen chosenMultiple
  • Switch  switch ( IMPORTANT. Do not use with a large PageSize(>30), because initializing multiple switches makes loading delay).
If a filter is installed near the column, it will be displayed at the top as a filter. 

If you need to place filters differently , you can use the FilterMakeup parameter in GetItems SELECT 3
and insert filters there via {colCode} (colCode-code of the column that has the filter type set).


Filtering itself occurs in the GetItems SELECT 1 query. the Filter comes in the @filters collection. You can extract it like this: 
declare @filterRole nvarchar(128)
select @filterRole = Value from @filters where [Key] = 'role'

Inline editing in the table

You can edit the following data types.
The line
text
The multiline text
textarea
The list
select
The date
date
Date and time
datetime
The mark
checklist
The switch
switch

The field is saved directly in the UpdateField procedure. 

Customization for FastCreate

Use variables in your GetItems query to rename the main placemarks: 

  • fastCreateLinkText - text of the Create button
  • fastCreateDialogHeader-dialog title
  • fastCreateDialogPlaceholder-hint text.  

Operations in the table (tableOperations)

You can make some custom operations in the table (using your own stored procedures). There are 3 types of operations: 

  • group-button in the toolbar for group operations
  • string operation-button at the string
  • toolbar button


Operation settings in the table

To add an operation to a table, create an operation in the table operations section (link Operations in table rows in the section of the Table). 

For the operation, specify: 

  1. code - used for building stored procedure names. 
  2. name-output in the table
  3.  icon-Font Awesome icon (you can find the appropriate icon in the Static menu).
  4. confirmation text - if set, confirmation of the operation will be requested before performing the operation
  5. message-message about the operation performed on success
  6. Hide the string after performing the operation - if YES, then hide the string for which the operation was called 
    (only valid for line operations). 
  7. Restarting the page after the operation is completed - if YES, then restarting the entire page on successful completion. 
  8. Users, Roles - who has access to perform these operations. 
  9. Type-operation can be: 
    1. for a string (output then in a string),
    2. just in the toolbar (button at the top of the table)
    3. for group operations (appears at the top of the table when one or more rows are ticked - 
      to do this, set the Show ticks for group operations setting in the table).
After configuring operations, we proceed to creating stored procedures. 
Stored procedures for table operations
button Actions are implemented via stored procedures of the following type

crud_{tableEntity}_{operationCode}_operation.

the input procedure is passed itemIDs nvarchar(2048), which contains the ID of the selected row, or the selected rows for a group operation,
or all the rows of the page (if it is just a button for toolbar).
--the name is constructed as crud_{table}_{operationCode}_operation
CREATE PROCEDURE [dbo].[crud_newTableTest_newOperation_operation]
	@filters CRUDFilterParameter READONLY,
	@itemIDs nvarchar(256),
	@username nvarchar(32)
AS
BEGIN
	-- execution table operations
	-- if this is a group operation, then itemIDs contains the value ID by a comma

	--declare @filterFormID int -- to use variables passed implicitly (through URL)
	--select @filterFormID = try_cast(Value as int) from @filters where [Key] = 'formID'

	-- select value from dbo.split(@itemIDs, ',') iterating through the values of the selected elements
	declare @res bit = 0

	-- SELECT 1 msg, Result, Info
	if(@res=1) begin
		select 'An error occurred' Msg, 0 Result
	end else begin
		select '' Msg, 1 Result, '' Info, '' RedirectUrl
	end

	-- SELECT 2 A call to an external action
	/*
	select 'email' type,   -- email, sms, notification, clearcache
    	'ru@rudensoft.ru' [to], 'sub1' subject, 'bodyXXX'+@itemIDs body,   ---EMAIL
    	'Text 1' msg, '723429234' [number],   												--- SMS
        '' prefix,																				--CLEAR CACHE
		'text1' text , 'ru@rudensoft.ru' [to], 'ticketExecutor' typeCode, 'http///' url, 'add111' additional -- notification
	*/

END


On exit: 
SELECT 1 - Result, Msg, Info, RedirectUrl. The field Info - this information will be displayed in the dialog box after the operation.
SELECT 2 -  calls to external actions (mail, SMS, notification, etc.). See calling external actions.

Note:
If you plan to use forms to process selected lines:
The modal form button (as-form-modal) добавляется в groupOperationsToolbar для таблицы,
On data-itemID buttom automatically, when ticks are ticked, itemID lines fall after a comma, for example "123,456,789".
If a RedirectUrl is specified, the operation will go to the specified URL when successful.

Additional modes of the Table component