0

Can I use a "sum" function to add up just the items found through a search?

[Bear with me, I'm still new at anything but the most basic ways to use Ninox.]

Here's my case: I've got a simple db set up to catalog a comic book collection. The first 2 images show how I usually view the data.

The field "value_av" is a rough estimate of each item's value.

The field "sum_av_value" calculates the total value of all comics in my inventory. That formula, shown in the 3rd image, is: 

    sum((select collections).value_av)

It works like a charm, and gives me the current total estimated value for everything entered into the db so far.

So here's my question: Is there some way to use this function to see the value for a *subset* of the inventory? I tried clicking on a table heading (as in ex 1 above) and then using the Filter to just bring up, for an example, "Rex the Wonder Dog". As expected, I then saw a table that only displayed "Rex the Wonder Dog" comic entries. But the "value_av" field still showed the sum for all the entries in the db, not for just the subset I was seeing in the table.

An easy way is preferable, but a less-easy way would still be okay, since eventually I'll want to know totals for just "Lone Ranger" comics, or just "Donald Duck" comics, an so on.

Thanks in advance for any suggestions!

2 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    You have two options:

    1) You can use the table form (all) in your collections table. You can display the field value_av. Then you can sum the value_av column. Then you can filter the table and you will see the sum of only the records you have filtered.

    2) You can create a dashboard. You can watch this video for some background.

    One comment:

    I'm guessing people can be an author in one comic and editor in another and an illustrator in yet another and sometimes they can wear multiple hats in the same comic or you can have multiple people in the same role on the same comic.

    You may want to consider creating two tables one called People and the other Artists. You would then make Artists a child of collections. You would then put in the names of the authors, illustrators, and editors in People so you don't have to retype people's name. That means you would need at least two fields (firstName and lastName). For the Artists table you would need the two reference fields (1 to collections and 1 to People) and a choice field (role) with author, illustrator, and editor as choices.

    Then when you add data to a new comic you will see a view to the Artists table, you will add a new record, select the Person, then select their role. You would repeat this for each role.

    What you have done is create a N:N (many to many) relationship table between collections and People with the Artists table.

    • Fred
    • 1 yr ago
    • Reported - view

    I actually didn't answer your question. To take your formula:

     sum((select collections).value_av)

    and to then filter it you can add the following

     sum((select collections)['title or name' like "Rex the Wonder Dog"].value_av)

    The [ ] is the best way to learn to filter select statements or arrays. You can use "where" if you have a select statement:

     sum((select collections where 'title or name' like "Rex the Wonder Dog").value_av)

    But that doesn't work if put the results of a select into a variable and tried to filter it or if you wanted to filter a relationship link.

    let x := select collections
sum(x where 'title or name' like "Rex the Wonder Dog").value_av <- will not work

sum(x['title or name' like "Rex the Wonder Dog"].value_av) <- does work

    Since you have hard coded the title name this formula will only work for that one title (or anything that has those words in it).

    To make it more flexible you look into dashboards like I talk about below.

Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 2Replies
  • 145Views
  • 2 Following