Manipulating Dates in a field

[Jill_W]

Hello:
I have a multiple select field with one or more dates.

1. I want to count the number of dates and then filter the people with with 2 or more.

2. I also want to eliminate people with whom we have not had contact with in more than 2 months? In if statement? if so how?

Thanks for your help.

[Justin_Barrett]

Welcome to the community, @Jill_W! :grinning_face_with_big_eyes: Counting multiple-select items is fairly straightforward. Here’s a rough formula; replace {Field Name} with the name of your multiple-select field.

LEN({Field Name}) - LEN(SUBSTITUTE({Field Name}, ",", "")) + 1

For your second problem, I’ve got some follow-up questions:

1. What do you mean by “eliminate people”? Are you talking about deleting records? Changing their status so they don’t show up in the main view with your active contacts? Something else?
2. Where are you tracking the date of your most recent contact with each person?

[Jill_W]

Hello Justin:

Thanks for your response.

1. The formula works, but I don’t know or understand the logic behind the construction or the functions. so could you kindly explain?

2. I have a main view with all the data. And I create other views filtering, sorting, grouping as needed. This multiple select field has the dates of contact with each c;ient. I want to be able to count # of dates as in 1) but also filter out the clients with whom we have not had contact after a certain date, say, a date 2 month ago. I’d like to give them a designation of 'no contact in 2 months.

3. Now the first contact with individuals are either web, email, community, hospital; I want to be able to get the result in 2) above, BUT keep all hospital referrals even if we have not had contact with the in the last 2 months, with me so far?

4. Another issue: I have been trying to figure out how to do this next thing. For each record, a client made contact with x-employee on a certain date. If they were contacted multiple times, they may have come into contact with different employees, say on March 10, they spoke with Ann, on march 15, and march 30, Drea. Right now as the table is set up, There is a main table with all clients, There is a multiple select date field with all the dates of contact (see above). This multiple select field is linked to another table with dates and each date lists the client ID of all clients that were contacted on that day. What is missing is who contacted each client on any given date. There is a multiple select field with all employees, but for each record, I am only able to see the last employee who contacted a client. I need to resolve this since I want to see which employee made contact with each client on all dates of contact. Do you understand?

Thank you so much for your help, and beyond just giving me the solution, could you also explain the solution in detail so that I will learn in the process?

Regards,
Jill

[Justin_Barrett]

Jill_W:

The formula works, but I don’t know or understand the logic behind the construction or the functions. so could you kindly explain?

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:

Jill_W:

There is a multiple select date field with all the dates of contact (see above). This multiple select field is linked to another table

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:

Airtable - Support

A beginner's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...

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:

1. [Clients] - Where you track all client-specific information (name, address, phone, email, etc.)
2. [Employees] - Where you track all employee-specific information (name, address, phone, email, etc.)
3. [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:

Jill_W:

I want to be able to count # of dates as in 1) but also filter out the clients with whom we have not had contact after a certain date, say, a date 2 month ago. I’d like to give them a designation of 'no contact in 2 months.

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.

Jill_W:

Now the first contact with individuals are either web, email, community, hospital; I want to be able to get the result in 2) above, BUT keep all hospital referrals even if we have not had contact with the in the last 2 months

For this, you could add an OR condition to the filter mentioned above, selecting records whose initial contact was a hospital.

Jill_W:

There is a multiple select field with all employees, but for each record, I am only able to see the last employee who contacted a client. I need to resolve this since I want to see which employee made contact with each client on all dates of contact. Do you understand?

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.

[Jill_W]

1. 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.)

2. 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

[Jill_W]

I read your notes again and the suggestion of a primary field in the junction table {Contacts}. Would that change after each contact/delivery with a client…since the different employee/volunteer will have contact with a contact on different dates?

[Jill_W]

One more thing: In the scenario that you presented, what if I wanted to continue to track the clients who we delivered to/made contact with on specific dates? As in Image B) above…Delivery Dates table?

[Jill_W]

So that this is as efficient as possible, please allow me to present a scenario:
Employees / Volunteers: Ant, Bee, Cheetah

Clients: Zed, Yuri, Xavier, Walter

Dates of delivery: 5/15, 6/15, 7/15, 8/15

Food Source/Pantries: Location X, Location Y

On each delivery day, a volunteer is assigned clients and not c=necessarily the same client that they may have delivered to or made contact with before. For eg.,
On 7/15, Ant may be assigned Zed from Location X, but
on 6/15, he was assigned Yuri from Location Y. and
on 5/15, he was assigned Xavier & Walter from Location X.
On 8/15, Ant called in sick and did not come to work.
Extrapolate this scenario to all volunteers/employees & clients.

I need to be able to see:

1. All the dates that Ant/volunteers/employees did deliveries and to which client
2. All the dates that each client received deliveries which which volunteer/employee did those deliveries
3. I need to be able to pull up a date, and see which recipients received deliveries AND which volunteers worked.
4. I need to able to pull up a food source, ie. pantry and see all deliveries by recipient on each date; right now there are 2.

[Jill_W]

So I have 4 tables in my base:
Recipients
Volunteers
Delivery Dates
Pantries
PLUS
I have one Junction Table, Recipients/Volunteers that links to:
Name | Recipients Link | Volunteer Link | Delivery Dates Link

a) Do I need to now create a field in the first four tables linking then=m to the junction table and when do I select" Allow for linking to multiple records" or “limit views…”

b) Also when I create a linked record, i am being asked to create a lookup field. When i do that a new column is created and I don’y understand why?

c) Also, i think that in my primary table, the contact table, i would prefer a more meaningful primary ID other than the random numbers, what would you suggest?

Thanks.
Jill

[Justin_Barrett]

Thanks for the further details, Jill. I’m winding down for the day, and I won’t be in here on Sunday, so I’ll review your notes on Monday and get back to you then. However, I feel like I should clarify one point based on this question:

Jill_W:

I read your notes again and the suggestion of a primary field in the junction table {Contacts}. Would that change after each contact/delivery with a client

I apparently wasn’t as clear as I needed to be about how the junction table would be used. It sounds like the way you’re doing things now, you modify a single record for various interactions with a given client. With a junction table, you wouldn’t operate this way. Each record represents a single contact between an employee and a client on a specific day. For the next interaction, you would make a new record, even if it’s the same employee and the same client. Each contact is unique, so each gets its own record. This does mean that you have a lot of records in the long run, but it’s actually a much more efficient way of managing the data, and provides you with more options when it comes to reporting.