Insert a list

Importante

Before inserting a list in a spreadsheet, ensure the list is tailored to your needs. Consider which fields should be visible as well as how the records are filtered and/or sorted. This can impact both the loading time and the user-friendliness of your spreadsheet.

To insert a list:

1. With the relevant list view open in your database, click the (Actions) icon beside the name of the view, then Spreadsheet ‣ Insert list in spreadsheet.

   Nota

   To insert only specific records, select the relevant records, click the Actions button that appears at the top center of the screen, then Insert in spreadsheet.

2. In the window that opens, edit the Name of the list if needed.

   The list name is used in the sheet name and in the list properties.

   

3. Edit the number of records, i.e., rows, to be inserted if needed.

   By default, the number shown is the number of records visible on the first page of the list. For example, if the list contains 150 records but only 80 are visible, this field will show 80.

   Nota

   While the data in your list is kept up to date thanks to the connection to your database, an inserted list will not automatically expand to accommodate new records, e.g., a new product category or a new salesperson.

   If you anticipate new records being added, consider adding extra rows when inserting the list. Records/rows can also be added manually after the spreadsheet has been inserted.

   Example

   Your company currently has ten product categories and you insert this list in a spreadsheet. If an 11th product category is created and your inserted list only had ten rows, the new category will be inserted in the appropriate position in the spreadsheet, thereby removing an existing category.

   One way to avoid this is to add extra rows when inserting the list.

4. Click Blank spreadsheet to create a new spreadsheet, or select in which existing spreadsheet the list should be inserted.

   Nota

   When inserting a list into a new spreadsheet, the spreadsheet is saved in the Odoo Documents app in the My Drive personal folder.

5. Click Confirm.

The list is inserted into a new sheet in the spreadsheet. The sheet tab in the bottom bar shows the name of the list followed by the list ID, e.g., Quotations by Total (List #1). A panel on the right side of the screen shows the list properties.

Suggerimento

• To sever the link between an inserted list and your database, select the entire list, right-click and select Copy then right-click again and select Paste special ‣ Paste as value.

• Do not modify the list ID in the sheet name, as the inserted list retains this ID for the lifetime of the spreadsheet. This list ID is used in the spreadsheet functions that retrieve data from your database.

List functions

When a list is inserted into a spreadsheet, the following functions are used to retrieve the header and field values, respectively:

=ODOO.LIST.HEADER(list_id, field_name)
=ODOO.LIST(list_id, index, field_name)

The arguments of the function are as follows:

• list_id: the ID assigned when the list is inserted. The first list inserted into a spreadsheet is assigned list ID 1, the second, list ID 2, etc.

• index: identifies the line on which the record appeared in the list before insertion. The first line has an index of 1, the second an index of 2, etc.

• field_name: the technical name of the field.

Suggerimento

Clicking on an individual cell displays the related formula, if relevant, in the formula bar. To display all the formulas of a spreadsheet at the same time, click View ‣ Show ‣ Formulas on the menu bar. The example below shows the functions used to retrieve list headers and values.

Proprietà elenco

The list properties appear on the right side of the screen when a list is inserted. They can be accessed at any time via the Data menu by clicking the relevant list, as prefaced by the (list) icon, or by right-clicking anywhere on the list and clicking See list properties.

The following list properties are shown, some of which can be edited:

• List #: the list ID. List IDs are assigned sequentially as additional lists are inserted into the spreadsheet.

• List Name: the name of the list. Edit this if needed. Note that editing the list name in the list properties does not modify the list name shown in the sheet name, and vice versa.

• Model: the model from which the data has been extracted.

• Columns: the fields of the model that were visible when the list was inserted.

• Domain: the rules used to determine which records are shown. Click Edit domain to add or edit rules.

  Nota

  When global filters are used, this domain is combined with the selected values of the global filter before the data is loaded into the spreadsheet.

• Sorting: how the data is sorted, if applicable. To add a sorting rule, click Add, select the field, then choose whether sorting should be Ascending or Descending. Delete a sorting rule by clicking the (delete) icon.

To duplicate or delete a list’s data source, click the (gear) icon, then click Duplicate or Delete as relevant.

Manage an inserted list

After a list from an Odoo database has been inserted into an Odoo spreadsheet, you can:

• add records, i.e., rows

• add fields, i.e., columns

• duplicate the list to create a new, identical data source

• delete the list and its underlying data source

Add records/rows to a list

To add records to a list, use one of the following methods:

• Select the last row of the table, then hover over the blue square until the plus icon appears. Click and drag down to add the desired number of rows. The cells of the new rows are populated with the appropriate formula to retrieve the list values. If there is corresponding data in your database, the cells are populated.

  

• Position your cursor in the top left cell of the sheet, click Data ‣ Re-insert list from the menu bar, then select the appropriate list. In the pop-up window, indicate the number of records to insert and click Confirm. An updated list is inserted, overwriting the previous list.

Suggerimento

The above methods can also be used to add additional blank rows to your spreadsheet table. This may be useful for lists where you expect additional records to be generated in your database, e.g., new product categories or new salespersons.

Add fields/columns to a list

To add fields/columns to a list:

1. Select the column to the right or left of where the new column should be inserted.

2. Click Insert ‣ Insert column then Column left or Column right from the menu bar, or right-click then Insert column left or Insert column right as appropriate.

3. Copy the header cell of any column, paste it into the header cell of the new column, and press Enter.

4. Double-click the new header cell then click on the field name that appears in quotation marks at the end of the formula; a list of all the technical names of the fields of the related model appears.

   

5. Select the appropriate field name and press Enter. The field’s label appears in the header.

   Suggerimento

   To know a field’s technical name, navigate to the relevant view, activate developer mode, then check the field name by hovering over the question mark beside a field’s label.

6. With the header cell selected, double-click on the blue square in the bottom-right corner. The cells of the column are populated with the appropriate formula to retrieve the list values. If there is corresponding data in your database, the cells are populated.

Duplicate a list

Duplicating a list via the list’s properties creates an additional data source. This allows for different manipulations to be performed on the same data within one spreadsheet.

With the list properties open, click the (gear) icon then Duplicate.

The new data source is assigned the next available list ID. For example, if no other lists have been inserted in the meantime, duplicating List #1 results in the creation of List #2.

Unlike when you insert a list, a duplicated list is not automatically inserted into the spreadsheet. To insert it, perform the following steps:

1. Add a new sheet by clicking the (add sheet) icon at the bottom left of the spreadsheet.

2. Click Data ‣ Re-insert list from the menu bar, then select the appropriate list.

3. Define the number of records to insert and click Confirm.

4. Edit the List Name in the properties panel if needed.

5. Rename the sheet by right-clicking on the sheet tab, selecting Rename, and entering the new sheet name.

Nota

Duplicating an inserted list by copying and pasting it or by duplicating the sheet into which it has been inserted does not create a new data source. Any changes made to the list’s properties would therefore impact any copies of the list.

Delete a list

To fully delete a list and the underlying data source from a spreadsheet, perform the following steps in any order:

• Delete the spreadsheet table using your preferred means, e.g., via keyboard commands, spreadsheet menus, or by deleting the sheet. This deletes the visual representation of the data.

• From the properties panel of the relevant list, click the (gear) icon then Delete. This deletes the data source of the list from the spreadsheet.