N:M relation in a view

Your solution is not only perfect but also educational. You have just removed a very annoying little pebble from my shoe!
The last solution works as a dream. (I hope that Ninox will soon offer an easier way to manage N:M links).
Thank you so much for your time and expertise.

Hello, I am back with an update to this old thread. So, with the following formula, I get the Médias relative to a Composer :

let zCompName := “ | ” + Name + “ | ”;
(select “Médias”)[contains(“ | ” + join(Occurences.Oeuvres.Compositeur.Name, “ | ”) + “ | ”, zCompName)]

Now, I need to have the sales relative to all these medias for a specific year. The following code is buggy but I do not see why :

let zCompName := “ | ” + Name + “ | ”;

select Sales where (select “Médias”)[contains(“ | ” + join(Occurrences.Oeuvres.Compositeur.Nom, “ | ”) + “ | ”, zCompName)] and year(Invoice.Date_invoice) = zYear

Any help highly appreciated!

Thank you for your reaction. (I worked previously with 4D where it was possible to store intermediate queries, which makes complex queries easier to build.)

[Sales] is a children of [Invoice] and is linked to [Médias]. The Date is a field of [Invoice].

Thank you very much for this important development. Putting the calculation back into the composer“s table and using the ”single“ command offers a lighter perspective.

However, I still have two problems:

1. On typing:

unique((Oeuvres.Occurences.“Médias”.Sales[year(Invoice.Date_invoice) = zYear])

Ninox asks to place a comma before the last parenthesis of the line, which I don”t understand. (I have indeed replaced double quotation marks with singles).

2. Once the view has been created, would it be possible to export it in order to have a table of annual royalties for each composer: [composer; year; amount of royalties]?

I am really measuring the value of the help you are giving me.

Dear Alain, your diagram is perfect and, indeed, the problem was simply that of the parenthesis. I must admit that my base is far from being finished, at least with me as a developer.
I was wondering if you were a professional developer. If so, I would be happy to consider a business relationship with you to solve the remaining problems: establishing an annual inventory statement and finalizing the copyright calculation.
You can contact me by email: infoATnewconsonantmusic.com.
Thank you in any case for your past and present help.

I finally managed to fix the issue thanks to Alain (and the end of the year days-off). Here is my button script for anyboby needing some inspiration :

let xCurrRec := ID;
let xYear := Year;
"// Select Medias ready for inventory //";
for i in select Medias where text(Step) = "Inventaire" and 'Année_sortie' <= xYear do
"// Create a subrecord for each Media //";
let x := (create InventaireLigne);
x.(Inventaire := xCurrRec);
x.(Year := xYear);
x.(Media := i);
"// *** variations annuelles *** //";
let xVentes := sum((select Ventes where Media = i and year(Factures.Date) = xYear).Nb);
x.(Nb_VentesAn := xVentes);
let yVentes := sum((select Ventes where Media = i and year(Factures.Date) = xYear).Prix_total);
x.(VentesAn := yVentes);
let xHommages := sum((select Hommages where Media = i and year(Date_hommage) = xYear).Nb);
x.(Nb_HommagesAn := xHommages);
let xTirages := sum((select Tirages where 'Médias' = i and Nb > 0 and year(Date_tirage) = xYear).Nb);
x.(Nb_TiragesAn := xTirages);
let xPilons := -1 * sum((select Tirages where 'Médias' = i and Nb < 0 and year(Date_tirage) = xYear).Nb);
x.(Nb_PilonsAn := xPilons);
let xFraisProduction := sum((select Tirages where 'Médias' = i and Nb > 0 and year(Date_tirage) = xYear).Cout);
x.(Frais_ProdAn := xFraisProduction);
"// *** état final du stock *** //";
let zVentes := sum((select Ventes where Media = i).Nb);
x.(Nb_VentesTot := zVentes);
let zHommages := sum((select Hommages where Media = i).Nb);
x.(Nb_HommagesTot := zHommages);
let zTirages := sum((select Tirages where 'Médias' = i and Nb > 0).Nb);
x.(Nb_TiragesTot := zTirages);
let zPilons := sum((select Tirages where 'Médias' = i and Nb < 0).Nb);
x.(Nb_PilonsTot := zPilons);
let zFraisProduction := sum((select Tirages where 'Médias' = i and Nb > 0).Cout);
x.(Frais_ProdTot := zFraisProduction)
end

(Well, I have other small problems but this will be for a new thread...)

A last (?) problem connected with this case...

I have to draw up a sales statement for each composer in order to calculate his yearly royalties. In the [Redevances] table, I have placed a button in charge of creating a sub-record [RedevancesLignes] for each [Media] of the composer (this works... thank you Alain!). The script will then search for the sales made for each media and calculate the number and the amount of sales [Ventes]. However, only certain sales are taken into account. I've been looking since yesterday why many sales are ignored. I see no difference between the selected sales and the ignored ones. Do you understand the error in my script?

[Ventes] is linked to [RedevancesLignes] which is a sub-table of [Redevances].

let xCurrRec := ID;
let xYear := 'Année';
let zComposer := "-" + Composer_No + "-";
"// Select Medias in production for inventory //";
for i in (select Medias where text(Production_state) = "Inventaire")[contains(Compositeurs_No, zComposer)] do
"// Create a subrecord for each Redevance //";
let x := (create RedevancesLignes);
x.(Redevances := xCurrRec);
x.(Year := xYear);
let yComposer := Compositeurs.Nom;
x.(Composer := yComposer);
let yTitle := i.Titre;
x.(Title := yTitle);
let yPrice := i.'Prix_détail';
x.(Price := yPrice);
let yProrata := i.Prorata;
x.(Prorata := yProrata);
"// *** Calculate each royalties PROBLEM HERE *** //";
let xVentes := sum((select Ventes where Media = i and Hors_redevances = false and year(Factures.Date) = xYear).Nb);
x.(Sales := xVentes);
let xSalesValue := sum((select Ventes where Media = i and Hors_redevances = false and year(Factures.Date) = xYear).Prix_total);
let xRoyalties := xSalesValue / (10 * yProrata);
x.(Royalties := xRoyalties)
end

Indeed, I removed each condition (even rearranged them) without obvious solution (to my eyes). I put a copy of the base here if you have any time to investigate :

<https://drive.google.com/drive/folders/1q-O4BvCV-hjk1N2HiYOc7lmDNAglj1hu?usp=sharing>

Not every composer has sales... so the best is to go to Compositeurs > Lysight > tag Redevances > 2020 > button [Créer redevances annuelles du compositeur] (2 minutes needed)

For example, the sales for "35 Rhythmic Studies" do not appear !

Still investigating...

Thank you for your time and concern!

Indeed, there was the problem! The field looks the same with a negative value and no value at all. It's fantastic to have found this. Thank you very much!
Send me your postal address to info@newconsonantmusic.com. I'd be happy to send you some records... so that you see what you contributed to.
Thank you very much again.