0

Sub table query - limit to current record

I have a table -> Event Details and a subtable - Event Expenses. 

Each event can have expenses of different types, there is a "Type" field in the expense table. I want to calculate the total spend on a specific type of expense but only for the Event Details  record that is open. 

sum('Event Expenses'.Amount)  gets me a total of all expenses. I assume I need a Select statement to narrow down by type, but I can't figure out the right syntax to make it work. 

How can i summarize all of the expenses with a Type = 10 but only for the current Event Detail record in a formula field? 

3 replies

null
    • John_Halls
    • 3 mths ago
    • Reported - view

    Hi James

    No need for a select statement, you can scope with square brakets

    sum('Event Expenses'[Type = 10].Amount)

    Regards John

      • Firefly Team Events
      • James.3
      • 3 mths ago
      • Reported - view

      Well that is much easier. Thanks!

    • John_Halls
    • 3 mths ago
    • Reported - view

    Hi James

    You have hit the nail on the head. The language has been designed so that almost everything in Ninox has a simple solution. As soon as you feel like you are going down a rabbit hole it's time to stop and think again, or reach out to the forum.

    It is low code after all.

    Regards John

Login to reply

Content aside

  • 3 mths agoLast active
  • 3Replies
  • 32Views
  • 2 Following