0

Issue with the 'select ... where...' function

Good morning !

I create a personalized address book in Ninox. For each company, there are several contacts and I need to display the names of the company's managers only. Here is my formula:

let directeurices := count(select 'Équipe impliquée' where 'Équipe impliquée'.'Type de fonction' = 1)
if directeurices = 1 then
    'Équipe impliquée'.'Contact lié'.'Nom affiché'
else if directeurices > 1 then
    'Équipe impliquée'.'Contact lié'.Nom end
end

... and I get error messages all the time: unexpected kewyords, table wasn't found, expected symbol, and so on. What do I have to do? Here is a screenshot, too :

Thanks a lot for your support.
Have a nice day!

9 replies

null
    • John_Halls
    • 1 yr ago
    • Reported - view

    You don't need 'Equipe impliquee' twice. Just have

    let directeurices := count(select 'Equipe impliquee' where 'Type de fonction' = 1)

    Regards John

    • Fred
    • 1 yr ago
    • Reported - view

    From the looks of your table, you can also remove the select statement as well:

    let directeurices := count('Equipe impliquee'['Type de fonction' = 1])

    You use the relationship link in lines 3 and 5, so I am guessing that you only want search related records.

    By using the select Ninox looks at the entire table of Equipe impliquee and finds any records with a Type de fonction = 1 not just the one related to the current record in Compagnies.

    • Stef
    • 1 yr ago
    • Reported - view

    Thanks and !
    I tried both of your solutions, but without success :( any other ideas?

    And the other solution :

    Thanks a lot for your answer!

    • Fred
    • 1 yr ago
    • Reported - view

    In a separate formula field does the following work?

    count('Equipe impliquee')

    Can you post a sample copy of your DB?

      • Stef
      • 1 yr ago
      • Reported - view

       

      Thanks for your answer. Your proposition:

      count('Equipe impliquee')

      but:

      count(Compagnie.'Équipe impliquée')

      works well and return the correct number!

      Here are the two tables I'm working with:

       

    • Fred
    • 1 yr ago
    • Reported - view

    Well I guess we should have been clear about asking what table are you starting in. According to your first post, it showed you were in the Compagnie table.

    We did not know that you were trying to do this from the Spectacles table.

    Glad things are working. If you feel like your question was solved please mark the post "answered".

      • Stef
      • 1 yr ago
      • Reported - view

      Unfortunately not... :(

      count(Compagnie.'Équipe impliquée')

      works well, but :

      count(select Compagnie.'Équipe impliquée' where 'Type de fonction' = 1)

      still doesn't work...

      • Fred
      • 1 yr ago
      • Reported - view
       said:
      count(select Compagnie.'Équipe impliquée' where 'Type de fonction' = 1)
      still doesn't work...

       well because you are mixing up how to write selects with relationship links.

      If you want to start in Compagnie then you move 'Équipe impliquée' to after the where.

      count(select Compagnie where 'Équipe impliquée'.'Type de fonction' = 1)

      Imagine that you when you write "select Compagnie" it is saying "start in the Compagnie table". Then where tells Ninox to look at the fields in Compagnie. So now you can start traversing the relationship links in Compagnie.

      Now if we are in the Spectacles table you have a relationship link field to Compagnie so you can use that  link like you did earlier so you only use records that are linked and not all records in Compagnie.

    • Stef
    • 1 yr ago
    • Reported - view

    AMAZING! Now it works, and now I understand what I was doing wrong.
    Thanks a lot for the explanation, it's very clear.
    Have a nice evening!

Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 9Replies
  • 111Views
  • 3 Following