Design for List Data Type – Setting up & Filtering List

[ppii775]

This is the second of a series of three new discussions for #978 Design for List data type. This discussion focuses on setting up List data type and filtering. See related discussion #1034 (Updated Mar 4). This discussion has been updated after meeting with Ghislaine on Mar 4.

The Problem

Setting data type

1. The user is able to change the data type of a column to a List and select the data type of what the items of the list should be. By default, the data type of list items will be "Text".
2. The user should be able to change the data type of List items for an existing column.
3. The user should be able to update the database and display options of List items for an existing column. The specific options available will depend on the data type, please see existing data type specs.
4. The user should be able to see errors if changing the data type, database options, or display options fails.

Filtering and Grouping

1. We should support the following filters for List cells: is empty, is not empty, contains <LIST ITEM DATA TYPE>, number of items greater than <NUMBER>, number of items greater than or equal to <NUMBER>, number of items equal to <NUMBER>, number of items lesser than <NUMBER>, number of items lesser than or equal to <NUMBER>.
2. We should support the following custom grouping types for List cells: Number of list items.

Questions Needed to be Addressed

1. How many dimensions are we thinking about supporting? "List" has a single-dimension connotation and it is recommended to be changed to "Array" as used in PostgreSQL.
2. How are we envisioning users setting up a "List"? Do we support only an entry or a cell that is already in an "Array" format? It needs an entirely separate discussion if we'd like users to set up a multi-dimensional array from scratch and the workflow can be complicated (I need some understanding myself).

Possible Solutions

1. Setting up a List data type from Text.

Update 03/04: This solution needs to be explored with multi-dimensional array. It only works the best for generating "List" or "Array" from a cell that has been formatted as such already.
From the column header menu, users can configure a List data type and its item type. This solution assumes Mathesar can automatically recognize a delimiter and allow users to type in a delimiter. Other configuration settings will be added after discussing with team members.

2. Filtering.

Update 03/04: After discussing with Ghislaine, multiple filters are only considered as "AND" operation.
Users can filter by both the List item types, numbers of items, and their content. Rather than providing every possible filtering options (e.g., equal to, greater or equal to...), the following design explores a filter "union" vs. "difference". If "union" is selected, results from all filters will be shown together.

This discussion will be updated after discussing with @ghislaineguerin @kgodey.

[kgodey]

@ppii775 I think your questions are answered in the discussion on #978. As far as feedback on the design here:

• We do not need to display or store a delimiter. Lists are stored as separate items in the backend.
• The database type is actually going to be something like VARCHAR[] for a list of text, there is no dedicated ARRAY type. The [] at the end of a database type signifies that it is an array, and the VARCHAR signifies the database type of the list item.
• We need a place to show the database and display options of the list item type. e.g. if the list item type is text, the user can then configure the database and display options of the list item like they would any text type. The same applies to other database types.

[kgodey]

Please let me know if any of the above is unclear or if you have more questions.

[ppii775]

@kgodey

So for example, List Item Type (as I've annotated above), is a database configuration so it should go under the "Database" tab.
Additionally, the List Item data type comes with its own database and display options (which you can refer to using the prototype above), which we also need to allow the user to configure. So, to follow the examples above:

• If the user sets the List item type to Text, they should then be able to set a "Field size limit" for the list items.
• if the user sets the List Item type to Boolean, they should be able to set the "Display as checkbox" option for the list items

Where is the "database" tab?
I was envisioning users converting an existing field into a List, so I didn't think additional properties would be useful since the data already exist, which means I no longer need to give them a field size limit.
From reading your explanation, it seems when first opening up a table (I assume this is the "database" you were referring to), all columns need to be manually configured with a data type from scratch (and this would be the "display"). Is this correct? For some reason I always thought this would be semi-automatic, meaning a default type would be given to the data, and users are mostly changing data types when they need to. If we use the following screenshot as an example, is this a process of converting a "database" to a "display"? What if the data is bigger than my display setting (say, 80)?

I'm not familiar with data splitting in Tableau. I don't understand the workflow here with the delimiter or why a delimiter is necessary.

I'm imagining a user has a data entry like this 1,2,3,4,5 and it is not a List. User would like to convert this into a List, so the user is telling Mathesar the delimiter is comma. However, based on the discussion above, maybe you are thinking it is already a List in the database, the user is just configuring the "display"? Please clarify.

[kgodey]

Where is the "database" tab?

@ppii775 The "Database" tab is this Figma prototype which I also linked to in the first line of my response above. I annotated some screenshots below, which might help. You can click through the prototype to look at the configuration menus for all the data types.

"Database" and "Display" are just categories for different options here. If you've used a spreadsheet program like Google Sheets, think of "Database" settings as applying data validation options to a column and "Display" settings as applying formatting options to a column. If this is unfamiliar to you, let me know and I'll explain in more detail.

I was envisioning users converting an existing field into a List, so I didn't think additional properties would be useful since the data already exist, which means I no longer need to give them a field size limit.

From reading your explanation, it seems when first opening up a table (I assume this is the "database" you were referring to), all columns need to be manually configured with a data type from scratch (and this would be the "display"). I

There are two different use cases here:

Adding a new column

The user is adding a completely new column. They start off with the column being blank. They know they want to store List data in this column, so they configure the data type before entering any data.

As an example, think about a user who wants to store the dates they watched a given movie. They start off by creating two columns, "Movie Title" and "Date Watched". They set "Movie Title" to a data type of Text and "Date Watched" to a data type of List with list item type set to "Date".

Converting an existing column

The user is converting an existing column to a List data type. There may be some existing data to convert, but even after they convert it, they might want to add or remove data.

As an example, think about a user who has been keeping a log of movies watched. They have two columns, "Movie Title" (data type Text) and "Date Watched" (data type Date). Then they rewatch a movie (let's say it's The Shawshank Redemption) and realize that they cannot enter a second date into the "Date Watched" column because it can only accept a single date. They then change the "Date Watched" column's data type to a List. Now it will accept multiple dates, and they find the row for The Shawshank Redemption and add a second date in that row the "Date Watched" column.

---

In both these cases, their data in the "Date Watched" is a list of dates. Dates can be formatted in different ways (you can say "Jan 3" or "2022-01-03" or "01/03/2022". How should the dates in the list be displayed? Here's where the "Display" tab comes in.

You can see that when a data type is set to Date, the user can use the setting I've highlighted above to set the format. But once they have converted their column into a List (with List Item Type "Date"), they still need to be able to set the formatting options for how to see the dates in their list. But there's no way to do that in the current design, since all "Date" options disappear once the column is a List. We need to find a way to show both List specific options and Date specific options.

---

Does this make sense? I didn't answer your later questions because I wanted to focus on what I meant by "database" and "display" options for data types, but let me know if any of them are still applicable. Let me know also if any of this is unclear.

[ppii775]

Thanks so much for your detailed reply! Apologies I missed the link – I thought they were the same but I have found this version in the prototype now.
There's fair amount of info and I need to look into this more to digest and make sure I don't miss any more things. I'll keep you posted if I have questions.
Once again thank you for your explanation and patience.

[kgodey]

You're welcome! Don't hesitate to ask questions whenever you have them. :)