0

How to select a row from a composition table to display in a superform

I am new to Ninox and am struggling a little with programmatically searching records - the equivalent of SQL-world Select statements) . I have many years of relational database experience in SQL-based platforms and coding in various languages, but have yet to grasp the Ninox scripting paradigm in this regard.

As a learning exercise, I have a "People" parent table that is 1:N related (composition) to a child "Events" table. In Events there are EventDate and EventType fields. The EventType field is a Choice field with "Birth", "Death", "Marriage" and "Divorce" values. Events records associated with a People record are not forced to be unique as there could actually be multiple marriages and divorces - we can assume a manually enforced single Birth and Death event. I would like to display the EventDate associated with a "Birth" EventType in a Function field on the People record form .

Thanks for any help and I'm looking forward to understanding Ninox better

7replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Fred
    • Fred
    • 5 mths ago
    • Reported - view

    Hi Rob -

    When you created the reference field in Events to People, Ninox should have created a similar view field in People to Events. Do you not see the field in People? It will show you all records that are related in Events. From there you can choose which fields to display as columns. If you edit the view field then you can click on Edit Columns then select which fields you want to display.

    If you want to put EventDate into a formula field then you can do something like:

    Events.EventDate
    

    There is no need for a select statement as you already have created a relationship between the two tables.

    This line says find all records in Events that are related to the record in People and show the data that is in the field EventDate.

    Now the issue is Ninox will return an array, even if there is only 1 related record, so it will show:

    Feb 1, 2021, May 23, 2019, Apr 21, 2001, etc.

    Now you said you want to only show births. You would modify the above to say:

    Events[EventType = "Birth"].EventDate
    

    The [ ] is Ninox's way of filtering through reference fields. On a side note you can also use them in select statements.

    The above only works if EventType is a simple text field. I'm guessing that it is probably a choice or dynamic choice field. If so then that will change things.

    I'll stop here and you can follow up.

    Like
      • Rob Young
      • rob_young
      • 5 mths ago
      • Reported - view

      Fred Thanks for the response. Events do show up in the People record. That part works great. I put "Events[EventType = "Birth"].EventDate into a People formula field and got no result. You mention the approach would be different if the EventType field is something other that simple text - it is a Choice field. What is the proper way to filter on a Choice field?

      Like
    • Fred
    • Fred
    • 5 mths ago
    • Reported - view

    When you created your choice field choices you will see that they have a number next to each choice. By default Ninox uses that number to reference choices. So if Birth is number 3, for example then it would change to:

    Events[EventType = 3].EventDate
    

    But like many of us, you don't remember the order so you want to use the text that is entered for each choice. Then it would change to:

    Events[text(EventType) = "Birth"].EventDate
    

    The text() command tells Ninox to treat the data in EventType as text so we can search using words.

    Like
      • Rob Young
      • rob_young
      • 5 mths ago
      • Reported - view

      Fred Your explanation made sense and I had high hopes. Unfortunately, neither approach (by row number or text value) returned a value to the People function field. Does it matter that I am running Ninox on my Mac?

      Like
    • Fred
    • Fred
    • 5 mths ago
    • Reported - view

    I too use the Mac App. Ninox does a good job at keeping the versions in sync, so there is not any functional difference between them.

    I was going to write something else, but then I went to my test DB and tried out using a Date field and I see what is going on.

    I too did not get a result if I used:

    Events[text(EventType) = "Birth"].EventDate
    

    In my testing I used a simple text field, so that worked. But when I switched it to a Date field then the field was empty.

    It looks like Ninox is waiting for you to tell it what to do when it you ask to return a date field in an array. Remember I said Ninox always returns an array, even with only 1 result, when doing a select or when you go down a link. Well I forgot that Date fields cause Ninox to not return a result until you use an array function like first, last, sum, concat, etc.

    So you will have to choose an array function that matches your situation. If you only want the first record you would modify it to say:

    first(Events[text(EventType) = "Birth"].EventDate)
    
    Like
      • Rob Young
      • rob_young
      • 5 mths ago
      • Reported - view

      Fred Perfect! Really appreciate your help on this.

      Like
    • Fred Hi Fred, this may sound pedantic but anyway. Ninox is not shy of producing arrays of dates. BUT, it does not provide a default way to display such an array, like it does with arrays of texts (text1,text2,text3,…). So, in the formula field, the array of dates is indeed present, but one does not see it. This is definitely not intuitive, because Ninox does have a default way to display dates, and one wonder why it does not use it to display arrays of dates. This explains why the result becomes visible as soon as a single value is extracted from the array, which was the right thing to do in this case.

      Like 1
Like Follow
  • Status Answered
  • 5 mths agoLast active
  • 7Replies
  • 215Views
  • 3 Following