How to Reflect Filtered Records on One Table to Lookup in Another?

Scott_Bleackley · ‎Jan 20, 2017

How do we apply a filter ( say January 1 - 31st in a column “Dates” ) in table “Services” for example, and then have this filter restrict records collected by Lookup in a table “People” in a column called “Service Dates”?

Currently when I do such a lookup all of the “Service Dates” and “Service Hours” all existing records are collected. This makes it very difficult to generate an invoice for a specific time frame.

Perhaps I am approaching this incorrectly.
Services Table has a record for each service provided.
People Table has a record for each client
We need to generate monthly invoices and so need to gather data from only the date range required for line items and create a total charge.

Any thoughts are most welcome,
Thank you

Howie · ‎Jan 20, 2017

Hi Scott, this support article will give you an idea of how to accomplish this:

Conditional rollups

By default, a filter applied in one table does not affect a rollup in another table. A rollup field will roll up data from all records in the target linked table, regardless of whether or not those...

Howie · ‎Jan 20, 2017

Alternatively, you may be able to simply create a grouped records view on the services table, but then group it by person. You can then perform a filter on this table to only show services performed during a particular date range.

Scott_Bleackley · ‎Jan 21, 2017

Thank you for the very quick response.

What I did:
• In service tracking add a columns “Month”, “Month Service Hours”, & “Month Service Type”
• if date of service is the 1st month, ie: January, then put date of service in column, otherwise put empty.
• Formula IF AND ( ( 1 = Month( {Date of Service} ), 2016 = YEAR({Date of Service})), DATESTR( {Date of Service} ) )
• This works but “Date of Service” results in Date Time, hence DateStr
• Lookup only finds Columns that have a value in them and ignore the empty.
• Same formula just change the last element to be output to hours and {Type of Service}
• Unfortunately this formula will have to be added to “Month Service Hours” and “Month Service Type” as well.
• Each month, the month number in all three formulas will have to be updated if this is to work, not a great interface mechanism : (
• Update “Intake data” table Columns to point at the new columns “Service Tracking” table.

Any suggestions are most welcome : )

Scott_Bleackley · ‎Jan 21, 2017

Feature Request: Based on this suggested solution.
• In a link, allow the option to use current filters applied to the target table • also allow selection of a view on the target table as a view can retain filter settings. • This would leverage the use of filters to provide a much clearer interface structure and
avoid the additional columns and clunky formulas with embedded variables that must be updated on a periodic basis to achieve the same goal. This would be better achieved by optionally enabling the use of current filters on the
target table for the link and further enabling the selection of a view on that table.

Perhaps there are underlying architectural concepts that I have yet to understand with regard to AirTable. But, the solution that I implemented is not elegant. An Elegant implementation reduces complexity to those creating and using airTable elements making a better, and more robust and malleable solution.

Any suggestions are most welcome : )

Scott_Bleackley · ‎Jan 21, 2017

Thank you Howie for both responses.

While this approach provides the data it does not do so in a way that is conducive to creating a downloadable CSV file that can be run with a mail merge to create invoices.

Would be very nice if we could do this internal to AirTable, but till we get there this appears to be the simplest approach.

Any Suggestions are most welcome.

Scott_Bleackley · ‎Jan 21, 2017

Bug Report: Related to Second Suggestion by Howie.

Print and Printing of Groups Does not show summary detail numbers ( see below ) working in macOS current version & Safari.

Also would like to accumulate such totals for individual groupings of various columns into a single view. e.g.: Number of shirts by color, Number of Shirts by Size, Number of Shirts by Length. Is there any way to access the summary information to do this?

I notice that when I export to csv files the summary information is similarly missing.

In Groups: Expand and Collapse does not appear to enable Summary Information for more than a single column. That is when collapsed only the first grouping is summarized. I could be nice if you could expand and collapse incrementally.

Print Output

On Screen View in AirTable

Any suggestions are most welcome
Thank you

Thomas_Dale · ‎Jan 12, 2018

In case anyone else comes to this conversation also looking to rollup by date range, you may find useful the guidance on with formulas and date ranges here: Referencing a date range in formulas