Filtering data based on month from Multiple columns

[Dilip_Adiga]

I have two tables taken from airtable “Conversations_Brand” and “Invoices_Jan”
Both of them have a Created Data column in them.

“Conversations_Brand” also has a dropdown called status which has two option “Confirmed” and “Negotiation”

I have another View called Dashboard which gives me the total of filtered data from Invoice and Conversations based on status.

Right now I am able to get the totalled data from these tables based on selected status for particular user.

I Will place a range picker in my Dashboard screen. how do I get to pick data from multiple sources

Would it work if add one more filter in my query data type which would specify range from and end date along with status filter ? What do you guys recommend?

[Dilip_Adiga]

Do I need to convert the Start date in each of the table “Conversations_Brand” and “Invoices_Jan” into the mathematical formula for Month , and year? or is there any simpler method?

[Dilip_Adiga]

https://www.youtube.com/watch?v=AVSavEhdt6Y I have followed this tutorial. I have got filter working. But how do I make the change to get the selected status values total from the table with name “Conversations_Brand”

[ThinhDinh]

Date Formula Help

Here is a loom that may help get you started without doing anything too fancy. This uses a second table using the query column to bring in bills that fall within the date range. Again, not a full solution but might get you started.

I think here’s a better approach with the query column, since Robert’s video was made before the query column becomes available.

Can you show us some screenshots of your data structure and what you have set up so far?

[Dilip_Adiga]

Here is my data structure for both tables

[Dilip_Adiga]

this example shows fetching values and sorting it based on range from singular table. I would want to do it from multiple tables. I am a little confused as to how to achieve that.

[Dilip_Adiga]

Any help from any members of glide community? @Darren_Murphy

Thank you in Advance. Appreciate it as always

[Dilip_Adiga]

@Jeff_Hager could you please help me out here?

[Darren_Murphy]

@Dilip_Adiga

this example shows fetching values and sorting it based on range from singular table. I would want to do it from multiple tables. I am a little confused as to how to achieve that.

Do you actually want to combine the data from both tables, or do you just want your filtering to be applied to both tables?

If the latter, all you need to do is add two single value columns to each table. One that takes each of your user specific start and end dates. You can then use these in a query column into each table, and finally do your rollups through the queries.

If the former, possible but complicated. If you really want to combine filtered data from two tables, then you probably should consider refactoring to combine the two tables permanently.

[Dilip_Adiga]

I just want filter to be applied to both tables.

“Conversations_Brand” and “Invoices_Jan”

but in “Conversations_Brand” It needs to be for each status in Status dropdown.

So you are suggesting we dont need to convert the stored date into a more numeric format like how the video suggests (The one I shared here)?

[Darren_Murphy]

@Dilip_Adiga

So you are suggesting we dont need to convert the stored date into a more numeric format like how the video suggests (The one I shared here)?

No, not necessarily. In fact I’d probably recommend that you do. Converting dates to numbers is always a good idea when doing this sort of thing.

[Dilip_Adiga]

you are suggesting -

in my “Conversations_Brand” table I add startDate and endDate after they are converted

Ill be using this formula for both
year(date)*10000+month(date)*100+day(today)

This values need to be added as single value columns in both tables

convert the Date of the row using same above formula and query it ?

and “Invoices_Jan”

Also we do need a temporary work table as suggested in the video right? that’s where we fetch the combined values from

[Dilip_Adiga]

these are my columns as specified

and this is my query , still nothing appears?, am I doing something wrong?

[Dilip_Adiga]

I just realised that this formula for converting date is wrong.

Ill be using this formula for both
year(date)*10000+month(date)*100+day(today)

Could you tell me an accurate formula that I can use?

Also I did the roll up on the query column and got the output.

Now I’ll do the same thing on the other table. How can I get the output from two different tables into one view. Isn’t that something we cant do in glide?

@Darren_Murphy

[Darren_Murphy]

The formula you have is okay, I usually write it as follows:

Year(Date) * 10^4
+ Month(Date) * 10^2
+ Day(Date)

The above gives the same result, but takes less brain power because you don’t have to count the zeros

Just taking a step back…

@Dilip_Adiga

Ill be using this formula for both
year(date)*10000+month(date)*100+day(today)

This values need to be added as single value columns in both tables

convert the Date of the row using same above formula and query it ?

and “Invoices_Jan”

Also we do need a temporary work table as suggested in the video right? that’s where we fetch the combined values from

You are kind of on the right track, but not quite there.

• Firstly, you need two columns to store the user supplied start and end dates. These columns could be anywhere, as long as you can target them from the current screen. I usually have them either in the User Profile row, or in a single row helper table. If you put them anywhere other than the user profile row, then they must be user specific.
• Next you need two math columns to convert each of those to numbers in YYYYMMDD format, using the above formula.
• In your data tables, you need the same math columns to convert the date on each row to the same format
• Now add two query columns to the same table where you are capturing the start/end dates. Each column should target one of the data tables, and use the following filters:
  – Start Date Math is equal to or less than This row->Start Date Math
  – End Date Math is equal to or greater than This row->End Date Math
• Now you should be able to do your rollups through the two query columns.

[Dilip_Adiga]

Hi Darren,

I could query and get the required output in two tables namely “Conversations_Brand” and “Invoices_Jan”

In those I have rollup for all the values stored.

Since these are in two different tables how do I access both tables from the Singular UI? Glide doesn’t allow it right?

Sorry it might be a stupid question to ask

[Darren_Murphy]

You just need to get the values in the table/row that your screen is attached to. There are any number of ways to do that. Try a single value column.

[Dilip_Adiga]

Thank you @Darren_Murphy you have been a great teacher. Thank you for your patience.

Looking forward to many more doubts when they arise.