Using a Filter?

[T-Abbella]

Hello Honeycoders,
I got some Honey for you!
I'm building recruiting app to Health care providers. I have a "Provider" table and "clients" table.

• On "Provider"table, there is a Picklist column for "clients" which list the client that a provider is assigned to
  

• On the "Client Table" there is a Column for filtered list of "Clients_requirements" that list a bunch of clients requirements by clients
  
  Problem

• Create a filtered list column, on "provider" table that list "Clients_requirements" based on "Clients" column on "Provider" table

At this time, on "provider" table, I'm unable to to filter "Current_required_documents" column from "Requirement_clients" table, based on "Current Client" column, no error on filter formula, and no results of the test data I know I have. What I'm doing wrong? See my formula below

Thanks in Advance!

[aj]

Hello @T-Abbella, hopefully I've got some honey to give in return! So I'm trying to make sure I fully understand this problem. If I do, I believe what you'll need to use is =FINDROW instead of the =FILTER function in the Client Requirements column of the Provider table.

For example: =FINDROW(Clients,"Clients[Client]=%",[Client][Client])[Client_Requirements]. This will give you the number of requirements as listed in the Clients table (via a filtered column) that are associated with the specific Client in that given row in the Provider table.

Here are some screen shots of my three-table mock environment.

If this isn't what you are looking for, I may have misunderstood your table layout. Please let us know!

[T-Abbella]

This works like a charm, Thanks soo much, you understood me completely!

[T-Abbella]

By the way @Taylor, I actually followed the example as you advised above. But for some reason, I kinda do not still understand the findrow() very well. Here is my issue.
table2 = Has the date I need to grab
table 1 = is where I have my findrow column
=FINDROW(table 2,"table2[column in table2]=%") ---I understand uptp this point..moving on
=FINDROW(table 2,"table2[column in table2]=%", [column header for the source data of table 1(source data)]) ----I also understand upto that point...moving on

DIfficult part
=FINDROW(table 2,"table2[column in table2]=%", [column header for the source data of table 1(source data)][Which column and why? it appears its column from table2 but why?])[a column from table 2? why? and outside of findrow() brackets?]

I can type the example you gave me, and it works, but I need a deeper understanding so I can apply in other areas of the app.
Thanks again!

[T-Abbella]

Hi @aj not sure if this is a findrow issue again, so sorry to trouble you here my friend, I will make this as simple as possible for you to understand what i'm doing.
Applicant Table - has 1 column(name)

Client Table - has 1 column (Client name)

ApplicantDocuments - has three columns (Requirment Name, Required by(picklist from Client, Applicant Name(picklist from Applicant))

CLient Requirements - has 2 columns (Requirement, Client Name(picklist from client)

ApplicantQualified Clients - This table has these column and roles:

Qualified clients column - picklist from client table
Applicant Name - Picklist from applicant table
Client Requirement - filter column to filter per client in column A

Applicant Document - This column contains documents submitted by applicant, and since applicant may qualify for multiple clients, I need to have submitted per client in column A

The problem, is I have tried using findrow formula, and filter with logical AND, and its gving me 0. I want the column to return only the documents, that match client X. As you can see, Tom submitted 2 documents for client X but its showing 0, with no errors. What I'm doing wrong? Or I'm modeling my tables wrong?
Advance in Thanks

[aj]

Hello @T-Abbella, thanks for the detailed breakdown. Impressive work you are doing here! And you are so close! The only tweaks that I believe you may want to make are the following:

• ApplicantDocuments table: Change the Requirement Name column format to be a rowlink, which should link back to ClientRequirement table. And then you can remove the Required by column in ApplicantDocuments because this can be pulled by the rowlock that you just created. The power of rowlinks. Here's my ApplicantDocuments table:
  

  

• ApplicantQualifiedClients table. The fun one.

  • for the Client Requirements column (C), try this: =FILTER(ClientRequirement,"ClientRequirement[Client Name]=THISROW()[Qualified Client]")
  • for the Applicant Documents column (D), try this: =FILTER(ApplicantDocuments,"ApplicantDocuments[Requirement Name][Client Name]=% AND ApplicantDocuments[Applicant Name]=%",THISROW()[Qualified Client][Client Name],THISROW()[Applicant Name])

Here is the table:

Now, when you go to add an applicant document, you'll see the change in the ApplicantQualifiedClients table (right hand side):

Lastly, I recommend checking out this article, as it discusses the concept of retrieving data from rowlinks, as this is essentially what we are doing above.

Hope this helps! Don't hesitate to keep asking questions here for further help or clarification.

[aj]

@T-Abbella, one other comment I'll add, depending on what your plan is from an app/app building perspective, because you have this many-to-many ApplicantQualifiedClients table, you may end up wanting to add some table-side automations. Quick example, when you add a new applicant you might want to auto-populate that ApplicantQualifiedClients table with how that applicant meets each of your clients' requirements. Like this...

Here's the Automation:

You could also do something similar for when a new Client is added to the Clients table, which would basically auto-populate the ApplicantQualifiedClients table with all of the applicants.

Ultimately I believe you may want to avoid any sort of manual updates to the ApplicantQualifiedClients table, and rather, have a nice UI that allows you or other users to add applicants and clients alike, and be able to quickly and easily determine whether any given applicant meets each client requirements.