⟵ hearthere ⟶
  • Workshops
  • External connections
  • Tableau и PowerBI
  • Knowledge base
  • Reference to the data of a temporary table
  • Loss of connection to the database during high data traffic
  • Save file in the field by external url link
  • Generating a list of rows marked with checkboxes and sending it to print
  • Adding a loop to the cycle table using a button from the temporary table
  • The TryCatch function and data transfer after a rollback
  • Access error when navigating to the cycle if the first table is unavailable to the user
  • The architecture of a system calculating balances for a cash register or warehouse
  • Sorting during selection based on the display of the Select field rather than its underlying value
  • Blocking the entry of duplicate values
  • Tabular display in the contextual panel
  • Sorting rows in a table using a button
  • How to retrieve a value based on a condition from a list of nested arrays?
  • Reset the ID counter in the table
  • Hiding a field in the table based on a condition
  • Action confirmation when clicking a button in a contextual window
  • Recalculation of a large table
  • How to sum values in `rowList` by one of the keys (one of the columns)
  • Field sets based on conditions when opening via `linkToTable`
  • How to delay the execution of an action and perform it in the background
  • Creating and deleting rows when selecting values in a Select or Select-tree
  • `selectRowListForSelect` with a condition where the current value is displayed as strikethrough
  • Not all external requests to Remotes are executed, causing the ID counter to break
  • Sorting Calculated Tables in a Cycle
  • How to pass data as numbers when making a POST request to a third-party API (must be an "integer")
  • How to create a chart when there are multiple parameters
  • Saving the initial values in a field when the field is calculated by code
  • Auto-filling the values of subsequent fields based on the selection in the first field
  • How to use a button in a temporary table to write data into a field in a calculated table within a cycle?
  • Display values from two fields of another table in a single Select field
  • #$ndts does not add seconds to a field of type Date
  • Calculator button in a field
  • Export CSV without technical data
  • Send an HTML list of field values via email
  • How to send multiple files via email in a single message
  • Action triggered by clicking a button in a sent email
  • Quick form and passing information to it from the DOM
  • Retrieve the pinning (pin) or locking (hand) status for a cell
  • Columns are displayed with incorrect sizes
  • Rebuild a multidimensional array into a one-dimensional array
  • Configuring the system's global search to search by a part of the entered number, not just from the beginning
  • How to remove artifacts from the global search across the system
  • Is it possible to launch a temporary table from a tree?
  • Output the values of multiple fields as text separated by a "separator"
  • Filter a list by the key `value`
  • Select all values belonging to a category in a field of type Select-tree
  • Hide the "eye" and "print" icons for users
  • Line break in text when sending an email
  • Sending a file via email without saving it in the database
  • Transferring files from a temporary table to a regular table
  • Codes in fields only work in a specific order, but I need to move the field to the front
  • Data format in PostgreSQL when accessed from an external BI system
  • Composite conditions in `WHERE` parameters
  • Uniqueness check during addition plus removal of extra characters
  • Replacing empty values in dates within `rowList`
  • Blocking the update of calculations in fields based on a condition
  • An anonymous table calls remotes, but data is not returned to the anonymous table even when a hash is passed
  • Back button
  • Working with a value from a pre-filter in `linkToInputSelect`
  • The absence of the three-dot button in an iframe
  • Block file deletion in a field of type File
  • How to enable preview for PDF files
  • Changing the `name` of an already created table
  • Checking field relationships in a table within a project
  • User IDs considering their roles for `notificationSend`
  • Adjusting access settings for Meilisearch results based on roles
  • Personalized sorting in a table
  • Editing a field of type File
  • Send a file from the panel without saving
  • How to alternate colors for days of the week in a table
  • Client registration/authentication through a form
  • LDAP ("ERROR: Operations error")
  • selectRowList: How to collect all data from the list
  • How to add an image to a print template?
  • Is it possible to remove field header titles and the space they occupy?
  • How to pass a pre-filter parameter when creating a cycle?
  • Saving passwords from a form into the Users table
  • How to validate a login in a quick registration form?
  • Missing keys in the response from an external API
  • Restrict a specific user’s access to only the row in the table linked to them
  • How to make a user see only the table inside a cycle but not the cycle table itself?
  • Display only unique values in the selection list of a pre-filter with a select
  • How to restrict a user from changing their password?
  • How to enable navigation to different tables in a cycle based on roles during its creation?
  • Additional information in a cycle table field from inside the cycle
  • How to determine if a row is the last one in a column?
  • Return a list of only those values from the original list that are unique (appear only once)
  • Open a string as a link starting with `https://`
  • The `panelImg` function: How to set an image as a preview from another table?
  • The system slows down with more than 10 users online
  • Row numbering based on the date in the Date field
  • Send files from multiple table rows via email
  • How to display the name or order number in the Tree instead of the cycle ID when opening a cycle?
  • The order of tab display in a cycle
  • Strict filtering in a field of type Select when selecting a value
  • Getting the ordinal week number of a month for a given date
  • Tracking changes in multiple fields
  • Select based on data created in another field of the same table
  • Replacing values in a list with new composite ones
  • Optimization of calculations in a table when the computation is duplicated between code and formatting code
  • Do not execute action code if the change is triggered by a script
  • Do not change the value in a cell if the action is not completed

    • Row numbering based on the date in the Date field

    Question

    Please advise on how to correctly number the rows in the #number field depending on the date in the #date field.

    Example

    There is a table with rows consisting of two columns date and number:

    01.03.24 1 07.03.24 2 20.03.24 3

    How to make the values in the Serial Number column recalculate according to the dates, where the earliest date is assigned the smallest serial number, and the latest date the largest?

    Continuing the example to better explain the question

    I add a row to this table with the date field value 10.03.24, it should look like this:

    01.03.24 1 07.03.24 2 10.03.24 3 20.03.24 4

    And one more nuance, if I add a row with a date already existing in the table, for example, 07.03.24, it should look like this (the numbers in the table should change according to the new order):

    01.03.24 1 07.03.24 2 07.03.24 3 10.03.24 4 20.03.24 5

    How can this be implemented?

    Answer

    Tasks involving complex sorting in tables, such as when we need a continuous sort by the date field plus id, are solved through an additional technical field of type String, where we compile a string Y-m-d-id that can be set as the default sorting field for the table.

    The id must be padded with leading zeros since the comparison will be done in string mode: 10 should be used as 00010.

    The number of leading zeros is determined by the maximum possible id in the table.

    Code in the technical field creating a continuous sorting identifier (field with name teh_order_field):

    =: str`#date + "-" + $zero_prefix + #id`

