Question: What kind of field is 'ThisYear'?

Just saw the issue, you can't use 'this' in a search. So you would have to change to:

let currentRec := this;<--assuming you are in the Composer table
let xYear := last(((select Royalties where Composer = currentRec) order by number(ThisYear)).ThisYear) + 1<--you can drop the number for ThisYear since it is already a number

Now it makes sense why you got a 1 as a result. You found no results with 'this' and then you added 1.

Since there is a relation between the two tables, a "select" statement is not needed. You can just use the reference. If you have not modified the name of the 1:N reference field in the "Composer" table and kept its default which is simply "Royalties", you can simply write:

   let xYear := max(Royalties.ThisYear) + 1;

   let xSolde := last(Royalties order by ThisYear).UnpaidAmount

To be clear: in this formula, "Royalties" is the name of the reference field, not the name of the table, they just happen to be the same because Ninox does it that way by default.

Alain brings up a good point, Ninox does not like "a lot" of select statements. How much is "a lot", you won't know until your DB starts to take a long time to load a table. I use the MacOS app so I may hit the wall sooner than the cloud version.

Always good to start thinking in how to use existing relationships to get to data in other tables then always using select statements.

I had to learn this the hard way. I have a sports league db and of course figuring stats is important, so I hit that wall early and had to re-design my db to speed things up.

That's the idea... Now, in this particular case, being inefficient would not be a big problem, since the script is to be run once per year and per record in the "Composer" table, and one would not expect to find many records of the "Royalties" table linked to each "Composer" - unless, of course, if the database has been in use for a few thousands years. If, in a similar case, more optimization would be needed, I would propose:

let currentRec := this;
let lastRoyalties := last(Royalties order by ThisYear);
(create Royalties).(
   Composer := currentRec;
   ThisYear := lastRoyalties.ThisYear + 1;
   ReportedAmount := lastRoyalties.UnpaidAmount
)