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
    • 3 mths 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
    • 3 mths 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
      • 3 mths 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
    • 3 mths 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
      • 3 mths 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
      • 3 mths 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
    • 3 mths 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

  • 3 mths agoLast active
  • 7Replies
  • 49Views
  • 2 Following