0

Draw records from a table based on selection criteria and summarize

I have 3 tables:
1. Profit table (parent): fields for start date, end date, description.
2. Expenses table (child): fields for date, amount, description.
3. Income table (child): fields for date, amount, description.
How do I show in the Profit table the profit: income - expenses?
Of course I could link each Expense record and Income record to a Profit record. But I would like the flexibility for different time periods (half year) or other selection criteria (such as type of expenses and type of income). So I would like the flexibility to draw records from the Expenses and Income tables based on flexible selection criteria and show a summary in the Profit table.
Is there a post/thread that explains such a setup?

1 reply

null
    • Nick
    • 4 yrs ago
    • Reported - view

    My model is

    New Table e.g. Report

    Reference to data table e.g. Category

    Date field: DateStart

    Date Field: DateEnd

    View: e.g. Results 

    Code for Results:

    --

    let t := this;
    let a := Category;
    select Journal where Category = a and DateStart >= t.From and EndDate <= t.To

    --

    So, every record in this new table will be a report that period.

Content aside

  • 4 yrs agoLast active
  • 1Replies
  • 888Views