Hi. It’s possible in Fibery to create a union query? Example Table A={name, field1} and Table B={name, field1} I want create table C=A union B. If A contains records [a,b] and B contains records [c,d] then C contains [a,b,c,d].
Thank you.
How do you want the union to be displayed?
The majority of views support presenting entities from two (or more) different databases.
Hi Chris, I’m applying a lot, but I’m finding some unexpected problems. Union query is fundamental for creating a knowledge space.
Like this:
I was successful with the feeds, but I don’t get a table.
Can you give us a bit more context about what you’re trying to achieve. At the moment, it seems rather abstract. Perhaps you can describe a specific use case…
OK Chris, I try.
Note: I indicate your space object with {space name} and your database object with [database name].
I’m creating a KOS to organize my personal knowledge base in different subjects.
Basically there are the following main sources of knowledge:
1] websites (also audio and video source)
2] books and magazines (in electronic pdf format imported in Fibery, and physical books on the library shelves)
3] documents created with markdown page, inside Fibery (notes, reflections, additions etc …)
The first two sources are two databases [books] and [sites], the third source is instead encapsulated in the various records directly at the lowest analytical level.
In the table [books] there will be all books of all topics (my full library).
In the table [sites] there will be all the sites of all topics (my full website collection).
I would put the two tables in a space called {sources} (which I would like to have in a separate workspace, but I’ve already seen that you can’t).
At this point I create as many spaces (let’s call them ‘knowledge spaces’) as there are study subjects. This is because, as we will see, each subject needs its own databases. So I will have the spaces: {computer science}, {law}, {mathematics} etc …
Take for example the knowledge base relating to information technology.
There are five areas of interest in computer science: programming languages, databases, computer networks, operating systems and hardware. Each area of interest has one or more specific databases to organize knowledge in a structured way. Programming languages has a specific database relating to language elements [language elements] where functions, instructions, operators, modules, classes and their relationships, etc. are documented, computer networks have a specific database relating to network protocols, where their structure is documented, the encapsulations between protocols is documented etc …, the operating systems instead have a database relating to commands with the relative options, one relating to the structure of the file system, one relating to file extensions, etc.
Up to here it is all very well feasible with Fibery (which between Coda, Notion and Zenkit is the one that best manages the hierarchies, which are indispensable to me).
Go on. All these spaces, related to the knowledge bases, in addition to the peculiar databases that I have indicated above, will also have three identical databases repeated in each space:
[book references] relation n to 1 with [books]
[site references] relation n to 1 with [sites ]
[arguments] (database that hierarchically structures a specific ontology for each sector).
In the {computer science} space we will therefore have:
[computer science reference to books]
[computer science references to sites]
[computer science arguments]
In {law} space
[law references to books]
[law references to sites]
[law arguments]
and so for the other spaces.
If I organize as I have illustrated up to now, I encounter an apparently insurmountable problem: by inserting a reference to a book, the list box shows all those present in the books table, the entire library (the books are thousands). There is no way to filter the records, I have already documented.
A solution is to bring the books and sites to the level of knowledge space, so we will have in addition to the databases already mentioned:
[computer science books] (less than a hundred books and therefore manageable)
[computer science sites]
in the {computer science} space
[law books]
[law sites]
in {law} space
etc.
That’s fine too, but I need to be able to create the [books] table and the [sites] table, with a union query.
The views feed and lists, which support union, are not adequate because they do not create any tables.
If you want, I’ll prepare an example for you and I’ll share it with you.
In theory another solution is create a table [computer science studied books] in {computer science} space with relation 1 to 1 with [books], where I select the studied books, and make a relation 1 to n of this table with [computer reference to books], but it’s an horrible mode. Very inelegant.
I think I have hit an underlying problem.
I state that Fibery is an exceptional application, but strangely Fibery looks strong on complex things like hierarchies and inadequate on simple things like a filter in a combo box (in Microsoft Access available since 1990).
Thanks for your attention.
Ok, that sounds quite complicated. I’m not even sure Fibery is necessarily the best tool for your application.
What other tools did you consider (apart from coda, notion and zenkit)?
Ther may be people here in the community who have other suggestions, but did you investigate Roam, Obsidian or Remnote?
If you are set on using Fibery, there is one thing that made me curious:
Why would you create these extra ‘reference to …’ databases? Why not just link from the area-of-interest database directly to the books database?
Or maybe you would be better served by using references instead of relations. E.g. #mention a book in the rich text field of a computer science entity
I need the databases and hierarchies. Also of note taking, but above all of databases. My app is 80% databases and 20% notes.
I have to get this:
Based on what you have said, I think the need for a union query comes from here:
It strikes me that having a unique book database for each subject is not the optimal solution, and the need for the union query is a workaround for the inability to solve the real issue.
I think the real problem that needs solving is
You want to be able to select a book (from amongst thousands) based on the the context of the entity you are picking from, right?
This would seem to be related to this:
It is partly solved, by hierarchical relations, but this doesn’t work in your case, because that functionality is not supported for many-to-many relations.
At this point, I wonder if anyone in the community has any suggestions for how to help you (either with a better tool suggestion, or with a better Fibery data model).
For now I have solved with the third solution. A table [studied books], in relation n to 1 with [books], but this new table shows the division into chapters, which in any case is information that I need.
Unexpectedly I get an excellent view like this:
However, think about these Fibery gaps, which if filled, would make Fibery even more powerful and flexible:
- filtering combo box source
- union query for table view (feed and list are ok)
Thanks for your help.