"Inbox" Filtering

Ask for Help
1

Howdy! I recently have been converting an app I had in Sheets over to BigTable for a CRM and texting app that I built. In Sheets, I recreated an “Inbox” feature via a vlookup to another table to find the most recent text in a conversation so, like most inboxes, only the most recent message would display in the table and the history was in the details. I’m having difficulty recreating that in BigTable. Any ideas?

2

Update: Actually, just re-reading your question, perhaps all you need is a Collection with a simple filter applied? :man_shrugging:

If it’s more complex than that, a couple of screenshots would help provide extra context.

Original reply:

The Glide equivalent of a VLOOKUP is either a relation + lookup, or a query + single value.
If it’s a simple one to one match, then use relation + lookup.
For more complex matches, use query + single value.

3

Yeah, I’m trying to do the collection.

My data list is a running list of all inbound and outbound SMS messages and phone calls sorted by most recent. I have a Client ID column that relates to a to/from phone number to a client so I can get their info nicely pulled up.

The catch is there’s no filter for “is unique” or anything like that, so I get ALL texts in the queue, instead of just the most recent which is what users typically expect in a messaging app. I’m attaching a screenshot and you’ll see two from “Deborah.”

Screenshot 2023-07-06 152300
Screenshot 2023-07-06 152300268×518 54.7 KB

In Sheets, I had a vlookup + count unique to get a sequence of occurrences of the Client ID value; then I filtered and only displayed the most recent message. But now that I’ve gone to BigTable I don’t have the same ability since BigTable seems to not let me reference a computed column in any way.

Any ideas based on that?

4

Is it possible for you to:

  • On the Messages table, create a “Numeral Timestamp” column, set it as a number column.

  • Create a math column called “Calculation Timestamp” with the formula:

YEAR(T)*10^10+MONTH(T)*10^8+DAY(T)*10^6+HOUR(T)*10^4+MINUTE(T)*10^2+SECOND(T)

You’ll get something like 20230710080006, which corresponds to 10th July 2023, 08:00:06 AM.

  • Temporarily copy the existing values from that “Calculation Timestamp” result to the “Numeral Timestamp” column.

  • On the form where you let users submit their messages, create an on-submit action that waits at most 10 seconds for the condition of Calculation Timestamp is not empty, then set the Calculation Timestamp value to the Numeral Timestamp column. This step converts the calculated column value to a normal column value, allowing you to use a rollup.

  • Create a multiple relation using the Client ID, targeting itself.

  • Create a rollup on top of the relation above, returning the biggest “Numeral Timestamp”.

  • Filter your list so that “Numeral Timestamp” equals the rollup value.

1 Like