0

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

null
    • Fred
    • 7 mths ago
    • Reported - view

    Can you post a sample DB?

    • SimonS
    • 7 mths ago
    • Reported - view

    Not quite sure how to do that. I’ve attached an archived version.

    Unique.ninox
    • Fred
    • 7 mths ago
    • Reported - view
     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.

    • SimonS
    • 7 mths ago
    • Reported - view

    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.

    • szormpas
    • 7 mths ago
    • Reported - view

    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.

      • Fred
      • 7 mths ago
      • Reported - view

      to be helpful like, , here is a sample html. Put the following into a formula field.

      let getSightings := (select 'Sightings revised 1');
let uniqueSpecies := unique((getSightings order by Species).Species);
let data := for loop1 in uniqueSpecies do
        {
            species: loop1,
            count: count(getSightings[Species = loop1])
        }
    end;
html("
<table>
<thead>
    <tr>
        <th>Species</th>
        <th>Count</th>
    </tr>
</thead>
<tbody>" +
data.("<tr>
        <td>" +
species +
"</td>
        <td>" +
count +
"</td>
    </tr>") +
"
</tbody>
</table>
")

      Always try to make a few select statements as possible, so line 1 is where we get all of the data from 'Sightings revised 1' and put it in a variable.

      Line 2, we then create another variable of just the unique values from the Species field using the variable from line 1.

      Lines 3 - 8, we are using the for loop command to create a JSON table that is organized by species and where we count the number of times each species shows up in the table by using the variable from line 1.

      Lines 9 - 29, is where we use the html() function to build a quick table that shows us the data we got above.

    • SimonS
    • 7 mths ago
    • Reported - view

    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!

      • Fred
      • 7 mths ago
      • Reported - view

      If you had the table of species then you can do a simple count() or length() of the table.

      Or you can modify the html code I posted earlier and add a few lines to get a count of species:

      let getSightings := (select 'Sightings revised 1');
let uniqueSpecies := unique((getSightings order by Species).Species);
let data := for loop1 in uniqueSpecies do
        {
            species: loop1,
            count: count(getSightings[Species = loop1])
        }
    end;
html("
<table>
<thead>
    <tr>
        <th>Species</th>
        <th>Count</th>
    </tr>
</thead>
<tbody>" +
data.("<tr>
        <td>" +
species +
"</td>
        <td>" +
count +
"</td>
    </tr>") +
"<tr>
    <td>Different Species: " +
count(uniqueSpecies) +
"</td>
    <td></td>
</tr>
</tbody>
</table>
")

      If you scroll to the bottom of the field you will see:

      Different Species: 689

      • SimonS
      • 7 mths ago
      • Reported - view

        Perfect! Works a treat. Thanks so much.

Login to reply

Content aside

  • 7 mths agoLast active
  • 9Replies
  • 72Views
  • 3 Following