Smartsheet Formula for Multiselect cell based on time

WCantrill

Hi,

I have created a form for our clinical therapists to enter type of service or referral provided for particular clients each week. I want an automation that takes this data and lists services provided to a particular client on another sheet if it was completed in the past week.

The formula I've been working with is:

JOIN(COLLECT({Service Type - Source Sheet},{Client Name - Source Sheet)},HAS({Client Name - Source Sheet},[Client Name @row - Target Sheet],CHAR(10)

I realise this doesn't have a date function attached at the moment as I'm trying to get the above formula to pull the correct data in the first place. The formula above seems to 'run' but returns a blank value when it should be returning multple services provided to the named client. Using HAS and CONTAINS functions seems to return blank values whereas not using them (using criterion@row on it's own) I have found when only a single client is listed in the Client name Column in the source sheet (it is a multi-select column), the formula does return multiple service types for that client. However as soon as multiple clients are entered in that column the formula does not list service types even when the clients name I'm looking to filter is among the clients selected in the dropdown menu.

Finally Assuming can get the above to work, what would be the best way to list only services provided to a client in the past week?

I am not sure what I am missing.

WCantrill

Update:

After reviewing threads and trial and error, I've been able to get the formula to partially work now by adding the @cell function to the formula above:

JOIN(COLLECT({Service Type - Source Sheet},{Client Name - Source Sheet)},HAS(@cell,[Client Name @row - Target Sheet])),CHAR(10))

Additionally I was able to add the time function by adding a 2nd date column to my source sheet that included the TODAY function. I was then able to use NETDAYS to work out the difference between todays date and when the form was submitted. I was then able to use the 'criterion 2' part of the COLLECT formula above, with the final formula being:

JOIN(COLLECT({Service Type - Source Sheet},{Client Name - Source Sheet)},HAS(@cell,[Client Name @row - Target Sheet]),{Days}, <7),CHAR(10))

I hope this helps somebody in future.

WCantrill

Update:

After reviewing threads and trial and error, I've been able to get the formula to partially work now by adding the @cell function to the formula above:

JOIN(COLLECT({Service Type - Source Sheet},{Client Name - Source Sheet)},HAS(@cell,[Client Name @row - Target Sheet])),CHAR(10))

Additionally I was able to add the time function by adding a 2nd date column to my source sheet that included the TODAY function. I was then able to use NETDAYS to work out the difference between todays date and when the form was submitted. I was then able to use the 'criterion 2' part of the COLLECT formula above, with the final formula being:

JOIN(COLLECT({Service Type - Source Sheet},{Client Name - Source Sheet)},HAS(@cell,[Client Name @row - Target Sheet]),{Days}, <7),CHAR(10))

I hope this helps somebody in future.