zero_prefix: strRepeat(str: 0; num: $id_length)
    id_length: 10 - strLength(str: #id)

    For simple tables and cycle tables:

    Now we need to calculate the number in the number field. The number is calculated as the number in the previous row sorted by the teh_order_field plus 1.

    Code in the number field:

    =: $prev_num + 1

prev_num: select(table: $#ntn; field: $#nf; where: 'teh_order_field' < #teh_order_field; order: 'teh_order_field' desc)

    It should be noted that this code will work correctly only in simple tables and cycle tables. In calculated tables and temporary tables, a different approach is needed since the rows in them are virtual and do not correspond to rows in the database.

    Next, it is necessary to add action code in the teh_order_field with triggers for addition, modification, and deletion, which will recalculate the rows following the modified row:

    Action code in the teh_order_field:

    ad1=: reCalculate(table: $#ntn; where: 'teh_order_field' > $#nfv)

dl1=: reCalculate(table: $#ntn; where: 'teh_order_field' > $#onfv)

ch1=: reCalculate(table: $#ntn; where: 'teh_order_field' > $min)
    min: listMin(list: json`[$#nfv,$#onfv]`)

    When changing, we determine which sorting marker was smaller (before or after the change) and recalculate from it, as the date change can go both ways.

    For Calculated and Temporary tables:

    It is important to enable the "Column-by-column recalculation" parameter for the table. This means that the column with teh_order_field should be calculated to the end when the number is being calculated.

    Also, in the calculated table, no action codes are needed for this task since it is always calculated in full.

    Code in the number field:

    = : $key[0] + 1

key: listSearch(list: $list; key: "value" = #teh_order_field)
    list: selectList(table: $#ntn; field: 'teh_order_field'; order: 'teh_order_field' asc)

    For optimization, it is better to create a field in the footer of the teh_order_field column where selectList is done, and in listSearch, take it through #list_in_column_futer.

    Code in the f_precalc field in the footer of the teh_order_field column:

    =: selectList(table: $#ntn; field: 'teh_order_field'; order: 'teh_order_field' asc)

    Code in the number field:

    = : $key[0] + 1

key: listSearch(list: #f_precalc; key: "value" = #teh_order_field)

    Updating the order in the table immediately after performing an action

    When we have the teh_order_field selected as the Sorting Field in the table settings, it only triggers at the moment the table is loaded.

    If all the table rows are transferred to the browser without pagination, we can update their order immediately through the table formatting code:

    Table formatting code:

    f1=: setRowsOrder(ids: $ids)

ids: selectList(table: $#ntn; field: 'id'; order: 'number' asc)