0

Filter by date in a sub database

Good evening, I would like to know how to filter on a table that uses a subtable. For example, the one that groups the total accounts cannot be filtered by month, or quarter, or year. How can it be done? Thank you

7 replies

null
    • Fred
    • 1 yr ago
    • Reported - view
     said:
    For example, the one that groups the total accounts cannot be filtered by month, or quarter, or year. How can it be done?

    First you probably want to create month, quarter and year formula fields in your subtable.

    Since you want to show data from a subtable and that subtable will most likely have multiple records you need to figure out which record you want to show in the table view of the main table. So how do you know which record you want to show?

    • alfonso_mino_arribas
    • 1 yr ago
    • Reported - view

    I have data in the subtable for 2016, 2017, 2018 and I want it to show me the data for a year, for example 2018.

      • Fred
      • 1 yr ago
      • Reported - view

      Your picture shows the Accounts table. Is the subtable in question, a sub of Accounts?

      Do you want to show the year data of the subtable in the Accounts table view?

      Can you post a sample DB?

    • alfonso_mino_arribas
    • 1 yr ago
    • Reported - view

    In accounts database, I need to show only amounts of 2016 (from accounting database) for example. I can not filter by year because it is a sub table.

      • Fred
      • 1 yr ago
      • Reported - view

      Just to be clear, 1 record in Accounts is linked to many records in Accounting.

       said:
      In accounts database, I need to show only amounts of 2016

      You try something like this in a column formula field or in a regular formula field:

      concat(unique(Accounting.year('Entry Date')))

      First we use the year() command to get the year of the 'Entry Date' field. Since we are accessing a subtable we could be getting many itertations of the same year, so we add the unique() command to get rid of duplicates. Then we have to use the concat() command to put remaining data into a string so it will show in table view.

      Now you can filter by year.

      But this will only show you account records that have records in 2016. No other data in the view will change.

      • alfonso_mino_arribas
      • 1 yr ago
      • Reported - view

       thanks for the response. What I need is the balance sheet for 2016 and it is many years old. Let the numbers for 2016 come out, for example. That's why it was filtered by year. sorry about my english

    • Fred
    • 1 yr ago
    • Reported - view
     said:
    What I need is the balance sheet for 2016

     Is that what the Balance Sheet table for?

    can you upload a sample db? it would make it easier to figure out what you want. 

Login to reply

Content aside

  • 1 yr agoLast active
  • 7Replies
  • 74Views
  • 2 Following