0

[HELP] Ninox Can't Perform Basic "Sumif" Function

I have two tables: "Orders" and "Line Items." The "Orders" table records the Orders, while the "Line Items" records the items ordered associated with the Order ID from the "Orders" table.

In the "Orders" table, I have a Formula field that computes the "Item Cost" from the "SKU" in the table "Line Items".  This would be just a simple "SUM IF" function if it was from Excel. So the code that was put behind the formula field is as follows:

let curRec := this;
sum((select 'Line Items' where SKU = curRec).'Item Cost')

The code above returns the correct value, which is great!

However, I can't use the data from this field functionally. I have another field, "Order Cost Total" which is just the value obtained from the above formula field, multiplied by the number of items ordered in the "Orders" table. So, suppose the formula field that contains the code above is named "Order Cost" and the number of items ordered is named "Num_Items", the formula is as follows:

'Order Cost'*Num_Items

Problem is, even if, say Order Cost has a computed value, and Num_Items also has a value, the formula field still will return a 0 value. Please refer to the attached screenshots for this.

Also, I created another field, "Item Cost Per Unit" which is a formula field whose formula is just simply "Order Cost" so it should display the value computed by "Order Cost". But, it displays a zero value, despite "Order Cost" having a value.

The only solution that I have for this is to manually write a value on any field, or create a "REFRESH" button that "refreshes" the whole table entry, and only then will the value appear. But, we have hundreds of items and orders! Worse, I have to do this EVERY SINGLE TIME I open Ninox. Does this mean I have to click over 100x buttons every time I open Ninox just so I can use this?

This seems ridiculous given this can be easily done by a spreadsheet. Is there a right way to do this? 

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • You can shorten the first part like:

    sum('Line Items'.'Item Cost')

    since the line items is a child table.

    If this gives the right result but the second doesn't, then for the second formula try :

    number('Order Cost') * Num_Items

    Only thing i can think of is that when you use a format with a currency symbol, the field isn't  evaluated as a number anymore.

    Steven

    Like
  • I had a similar problem.

    Try this:

    do as server
        sum((select 'Line Items' where SKU = curRec).'Item Cost')
    end
    
    Like
    • Borgir! So.. Did that work?

      Like
Like Follow
  • 6 days agoLast active
  • 3Replies
  • 50Views
  • 3 Following