Manipulating Dates in a field
0
2001
10
When pulled into a formula, multiple-select fields return a comma-separated list of items as a string. The SUBSTITUTE() function in my formula replaces all commas with an empty string, which removes them. LEN() returns the length of a given string. With that in mind, the first part of the formula takes the full length of the multiple-select string, then subtracts the length of the same string with the commas removed, which tells us how many commas were in the string. Because the number of commas will always be one less than the number of items they separate—one comma between two items, two commas between three items, etc.—we add one to that result, which gives us the number of items from the multiple-select field.
While reading through the rest of your notes, I realized that what you actually have is not a multiple-select field, but a link field. Here’s the clue:
Multiple-select fields cannot link to other tables, but link fields can. If you’re pointing to one or more records in another table, it’s a link field. Here’s how to spot the difference between the two via their field icons:
Thankfully a link field also returns a comma-separated list of items when queried by a formula, though, so the formula I gave you earlier doesn’t need to change…yet…
The bigger issue I’m seeing after reading your comments is the general structure of your base, which I’m pretty sure is the main reason that you’re having problems doing some of the things you want to do. Currently, you have a table with dates in the primary field, and you’re linking to these records to track client contacts. However, because you’re tracking multiple contacts for a single day all in one record, you’re not able to break out individual contact details.
What I suggest is creating what’s known as a junction table (also called a join table). A junction table isn’t a specific type of table with different behavior or operations. It’s just a regular table that’s used in a very specific way to act as a junction between other data you’re tracking. In your case, each record in this junction table would track one employee contacting one client, the date of that contact, and any other details related to that interaction. You might be able to convert your existing contact-tracking table into a junction table. Whichever way you approach it, it’s going to take some work, but in the end, it will be worth it.
I’ll describe the setup of this junction table in detail below, but here’s where you can read more about junction tables and how they’re used:
I also suggest changing your employee multiple-select field into a link field pointing to an [Employees] table, which would allow you to do even more things with your base.
In the end, here’s the list of tables that you would have:
-
[Clients]- Where you track all client-specific information (name, address, phone, email, etc.) -
[Employees]- Where you track all employee-specific information (name, address, phone, email, etc.) -
[Contacts]- The junction table I mentioned, which could be set up with fields like this:
a.{Name}- Your primary field, which could be a formula combining elements of the other fields to make a unique identifier for each record; e.g. “6/26/2020 - Joe & Client A”
b.{Employee}- A link field where you would link to the employee who contacted a client, with only one link per record.
c.{Client}- A link field where you would link to the client contacted by the listed employee, with only one link per record.
d.{Contact Date}- The date the contact was made
e. …plus any other fields for tracking notes, follow-up instructions, etc. related to each specific contact.
Here’s how this setup could address your other questions:
On the [Clients] table, you could add a rollup field named {Last Contact}. This would use the links coming from the [Contacts] table, collecting the {Contact Date} values to find the latest contact date using MAX(values) as the aggregation formula.
You could then make a view named “No Contact in 2 Months”, with a filter that only displays contacts with a {Last Contact} date that’s older than 60 days ago.
For this, you could add an OR condition to the filter mentioned above, selecting records whose initial contact was a hospital.
By using a junction table as I outlined above, and changing the multiple-select employee field into a link to an [Employees] table, you would see the employee involved in each contact with the client.
I think that covers it, but please let me know if you need further clarification.
- So, Justin. Thank you for this. I understand the formula now. However, I need a bit more help actually…implementing your thoughtful analyais and suggestions using what I currently have. So I have attached links below. Note that the primary ID is not the dates, but an ID number based on the intake order. I don’t know if you will recommend another primary ID. It was just default because it made data visualization and map routing through Google maps easier.
( FYI: We are an aid/non-profit volunteer network that has a food disbursement delivery system with a food pantry run by a major hospital. A fleet of volunteers pick up food on certain days and deliver to clients.)
- Can I give you access to it by sending a link? Would I have to create a new base without the client info? the primary ID in the main client base is an ID#. I’m not sure that this changes your analysis/suggestions above. In any case, Here are some pics of the field titles for the main base where everything lives currently:
A. The Main Data table
B. The Delivery Dates Table
C. Pickup Organization & Contact Info.
Thank you so much for your continued help!
Cheers,
–Jill
