after a long time searching in the forum and trying different ways in Pan X I turn to the forum in hope for a solution.
I am trying to establish a m:n relationship between two tables (contacts and companies). One contact can be connected to more than one company and the company can have more than one contact. In a “normal” relational database the connection between the tables is established by a bridge table.
If I got it right, in Pan X 10.2 only joins between 2 tables are possible. So the bridge table option does not work here.
If I try the lookup function (e.g. superlookup(“Unternehmen”,|||««VAS-ID»» contains «VAS-ID»|||,|||PLZ|||,“default”,0)) I also only found a n:1 or 1:n option but not a m:n option
Is there maybe a simple solution I missed yet?
Thanks in advance,
Ralf
Unfortunately there isn’t currently a simple solution. Support for m:n relations is on our list for possible future enhancements, in fact we have some design ideas sketched out. However, there is a lot of work on our plate at the moment so there is no timeline for this.
If I needed to do this now I would probably use a Text Array in a field in one of the databases, essentially building the bridge table into one of the databases. For example in the Companies field you could have an Employees field that contains an array. Then you could use various array functions (arrayfilter(, arraybuild(, arraycontains( to calculate the related data. If M and N are generally fairly small this could be a reasonable solution, for larger values of M and N performance might be an issue. And it won’t be simple.
It should also be possible to even set up a bridge table with the current version of Panorama, but it would not be simple to set up and there might be performance drawbacks, again depending on the amount of data.
Hi Jim,
thank you very much for explaining in detail. This confirms the impression I got when I was searching for a solution in that matter.
Kind regards,
Ralf