How to search with an input in the entire column instead of the row

Ask for Help
1

Hello community, I need help with a logic, I am learning about Glide and have made great progress, but I am stuck in this context:

I have a search to be done in a table, this table has the following composition:

Captura de Tela 2023-04-13 às 03.14.08
Captura de Tela 2023-04-13 às 03.14.082168×922 102 KB

Context:

  • The idea is to have a text entry (input) where I will enter the CNPJ (number)
  • If the input is not found, that is, the entered CNPJ is not equal to any CNPJ in the database, I will return = Available client for negotiation.
  • If the input is found, that is, the entered CNPJ is equal to some CNPJ that is already in the database, I need to make another verification in the Status column.
  • If the CNPJ is found, I search the status column that can have two statuses: lost or won.
  • If the status is = lost, I will return = Lost client, available for negotiation.
  • If the status is = won, I will return = Already our client.

Google Sheets Formula:

  • I used the following formula in GS and it worked smoothly:
=IF(ISBLANK(G2),"",IFERROR(IF(VLOOKUP(G2,API!A:E,3,FALSE)="won","Already our client";IF(VLOOKUP(G2,API!A:E,3,FALSE)="lost","Lost client, available for negotiation")),"Available for negotiation"))

Attempts:

  • I tried to use the logic in GS, but there are 40,000 CNPJs, which makes the search very slow (discarded).
  • I tried to use a search logic (input) + IF-THEN-ELSE column, but the input seems to search line by line and not the entire column, if you know how to do this.
  • I tried to use common lookups, but I did not know how to relate the first CNPJ search with the conditions in case the CNPJ is found and I need to verify the status.
2

I would do it something like this:

  • Create a separate single row table to use as the source for your query input
  • Add a RowID column to that table, and a User Specific Text column to accept your Text Input (CNPJ value)
  • Create a single relation column that matches the entered CNPJ value with the CNPJ column in your data table
  • Add a Lookup column that fetches the Status value via the Relation column
  • Finally, add an if-then-else column using the following:
    – If Relation column is empty, then ‘Available client for negotiation’
    – If Lookup column is lost, then ‘Lost client, available for negotiation’
    – If Lookup column is won, then ‘Already our client’
3

Hey @Darren_Murphy , it perfectly solved what I needed, I really appreciate the help the glide community has been incredible in this process of learning with the tool. Thanks!