M2m full join list

Hello, I might totally miss the obvious but somehow I am lost… How can I create a list of a full join of two tables with a m2m relation

e.g. orginal tables

  • clothes - tshirt, trouser, skirt
  • sizes - s, m, l
    clothes has an m2m relation to sizes

The list I need is something like

  • tshirt s
  • tshirt m
  • tshirt l
  • trouser s
  • trouser m
  • trouser l
  • skirt s
  • skirt m
  • skirt l

I am sure this is documented somewhere :sweat_smile:

Not sure what you mean by

Is every entity in the Clothes database related to every entity in the Size database?
Or is it just that the databases have a m2m relation?

yes i need the cartesian product of all permuations

It sounds as though you’re saying that the databases have a relation, but the entities themselves are not linked.
If that’s the case, then it’s not possible.
You need to link the entities (manually, via automation, or with an auto relation perhaps).
Then you could use a hierarchical list to show all sizes for all clothes.

But ultimately, it’s not clear what your objective is.
I suspect it might be the case that you actually need to create a ‘bridge’ database, that connects (many-to-one) to clothes and also connects (many-to-one) to sizes. Then you can create all the combinations as entities in this db.

I’ve looked for something like this as well. If we are thinking of the same thing, it’s like running a query on two related databases with a join statement and getting a list of every record in the clothes table for every relation it has in the sizes table.

Fibery displays it as either:
tshirt | s, m, l
s | tshirt, trouser, skirt.

Reports, as far as I can tell, do more of a union than a join and you are unable to get the desired result.

Well, I guess the problem is that there is no such thing as a small t-shirt if you only have 2 databases.
There is a tshirt, which may be linked to small, medium and/or large. If it is linked to small, it is the same small that the skirt is linked to.

If a hierarchical list were to show:

  • small
    – tshirt
    – trouser
    – skirt

  • medium
    – tshirt
    – trouser
    – skirt

  • large
    – tshirt
    – trouser
    – skirt

then it contains the same information as is contained in a list that shows

  • small tshirt
  • small trouser
  • small skirt
  • medium tshirt
  • medium trouser
  • medium skirt
  • large tshirt
  • large trouser
  • large skirt

So I’d like to know why the latter is preferable? How is the resultant information to be utilised?

Fyi, it is definitely possible with report view to create a table that looks like this.

Not quite a list, I know, but sufficient maybe?

Hi all,

Sorry if I was too unclear in my case description.

I do already link the entities, but maybe the intermediate database might even be a better idea, which, in this example, would allow to manage stock (i.e. having more attributes on the relation, like “amount”.

Anyways, the result I want to have is a (concatenated?) description of all related tuples, as you @Chr1sG indicate with your report. Could you reveal how you built that? This would be of great help :slight_smile:


My brain wasn’t working very well when I was testing that. Thank you!

The report view can be made by picking one of the two databases to report on, and then in the table you choose Name as one column, and the other database as the other column.

Nearly there :slight_smile: What if I need not the Name field of the referred table but another column?

Then I suggest using that db as the source, and picking this column instead of Name.

Or did you mean that you needed to show non-Name fields from both dbs?

The latter. I was hoping for some reference mechanism like [sizes].[otherlabel] or similar

In that case, you would need to make the fields from one db available in the other (using lookups or formulas) prior to making the report view.

Thanks @Chr1sG , got it!