0

Extract Record from a Table meeting some criteria

Hi to all,

 

i have a table called Album, with a relationship with another Table Called Release (1:N)

Each album could have 0, 1, or multiple Releases

i am trying to extract in a formula field the name of the album which has the wMax amount or releases connected on it.

 

I am trying with this formula:

 

let maxCount := 0;
let albumName := "";
for i in Album do
    let releaseCount := count(i.Releases);
    if releaseCount > maxCount then
        maxCount := releaseCount;
        albumName := i.Album
    end
end;
albumName

 

but I get no response. Any suggestions?

 

Thank you in advance

3 replies

null
    • Fred
    • 1 yr agoSat, August 5, 2023 at 1:34 PM UTC
    • Reported - view

    When I copy your code into a test DB I get an error at i.Releases and i.Album. To correct that I had to fix line 3:

    for i in select Album do

    I'm guessing you wanted to go through the entire Album table so you need to use a select statement. Otherwise Ninox assumes you want to loop through the field Album.

    • John_Halls
    • 1 yr agoSat, August 5, 2023 at 4:03 PM UTC
    • Reported - view

    We like a one liner, this might work

    last((select Album) order by count(Release)).Album

    Regards John

    • francescostefanello
    • 1 yr agoSat, August 5, 2023 at 8:10 PM UTC
    • Reported - view

    Fixed, thank you a lot

Login to reply

Content aside

Categories

View all topics
  • Status Answered
  • 1 yr agoSat, August 5, 2023 at 8:10 PM UTCLast active
  • 3Replies
  • 90Views
  • 3 Following