Filtering data - 2 unclear issues. Filtering lookups and filtering data/dates

[Brendan_Woithe]

Hi all,
I’m REALLY determined to get my head around filtering. It is going to be extremely important in making my docs to help my small business work for all 5 staff!

I’m using my simple CRM setup as an example, but this same issue is stalling me in a bunch of little apps.

Issue 1 - (the easy one i think ) : Filtering options in a lookup based on a second column. I’ve read help / threads about this, but for some reason just don’t understand the logic / formulas enough to get it working properly and with understanding.

What I want to do is : In the table CRM Activities I would like the lookup in Column Company to only show (only allow me to select) from companies who are either “Targets” or “Re-Targets” - which is defined in column 2 in the table Company Example. (Excuse the poor naming - threw together the example very quickly!)

It should be incredibly simple. I’m sure I’ve got it working many times. I guess my head is so caught up in solving part 2 (below) that this one has just escaped me.

EDIT : Issue 1 is Solved. Was indeed incredibly simple. See reply for info so others don’t go down the same rabbit warren!

Issue 2. Filtering down data.

What I’m trying to achieve : I’d like a view / table (whatever works at this point in time - but I’m guessing a view is technically the best way to achieve this) that shows ONLY one row for each company with associated data in CRM Activities. I want that row to be the row with the latest date in the Followup column

So - from my example
Problem 1 : Company C should not be able to be selected
Problem 2 : Company B should only be listed once, and that should be row 5, as it has the latest date in the followup column.

I’m hoping from that point, I’m able to continue to filter down - only showing rows which have a followup date that is in this week. (hints appreciated!)

Bonus question - and this is a big bonus which I cannot see being possible, but maybe only as I don’t understand the massive power that coda has…

When a staff member presses a (still to be made) button in a column of the CRM activities table, it populates another table below with ALL the entires of the CRM activities table which include the company of the row who’s button was pressed. So if I press the button in row 1,2 or 5, it would have the same result of showing me a table below with 3 rows, showing all the activity for that company.
Now, I can KINDA do this already in my real company table (not shared as it has real info in it!) - where I pull in all the associated activities for a company. But my ask above just makes the UX for the user a bunch better / smoother for our use case. At least I think it does

Thanks all.

[Brendan_Woithe]

Issue 1 solved.
Was so incredibly simple - I was just not finding the right info / saw some old info / was just not on the right path. Funnily enough, I’d got it working in at least 2 other places in my docs… but after a week of being in a very different headspace, I just couldn’t remember / find those bits.

For those who are looking for the info - its all right there in lookups.

Item settings : Filter
I had been trying to do it just with formulas and ending up with the right options, but just not as lookups !!!

I’m still trying to solve issue 2 and the bonus problem. (I’ve got ideas about the bonus issue, but its only useful to look into that more once issue 2 is solved ! )

[Johg_Ananda]

Hey @Brendan_Woithe great job solving your own problem!

Re #2, there are a few ways to tackle this. Grouping is a native solution and would give you a related result, but not exacatly what you seem to be looking for. One solution that comes to mind would be to create a column in CRM Activities that checks to see (true/false) if its [followup] date is the max() of the rows of that Company. Then filter to only show columns with true.

If you shared a demo doc I could hop in and help you build it.

Regarding the Bonus question, you’ll need a table containing each of the users. Add a column [Company Selected], which should be a lookup from the Companies table. Then, set your new ‘bonus’ table to filter to show companies that = the logged in user’s [Company Selected] value. Use the button to toggle the user’s [Company Selected] value.

There’s a lot to unpack there, but I think that is the skeleton to get it all working! Good luck

[Brendan_Woithe]

Oh many thanks!
And while you replied, I came up with a solution… which is kind of along your lines.

I’ve created a column with the following formula :

If(thisRow.Followup=last(sort(lookup([CRM Activities],Company,thisRow.Company).Followup)),“Current Followup” ,“Previous Activity” )

I’ve ended up using lookup (which I understand is not exactly best practice?) but at least I got it working. I was getting nowhere with filter.

So my table now has a final column telling me if each row is the most current followup or not.

And I can filter it down to the most current for every company with a very simple filter

I’m still to understand doc sharing / making sure that all our other data remains safe. Just never had to really look into it to figure it out, but it would be so useful in this case - yes!

And I’m going to have a go at the bonus now. Funny how I had a good 3 hours this morning getting no-where, but after typing out the problem and resetting, have been able to solve it - even if not in the most elegant way.

[Johg_Ananda]

Great! This is the process on how to learn - you have to just hack away at it until it unlocks. Your solutions will become more elegant as you practice. Good luck!

[Brendan_Woithe]

And I have been able to figure out my bonus problem as well!
I think that in order to share I’ll have to copy bunch of things to a different workspace… I’ll give it a go. I wouldn’t mind hearing a reaction to how I’ve done it / suggestions on doing it a different way.

Essentially I just made a button that updated a row in a single row, single column table with the name of the company in the button row… and this is then used for the filter of the view of the table that we want to see.

Plus a bunch of other little extra buttons / things to help with formatting

But all in all I feel like I’ve cracked most of this, even if its not elegant.