Formula values not appearing correctly in table view

Leah
6 days ago
7replies
Answered

I have a few formulas in a "People" table that sum up various amounts from another table. These amounts appear correct on each record in Form view. When I look at the data in Table view, the amounts are sometimes incorrect (ie $0). The values in Table view also seem to change when I make unrelated changes to the record - I added another item to the view and the dollar amounts in the formulas either became $0 or became the correct amount.

The formula's seem pretty standard to me, and all are roughly the same:

Donation Formula

let myID := Id;
let x := (select Splits where People = myID and 'Class (Revenue)' = 6);
sum(x.Amount)

I've tried exporting the table to see if the formula values are coming through, but no luck.

I'm stuck as to what I'm missing, or if there is a bug.

- Fred
- 6 days ago
- Reported - view
can you post a sample DB with no personal info?
- Leah
  
  6 days ago
  
  Reported - view
  @Fred Here's a scrubbed test version TestDB (1).ninox
- Fred
- 5 days ago
- Reported - view
Try changing Donations to:

let x := Splits['Class (Revenue)' = 6]; sum(x.Amount)

Try to use reference links whenever possible because it is easier for Ninox to just go through the linked records then to do a select.

For example record 92, only has 1 record related to in Splits. So if you do a select then Ninox has to go get the 45K records from Splits then filter for that 1 record that matches the People and checks to see if 'Class (Revenue)' = 6. Then repeat that for each of the other 91 records in People.

Now if you use the reference field (Splits), then Ninox knows to look at the 1 record linked to 92.

Once I did that then the table view showed the correct info.

Just a thought on your DB.

Your 5 connections tables could be consolidated into one connections table that has a choice field that separates Pets/People, Pets/Pets, People/People, etc. It seems like the main job of the table is to show the connection between entities. To help facilitate that, a child table called Entities would be created as well to list the entities that are connected. This way you can link two entities or three or 100.
- Leah
  
  5 days ago
  
  Reported - view
  Thank you! I've only ever done it via the select function so this is a bit of a revelation for me. I'll need to poke around a bit with consolidating the connections tables, but I think I see what you mean. Thanks a bunch!
- Fred
  
  5 days ago
  
  Reported - view
  I did the same thing in the beginning as well. It is all part of the learning curve.
  
  I did a post about how my data model has changed through the years.
  
  Somethings to keep in mind:
  
  1) only create a new table when you are tracking a different set of data.
  
  2) use as few selects as possible.
  
  3) don't mix your reports with your raw data tables and you might think about summary tables as well
  
  4) if you find yourself creating multiple instances of a data field, e.g. phone1 and phone2 or employee1 and employee2, then it is probably time to create a child table to track the multiple instances
- Leah
  
  5 days ago
  
  Reported - view
  That's awesome, I will definitely check it out. Sadly I've been using the program for many years...but have never been pushed into more efficient models as, until recently, I didn't really need it. With the new organization I founded I find I need more complex things than I've needed before. So I will learn!
- Fred
- 4 days ago
- Reported - view
If it works for you please mark the post "answered" when you get a chance. :)

Welcome to the Ninox community

Formula values not appearing correctly in table view

7 replies

Content aside

Categories

Overview