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

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.

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.

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)