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
7 replies
-
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)
Content aside
- Status Answered
- 2 yrs agoLast active
- 7Replies
- 700Views
-
3
Following