1:n relation with a different source column name

Dear Peter,

one small add-on to my previous message:

The order of those two lists below the edited table will change whether the 1:n is defined below or above the lookups in the Manage Options…

Kind regards

Michael

Hi Michael,

Thank you for your compliments! ?? Always nice te hear…

Not sure if I understand your question correctly, but you should be able to select a column from the lists (source colummn name and target column name). However, you need to save these settings explicitly by click on button save relationships. Did you save them?

If not can you add some screenshots to clarify the issue? Thanks!

Best regards,
Peter

Hi Peter,

thanks for your fast response.

Well, I am of course able to select any column as an 1:n source, however, when executing it will only use the table’s key to generate the link.

And yes, I am saving the table options using all 4 buttons ;-).

Here’s screen shot:
https://www.dropbox.com/sh/llg644w4ob7zrb2/AADi1NGwH8_6V90sGnbQC53ra?dl=0

Best regards

Michael

Hi Michael,

It looks like you have a 1:n relationship
(1) from Voices_Liedlisten to Voices_Liedlisten_Namen and one
(2) from Voices_Liedlisten_Namen to Voices_Liedlisten back.
Is that correct?

Can you send me your project? If you send me an email from the plugin website I’ll reply and you can send your files in private.

This is how you can export your project:
– In Data Projects click the export link on the project row
– In Manage Table Options find your tablea and export them
– In the Data Explorer find your tables and export them

It often helps if I can have a look at it…

Best regards,
Peter

Hi Peter,

well, not exactly. I have here just one 1:n. The other two are just lookups.
Even if I remove the two lookups, the 1:n error persists.
I tried to mark the wrong link encircled in red.

As you can see, the 1:n is defined as:
from Voices_Liedlisten – Column LLID
to Voices_Liedlisten_Namen – Column LiedlistenID

But what it actually does (see on the left below):
from Voices_Liedlisten – Column id
to Voices_Liedlisten_Namen – Column LiedlistenID
which is not correct!
It should display a reference to 3, instead it uses 18.

Let me check out the exporting tomorrow, if really needed, as the table and 1:n definition are straight forward.
Everything is in the screenshot…

And I have added the overall data model in another file, same link as above.
This error occurs everywhere. It will only refer to the key column as source, not the selected source column

Best regards

Michael

Hi Michael,

Can you explain what you are trying to achieve? Can you draw your desired page layout (including the meaning) on a paper and upload a photo? Preferably without any technical details? Take your time! I’ll try to look at it tomorrow.

Best regards,
Peter

Hi Peter,

first, please, let me tell you that your dedication is really superb and I am flashed by your responsiveness!

Regarding your question, I am not so sure to understand though, as I thought my screenshot would speak by itself…

Nevertheless, I will put something to the folder tomorrow (see the link above).

Have a good night.

Michael

Hi Michel,

Your data model shows a relationship between the tables Voices_Liedlisten and Voices_Liedlisten_Namen. From your model it is not clear which table is the parent and which table the child.

In the table settings you have defined Voices_Liedlisten_Namen as a child and a lookup of table Voices_Liedlisten at the same time. That looks like a design failure. Table Voices_Liedlisten_Namen can be a child or a lookup of table Voices_Liedlisten, not both.

Draw your desired page layout on paper without thinking about relationships first. That often helps…

Best regards,
Peter

Hi Peter,

thanks for your kind support.

In order to demonstrate my issue, I have established a small project and exported it to the Dropbox link above.

If you go to Menu ?Test 1 to n“, table ?Events“ and edit Event 4, you will recognize my topic:

Below the table editor, it should display a list for the source column SetListID 3 references, however, it uses SetListID 4 as reference, which comes from the Event 4’s id (= 4).

What am I doing wrong?

Thanks a lot!

Michael

Hi Michel,

I’m very busy at the moment, I’ll have a look at it as soon as possible.

Best regards,
Peter

Hi Michael,

Thank you for your files. I been thinking what you are trying to achieve and I came up with this model:
https://wpdataaccess.com/wp-content/uploads/2020/01/michael.png

The data model does not contain all your columns. It is just meant to get the relationships right first.

This data model allows you to create multiple music lists. A list can contain multiple titels and titels can be added to multiple lists. A list can be added to an event and an event can contain multiple lists.

You can do this with the plugin! Let me know what you think…

Best regards,
Peter

Dear Peter,

first, let me thank you a lot for your considerations, your ideas and your time!

I am sure that the proposed data model could be fine and as well be a bit more flexible than mine (even if I don‘t need multiple songlists for one event).
My data model used the main principle of a key-reference list already for the titles and the songlists, but not for the events, which you ?correct“ in your model.

In a nutshell, your proposal tells us – in other words – that the selection of a source column for a relation other than the primary-key works for lookups only. It does, however, not work for 1:n or n:m relations.

I propose that you bring this up in your documentation, if not already in (I probably overlooked it).

Could you please be so kind to re-confirm this fact?

Thanks a lot for your kind help!

Michael

Hi Michael,

It is not a matter of wrong or right data model. Wheter your data model is correct or not depends on the functionality you are trying to achieve.

You need to make a difference between lookups and relationships.

A lookup is in fact nothing more than a column constraint which allows the user to only enter data available in the lookup table. (CONSTRAINT means: no info is added)

A relationship is something completely different. It allows you to add multiple child records to a parent record. (extra info is added) Your child table MUST repeat the parent keys! Otherwise there is no relationship.

Regarding your data model, table Voices_Liedlisten_Namen cannot be a lookup and a relationship of table Voices_Liedlisten at the same time. Given the columns those tables contain, I would suggest this is just a lookup. But that is only from a technical perspective. I’m still not sure about this, since I don’t understand what you are trying to achieve.

I would suggest to just get back to the design table and get clear what you want to achieve. It often helps to take a piece of paper and just draw the intended page layout. Once you have that clear, you can start to normalize and create your tables.

Hope this helps.

Best regards,
Peter

Hi Peter,

well understood, thanks, I got it running ;-).

Nevertheless, the ability to select any source column besides the table’s key when defining a 1:n or n:m relation other than in a ?default“ definition suggests that it would work, which is not the case.
Maybe you would one day chose to restrict this use, which might not be easy due to the ?hierarchy“ explained in one of your nice videos.

Whatsoever, thanks for this incredible plugin!

Kind regards

Michael

?? Well done Michael! ??