Insert a list

There are two ways to insert an Odoo list into an Odoo spreadsheet:

• Insert a list from an Odoo list view into an Odoo spreadsheet, Odoo dashboard, or quote calculator spreadsheet.

• Insert a list that retrieves Odoo data directly from an Odoo spreadsheet. This option allows data to be pulled from any model, including models for which no list view is available, e.g., the Sales Order Line model.

In both cases, the list is connected to the underlying database data thanks to its unique data source, which keeps the data up-to-date and provides access to the underlying data directly from the spreadsheet. However, whether or not the list expands to accommodate new records depends on whether it is a static or dynamic list.

Static vs dynamic lists

When a list from an Odoo list view is inserted into an Odoo spreadsheet, it is, by default, a static list, where each cell retrieves data from the database via an Odoo-specific function. The list’s data is kept up to date, but it does not expand to accommodate new records.

Inserting an Odoo list directly from an Odoo spreadsheet results in a dynamic list, where a single Odoo-specific array function retrieves data from the database. The list’s data is kept up to date and, if desired, the array function can be modified to allow the list to expand to accommodate new records.

Note

A static list can be converted to a dynamic list and a dynamic list can be converted to a static list if needed.

Insert an Odoo list from a list view

Astuce

• Before inserting a list into a spreadsheet, 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 the spreadsheet.

• Property fields visible in a list view are included when the list is inserted into a spreadsheet.

To insert a list from an Odoo list view into an Odoo spreadsheet:

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

   Astuce

   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 List name if needed.

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 view. For example, if the list contains 150 records but only 80 are visible, this field will show 80.

   Astuce

   A list inserted from an Odoo list view is a static list that will not automatically expand to accommodate new records. If it is likely that new records will be added in the database, consider adding 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.

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 spreadsheet shows the list properties.

By default, the list is a static list, where each cell contains an Odoo-specific function that retrieves data from the database.

Note

• The List name is used in the sheet name and in the list properties, and can be edited at any time.

• Records/rows can also be added manually after the spreadsheet has been inserted. Alternatively, the inserted list can be converted to a dynamic list to allow it to expand to accommodate new records.

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

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

Static list functions

A static list uses the following Odoo-specific functions to retrieve the header and field values, respectively, via its data source:

