Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Issue with Date Filtering in Table Interface

Topic Labels: Formulas
1747 5
cancel
Showing results for 
Search instead for 
Did you mean: 
asguth
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello friends of the community,

I'm facing a problem when trying to filter the date in the interface. Whenever I select the month it shows me the total, the same happens when I use the specific date filter. Do you know any formula or trick to solve this problem?

Screenshot 2023-08-28 123707.pngScreenshot 2023-08-28 123720.png

5 Replies 5
Valentino_Escal
7 - App Architect
7 - App Architect

Hi @asguth 

I am assuming the month and year is a single line text field type? If yes you can use 'contains' instead of 'is'. That could help depending on what field type is it. If for instance it's a linked field that has more than 1 value, it might not work.

 

As for the date field, you can do the date > is within > this month, it will show all records that are pertaining to this month. Alternatively you can use a date range. The date > is on or after 1/8/2023 and the date > is on or before > 31/8/2023. 

Hope this helps!

Hello Valentino thanks for your reply!

month and year are a formula: DATETIME_FORMAT({Date},'YYYY') and MMMM

Screenshot 2023-08-28 160841.png

Sorry for the lack of information, I'm trying to make an interface page with two tables, Interactions and employees, my client needs to have all employees filtered in this Employees table interface by data, I'm getting the result I need with counting numbers and graphs in the interactions table , this is the result I need to show in Employees Interface table:

Screenshot 2023-08-28 161046.png

I'm not sure if this is possible in the employees table, my question is if it is possible to make a formula to have the same result as above.

 

asguth
5 - Automation Enthusiast
5 - Automation Enthusiast

asguth_0-1693251605298.png

Should I work only with Interactions table? to get the result desired?

Hi @asguth 
If I understand correctly, the first thing you are having issues with the primary field formula in the interactions table? Here is a formula that will fix the primary field assuming it is indeed the Date you want with Type.

DATETIME_FORMAT({Date}, "D/M/YYYY") & " - " & {Type}

As for the second question regarding how to display the data and filter correctly in the interface, there are so many different avenues you can take. Interfaces work great with multiple tables. But you need to decide for each page on the interface what table you want and all the information you can get out of that one table. 

If I can make a suggestion, I would use the employees table as the core as it seems all the other tables all point to employees. Then have linked records with lookup or rollup fields that are important pieces of information pertaining to each employee. So in essence, the answer to your last question is yes it's "possible to make a formula to have the same result as above." I would create a lookup field in the employees table (from Interactions) with month and then in the interface your filter will work. Then on the filter in the interface you can say 'When month > Contains > July 2023.

asguth
5 - Automation Enthusiast
5 - Automation Enthusiast

Valentino Thank you a lot for your help!

I ended up taking a different path and was able to achieve the desired result. Using the 'employees' data didn't give me the numbers I needed, so I looked at the 'interactions' data instead. I applied an IF(FIND("BUD", Type) > 0, 1, 0) formula and for others {types}. At the bottom of the table, the client can see the sum. It was the only way I could manage to do it. If you have any advice, please feel free to share.

No filter:

Screenshot 2023-08-29 173058.png

Filter applied for July:

Screenshot 2023-08-29 173113.png