Manipulating Dates in a field

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?

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

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:

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:

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.

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:

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:

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.

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:

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:

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.

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:

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:

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.

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:

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:

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.

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?

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:

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.