=ODOO.LIST.HEADER(list_id, field_name)
=ODOO.LIST.VALUE(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.

Astuce

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.

Convert a static list to a dynamic list

To allow an inserted list to expand and accommodate new records, convert the list to a dynamic list, then update the array function.

There are two main ways to convert a static list to a dynamic list:

• Duplicate the list from the list properties. The new dynamic list has the same styling as the original list.

  Note

  With this method, the new dynamic list is assigned the next available list ID. This means multiple lists can be associated with the same model, but have distinct configurations or calculations.

• Re-insert a dynamic list from the Data menu. On the sheet that contains the static list, position the cursor in an empty cell. Click Data ‣ Re-insert dynamic list from the menu bar, then select the relevant list.

  A new dynamic list appears, with the same styling as the original list.

  Astuce

  It is also possible to re-insert a dynamic list by entering the function of the dynamic list in an empty cell, e.g., =ODOO.LIST(1), where 1 is the list ID. However, with this method, the table styling needs to be re-applied manually.

  Note

  When a dynamic list is re-inserted from the data menu or by manually entering the dynamic list function, the static and dynamic lists share the same data source and, consequently, the same list ID. To avoid confusion, delete the original static list.

The top-left cell of the new dynamic list contains an Odoo-specific array function that retrieves data from the database.

Voir également

Determine the number of records displayed in a dynamic list

Insert an Odoo list from a spreadsheet

To insert an Odoo list directly from an Odoo spreadsheet:

1. Open the relevant Odoo spreadsheet from the Documents app or create a new blank spreadsheet.

2. Click Insert ‣ Odoo list from the menu bar.

3. In the New Odoo list panel at the right of the sheet, select the Model from which data should be retrieved, then click Save.

The default list for the model is inserted into a new sheet in the spreadsheet. The sheet name in the bottom bar shows the model name followed by the list ID, e.g., Sales Order (List #1). A panel on the right side of the spreadsheet shows the properties of the list.

By default, the list is a dynamic list, where the top-left cell contains an Odoo-specific array function that retrieves data from the database.

Astuce

When a dynamic list is first inserted into a spreadsheet, a maximum of 80 records are displayed. Edit the array function to change the maximum number of records to display or to display all records and allow the list to expand to accommodate new records.

Dynamic list function

Unlike a static list, where each cell contains a unique function that retrieves data via the data source, a dynamic list has a single Odoo-specific array function:

=ODOO.LIST(list_id, [row_count])

The arguments of the function are as follows:

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

• row_count: set a value to limit the number of rows/records.

Voir également

Determine the number of records displayed in a dynamic list

Convert a dynamic list to a static list

In some situations, for example, to be able to add a column containing a manually entered formula, it may be necessary to convert a dynamic list to a static list, where each cell has an individual function that retrieves data from the data source. To do so, follow these steps:

1. On the sheet that contains the dynamic list, position the cursor in an empty cell.

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

A new static list appears, with the same styling as the original list. Click on a cell to see the function in the formula bar.

Propriétés de la liste

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 selecting the relevant list from the List section, 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, which is used to identify the list for the lifetime of the spreadsheet. 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 and the total number of records in the model. The number of records is re-calculated each time the spreadsheet is opened or each time the browser or spreadsheet is refreshed.

• Columns: the columns that were visible in the list view when the list was inserted, or the list’s default columns if the list was inserted from the spreadsheet.

• Sorting: how the data is sorted, if applicable.

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

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

Note

• For a dynamic list, columns can only be added, hidden, or deleted via the list properties; these actions cannot be performed in the list itself. For a static list, the opposite is true; columns can only be added, hidden, or deleted in the list itself.

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

Manipulate an inserted list

Sort columns

Note

Unlike standard spreadsheet filters, which only act on visible data, the Sorting functionality in the list properties panel acts on a spreadsheet’s underlying data sources, essentially sorting data before it is loaded.

To sort a list by a specific column:

1. Open the list’s properties panel.

2. In the Sorting section, click Add.

3. Select the appropriate field. The table is then sorted based on the selected field/column, in ascending order.

4. Change the sorting Order to Descending if desired.

To delete a sorting rule, click the (delete) icon on the field’s card.

Astuce

Add multiple sorting rules to create hierarchical sorting where data is first sorted by the first sorting field/column, then by subsequent fields/columns in order. For example, with hierarchical sorting, customer could be shown in ascending alphabetical order, with their respective sales orders displayed in descending order by total amount. To change the order in which hierarchical sorting is applied, drag a field’s card to the desired position.

Manipulate a static list

After a list from an Odoo list view has been inserted into an Odoo spreadsheet, it is possible to:

• add rows/records

• add columns/fields

Add rows/records

To add rows/records to a static 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 the database, the cells are populated.

  

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

Astuce

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

Add, hide, delete, or rename columns

To add columns to a static 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 select and delete the field name that appears in the first set of quotation marks; a list of all the technical names of the fields of the related model appears.

   

5. Select the appropriate field from the list.

   Astuce

   • Hover over the technical name of a field to reveal the field’s label.

   • To add a related field, click the (right arrow) on the line of a field to access the list of related fields. Select the appropriate field or type . then type the technical name of the field. For example, to display a column with the customer’s country in a list from the Sales Order model, use the related field partner_id.country by selecting Customer, then Country.

6. Edit the field label if desired.

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

To hide a column in a static list, right-click on the column letter header, then click Hide column. To unhide a column, click the or (left or right caret) on the header on either side of the hidden column(s).

To delete a column in a static list, right-click on the column letter header, then click Delete column.

To rename a column in a static list, double-click the header cell, then edit the field label that appears in the second set of quotation marks. To revert to the default Odoo field label, remove the field label entirely.

Manipulate a dynamic list

Determine the number of rows/records displayed

By default, when an Odoo list is inserted directly from a spreadsheet or when a list is duplicated via the list properties, a maximum of 80 records are inserted.

Note

The total number of records in the model is shown below Model in the list properties.

To modify the number of rows/records displayed, double-click the top-left cell of the table, then:

• change the second value in brackets, i.e., the row count, to the desired number; or

• remove the row count value to display all records and allow the list to expand to accommodate new records.

Add, hide, or delete columns in a dynamic list

To add columns to an inserted Odoo list:

1. Open the list’s properties panel.

2. In the Columns section, click Add.

3. Select the appropriate field.

4. Rename the column if desired by clicking on the column’s card, then typing the new name.

To hide or unhide a column, click the (hide) or (unhide) icon on the column’s card.

To delete a column, click the (delete) icon on the column’s card.

Duplicate or delete an inserted list

Note

When duplicating or deleting an inserted list, it is important to remember that each list also has a data source.

Duplicate an inserted list

Duplicating an inserted 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.

To duplicate an inserted list:

1. With the list properties open, click the (gear) icon next to the List Name, then Duplicate. The duplicated list is automatically inserted into a new sheet in the spreadsheet, with the list properties open in the right panel.

2. Edit the List Name in the properties panel and the sheet tab if desired.

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.

Note

• 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 such a list’s properties would therefore impact all copies of the list.

• Duplicating a:

  • static list results in the insertion of the default dynamic list for that model, i.e., with default columns and a maximum of 80 records;

  • dynamic list results in the insertion of a list with the same columns as the original list and a maximum of 80 records, even if the original list had been modified to have a different or no limit.

Delete an inserted list

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

• Delete the spreadsheet table containing the list using the preferred method, 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 list, click the (gear) icon, then Delete. This deletes the data source of the list from the spreadsheet.