Birding database
I have created a simple database on my iPad that contains a list of certain of my sightings of birds. Each sighting will have a Date and a Country as well as the species name.
I am using a View within a dashboard to view records. I want to be able to extract two different sets of data from my records:
1. A list of all sightings filtered by species and/or date. This is simple and allows me to see all the Countries in which I have seen a specific bird either over all time or over a single year. I have no problem with this part.
2. A list of unique species seen. The Date and Country are irrelevant. I only want each species listed once. I have tried using the ‘unique’ operator in various ways without any success. Can anyone help me out? Many thanks..
9 replies
-
Can you post a sample DB?
-
Not quite sure how to do that. I’ve attached an archived version.
-
said:
Not quite sure how to do that. I’ve attached an archived version.that will work.
so you have this in your view element:
select 'Sightings revised 1' where unique(Species)
Sadly, Ninox does not work that way. I'm not even sure how Ninox would think this would work. I'm also surprised that it didn't throw any errors.
If you wrote:
select 'Sightings revised 1' where Species
It is asking Ninox to find records where the Species field has data, basically a true/false (boolean) statement. So adding unique() doesn't do anything to this boolean check.
So a bit of background on view elements. It is a field that allows you to show records from another table. Since your table 'Sightings revised 1' is based on sightings you where you can have the same species across many sightings, you have some options:
1) write a script that finds the first/last instance of each species and only show that record. The problem with this is that if you click on the record you are looking at just the first instance.
2) you can create an html table that will take the data from 'Sightings revised 1' and reconfigure it so it will show a table of your desired data.
3) to make your data entry easier I would suggest you:
a) create a table of species (and if necessary a subtable for subspecies) that can store countries that they can be found and any other relevant data, e.g. the total number of sightings
b) create a table of countries
c) create a Page where you can select a country, then select the appropriate species/sub species, date and any other relevant data then have a button that then creates the record in the 'Sightings revised 1' table.
d) then from these new tables you can easily have a view element that shows you the species list.
-
Fred,
Thanks for your very comprehensive reply. There’s a lot of work there for me to get my teeth into! Hopefully I can get something to work.
Again, many, many thanks for your time and effort.
-
Hi, for a simple and quick solution, put the following code inside a formula field on your Dashboard:
join(unique((select 'Sightings revised 1' where Date1 != null and length(Country1) > 0) order by Species).Species, " ")
Let us know if this is the outcome that you would like.
-
Thanks for the suggestions but neither seem to work for me. I have found the simplest solution is to just Group the records by Species.
I now need to count the number of Groups. NOT the total number of records which selecting Count in the field header reports. Any suggestions?
Thanks again!
Content aside
- 3 mths agoLast active
- 9Replies
- 67Views
-
3
Following