Hi,
I am working on an automation that collects a rota, manipulates the data (creating an email address from the names) and sends out a calendar invitation using both the date and now converted email field.
The 2 fields it collects from excel are date (dateTime) and name (text). This has given me 30 rows (a months worth of data) stored in collection 'rota'.
My issue is that some of these name fields contain the names of 2 people (most only contain 1 which is fine). To get around this I have built an object that first uses 'utility strings -split text' to check for an '&' and store to a new collection called 'split values'.
It then loops the split values collection, adds a row to a new output collection called 'recipient contacts', uses a calc stage to convert this into an email address and stores both the names and email addresses in this new collection.
My issue is that I now have the original rota collection which has 30 rows (date and names) and a new collection which contains 36 (text and email). The 6 extra rows are the split names from the rota collection.
Ideally I want to merge them both into a collection that contains 36 rows with 2 columns of date and email address as this is all I will need to run the 'add calendar appointment' object.
How do I do this? Or was there an easier way to do this that I'm missing?
TLDR version : I want to merge a collection with 30 rows to a collection with 36 but keep the date column from collection 1 (duplicating 6 dates) and the email column from collection 2.
Further explanation. Imagine collection 1 is...
Phew! Thanks
I am working on an automation that collects a rota, manipulates the data (creating an email address from the names) and sends out a calendar invitation using both the date and now converted email field.
The 2 fields it collects from excel are date (dateTime) and name (text). This has given me 30 rows (a months worth of data) stored in collection 'rota'.
My issue is that some of these name fields contain the names of 2 people (most only contain 1 which is fine). To get around this I have built an object that first uses 'utility strings -split text' to check for an '&' and store to a new collection called 'split values'.
It then loops the split values collection, adds a row to a new output collection called 'recipient contacts', uses a calc stage to convert this into an email address and stores both the names and email addresses in this new collection.
My issue is that I now have the original rota collection which has 30 rows (date and names) and a new collection which contains 36 (text and email). The 6 extra rows are the split names from the rota collection.
Ideally I want to merge them both into a collection that contains 36 rows with 2 columns of date and email address as this is all I will need to run the 'add calendar appointment' object.
How do I do this? Or was there an easier way to do this that I'm missing?
TLDR version : I want to merge a collection with 30 rows to a collection with 36 but keep the date column from collection 1 (duplicating 6 dates) and the email column from collection 2.
Further explanation. Imagine collection 1 is...
- 01/11/2020 - Person A
- 02/11/2020 - Person B
- 03/11/2020 - Person C & Person D
- Person A - PersonA@email.com
- Person B - PersonB@email.com
- Person C - PersonC@email.com
- Person D - PersonD@email.com
- 01/11/2020 - PersonA@email.com
- 02/11/2020 - PersonB@email.com
- 03/11/2020 - PersonC@email.com
- 03/11/2020 - PersonD@email.com
Phew! Thanks