Link Tables / Relational column field

[xbluemonkx]

It would be amazing if we coul activate a Key-Field in a table and re-use that key field in a second table.

Here is an example use-case:

• Table A: List of Movies
  -- Fields: Name, Year, Category, etc.
  -- Key Field: Name

• Table B: Comments
  -- Fields: Name (From Table A), Rating, Comment

That would be amazing because, than you could add a Link in Table A that says "show comments" that links to Table B that is Pre-Filtered (#171) for the Movie that you want to see the comments for.

Long term goal would be to pre-aggregate the informations from Table B in Table A. So instead of having a column with a link that says "show comments" we could have a column that says "average rating" or "number of comments".

[datenangebot]

Hi,

thanks for your issue.
That is exactly what I want to do. It is on the roadmap and I like to use this issue to track that.

[xbluemonkx]

amazing to hear this! while I am not able to support you code-wise, I have a use-case ready and I am happy to support you as a beta tester and provide feedback.

is there any ETA on the v0.5? I saw that v1 is estamated for end of june, does it mean v0.5 will be available earlier? I am really excited!

[datenangebot]

I add some new features and fix bugs over the time an release it as 0.x releases. The over all roadmap can be found as a github project. The detailed planning as milestones, but that's a lot dynamic in there... 🙃

So, yes there will be some releases before the hopefully absolute stable 1.0 release. Testing is welcome. Thx!

[almereyda]

Merging of #375 for the #234 use case brought the ability to link to any Nextcloud resource that exposes a search provider.

Could this mean in return, that Tables should implement one for itself, and it will immediately be able to link to a table? Or would we rather want to link to selected columns, as presented in the OP?

Then we could retitle this one a bit to *Link columns between tables", or: "Create table from table"

The other use case could be to reference (multiple) items from a certain table in a column, similar to how we reference other resources in #234.

[xbluemonkx]

@almereyda I think we have two different use cases here.

1. referencing ressources: If we consider tables as ressources we could create a table that has a link to another table. e.g. you could create a table that is called "all my tables" and in each row you can enter one of your tables and then use the reference feature to link to the table. if you click on the link you will be forwarded to the linked table. - even i don't think this specific example is very helpful, I like the idea that tables should expose a search provider by themselves. not only that you could use it in the described way, but you could also reference a table in the chat. that would be very helpful from time to time.

2. actually linking tables: this is what this OP is about.

[datenangebot]

@almereyda Thanks for clarification. I agree, we have to different use cases here.

The first is not yet implemented and will bring some linking of data per row between tables. So an order in a table "orders" can refer to a customer from the table "customers" or whatever.

The second use case is now merged, but not yet shipped, about linking (means giving a route to external resources) resources what we now within our nextcloud or as plain url for whatever. We don't want to reinvent the wheel, so we reuse the search logic and so we provide all the resources that are provided via the search logic. This includes the tables itself, but only the tables and hopefully upcoming also the views for tables. No rows. That's it.

[almereyda]

Thank you for the clarifications and confirmations!

[xbluemonkx]

@juliushaertl - this is probably one of the most complex Feature requests at the moment, but for us it is a must have, before we can start using tables app in our work routines. this has been moved from milestone to milestone and at the moment it is not yet assigned to any milestone anymore. would it be possible for you to provide some insights on the internal progress / roadmap regarding this idea?

[dillardblom]

@juliushaertl @datenangebot Although I realise this is not an easy-to-implement enhancement, but it would indeed be a great feature. I'm not suggesting to build a own referential database system, for those there are better options available (outside of the Nextcloud framework), but for a 'simple' application as the movie app OP has given as example, or a student, classes and grades internal app (table1 : student information, table 2 : available classes, table 3 : student (link), class (link), grade. ) this would be a great solution.

So is there any idea when this would become a milestone target?

[marcoambrosini]

@nextcloud/designers this was picked for 31

[juliusknorr]

This is not fully specified yet on how we want to approach it. My first though would be to introduce a new column type for relation to another row or multiple rows.

• When creating the column you can limit it to a specific table/view
• The column setting needs an option to configure what information to show in the column (e.g. which column from the other table)

Some example use cases:

• You have a table listing your teams (e.g. office team, groupware team), then you have a feature list table where each feature gets assigned to one team
• There is an invoice and a invoice positions table, each invoice position gets linked to one invoice
• There is an feature list table and a customers table, each feature can have multiple customers referenced for noting interest in a feature

Open questions:

• Do we want to have back references?

We should come up with a proper mockup together with the design team about this and then discuss how this can be technically implemented.

[xbluemonkx]

HI,

glad to see this is still beeing worked on. Just as an Input for the Brainstorming I made up a scenario.

And please keep in mind, that you might want to connect more then two tables. E.G. "Movies" + "Actors" + "Filming Locations"

[xbluemonkx]

I would like to add one more use case, that is probably the most used in the end.
Given the VIEW: Results. I want to click on one rating. and this should open "VIEW: Detailed Results", but pre-filtered to the single movie that belongs to the rating i clicked on.

[ISC-PRO]

Good morning. Can you tell me what the further planning looks like?

[enjeck]

Good morning. Can you tell me what the further planning looks like?

I'm gonna start working on this. Here's how I imagine it to look:

1. You can create a Relational column just like you do any other column. Here, you specify a table/view that should be linked to it and a default column that should be shown (see point 3)

2. When creating a row using this this relational column, you can select a row from the linked table/view:

3. After row is created, the relational column cell value shows an item from the linked row, based on the default column selected in point 1. If no default selected, then the first column is used. You can hover on the cell to see the full linked row, and click to visit the table:

Hope this all makes sense!

[xbluemonkx]

Hi,
glad to see this idea is coming to live.

What you decscribe probably makes sense, but to be honest I am not completely getting your approach.Therefore I woul like to challenge it.

Step 1)
Lets say we have two tables "MOVIES" und "COMMENTS". Now I can add a new column to the table "MOVIES". That column is relational. So i select table "MOVIES" (i am not sure if it makes sense to provide views as an option too, but it is shown in the mock up - "select a table/view").
What is the "default colmumn to show" selector for? I am missing the "use this column as link" - or is this handled internally by an ID when the user selects a specific row in a later step?

For the example: Let's say I add the new Column "movie title" from the table "MOVIES" to the table "COMMENTS" and call it "movie link".

Step 2)
If i get it right that means that if i add a new row to "COMMENTS" the is one field "movie link" where I can select from all the values (rows) stored in the colum "movie title" from "MOVIES". So i can link one movie to each comment.

This makes sense, but we will need another feature: When creating columns we need to be able to specify "no duplicates allowed" just to avoid that in "MOVIES" there are multiple movies witht the same name. - technically possible, but it will confuse users. (i created a ticket for this #1566)

Step 3)
I think i am getting it now but the term "default column" is confusing. Because it implicates somehow that it is default, but can be changed. But the link is fix! I think it sould be called "link column" or "reference column" instead!

Overall)
All of this makes totally sense when you have a 1:1 or N:1 relationship. (one comment can have one movie - multiple comments can have the same movie). - But what if you have a 1:N relationship? (one movie can have multiple comments).

I guess your approach in the backend is based on internal Row IDs that means technically it is a 1:1 relation all the time. But this will cause problems. We need to keep in mind that we will have 1:N relationships and the user wants to aggregate the values. - E.g. 1 movie has N comments. Now i want the link column to show count(comments) or avg(rating).

But this is probably a feature for views?

Hope my thoughts are helpful.

[Logianer]

@xbluemonkx your feedback makes sense in terms of naming things. Yes, it should be called "linked column" or "Label Column" or somerhing like that. What i dont understand is your critique on the relational approach:

• If you want to have 1:1 relations, you can have them with our current solution.
• If you want to have 1:N relations, you can have them with our current approach. (and 1:N is N:1)
• If you want to have N:M relations, you can have them with our current solution. Just create a Linking table with two columns that link two table entries together. Just like you would do in a normal relational SQL Database for example.

The real problem
What @xbluemonkx is referring to, is a completely different, but also important question: How do we display these relations??
Saving them is easy, but we need to have a view or a table that can display all fields or an n:m or 1:n relation. (something a sql "select join" operation would do)

Maybe there can be the option to "expand all" columns of a linked table into the current view so that not only the "default column" is visible, but all columns and their respective values.

That is all I wanted to say for now.
Have a great day!

edit: typos

[xbluemonkx]

@Logianer you are completely right. The ticket has a more technical focus while I am wearing the user-glasses.

Technically: your approach sounds good.
Usability: i think we will have two scenarios:

1. Showing all - this is the one you described
2. Aggregate - show only one value. (max, min, avg, sum, etc.)

Edit: One more thought on N:M - I Aggree with you that this is already technical possible with the suggested solution. But from user-perspective this is pretty messy. E.g. if you have two tables "MOVIES" and "ACTORS" with the current approach you will have a separate table "ACTORS-MOVIES" where you have to maintain all the information of which actor was in which movie (or vice versa).

I think it would be much mor comfortable if you could specify if a a linked column is allows single- oder multi-link (Step 1). And then you could select one ore more values/links (Step 3). - But this means that you would have to choose a different technical approach and let the table app handle the linking-table for the user. - sorry, just brainstorming to avoid future technical dead-ends.

Edit2: Another thought on a another usecase when it comes to re-presentation: will it be possible to show "secondary" columns in the vies? e.g. I link to "COMMENTS" to "MOVIES". Now i want to have a view that shows the comment and the movie ("primary column" - link) but although the year the movie was released and the studio that made the movie ("seconday columns")

[ionoci]

Hi @enjeck,
thanks a lot for implementing this.
How may I possibly beta test this? 😃

[enjeck]

@ionoci It's not fully implemented yet. Still working on it! You'll know when it's done since this issue would get closed 😀

[ionoci]

@enjeck 👍 Whenever you need a tester I'll be available 😀