Scripting challenge for looking at values across tables

[AndrewDodds]

Hello all,

I have been wrestling with this scripting challenge for awhile and could use help. At a high level I am looking for a way to specify on a record in one table what fields I want to go capture on another table to write into a single field on the record in the original table. 3 tables are involved(but only 2 matter for the scripting):

1)Task template
2) Tasks
3) Services

Tasks are created based on tagging a service(services have their own tasks defined on the task template. Those tasks are generated and have a field called "additional fields needed". This field will have the names of fields on the services table. I want the script to look at the field names in "additional fields needed", go to the services table, find the correct record based on the service name, find the fields, grab the values, write the values into the tasks table in a field called "additional information"

Any help or advice would be greatly appreciated

[Ben_Young1]

Hmmm... I must've read over your post over twenty times and each iteration leaves me just as lost as the last.
Are you able to provide:

1. Screenshots of your table/base schema?
2. Snippets of any existing code you already have?
3. Additional information about what the workflow and how you've ended up at the current structure you already have?
4. Information about when the script is being run? Is it through an automation or through an extension? If it's an automation, what's the trigger and when is it intended to be triggered in a theoretical workflow?

Reading through your original description leaves me with the following initial suspicions:

1. It's possible that you don't need a script at all.
2. Depending on any new information on its actual usage, you shouldn't need that Task Template table at all.

Again, any additional information is always appreciated.

[AndrewDodds]

Hey Ben,

Thanks for taking a look and apologize for the confusion. Let me include some more detail for you(I am positive however that this will have to be done with a script unfortunately).

Task Template table has a list of all tasks that have to be completed by Service.  This is linked to the service record. These tasks are automatically generated when a service is selected for a client in the Tasks table. One of the fields in the template has field "Additional Fields" which contains the names of fields(that exist on the Service table). This field could contain 1 or many different field names depending on the task. This is to denote what fields in the service record have information that is required to complete a task.   

At a high level the script should look at each record thats been created in the Tasks table and for each record look at additional fields. Go to the Service table(for the service that is tagged against the task(look up from template) and get the value of those field names. It should then bring those values back to the Tasks table and write them comma separated into a field called "Additional Information".

Example:
Service Plan 1 had these tasks created on the task table
Task 1: Task 1 has the name "Field 2", "Field 3" in the "additional fields" field
Task 2 Task 2 has nothing in the additional fields field
Task 3 Task 3 has "Field 6" in the addtional fields field

The service table is then queried and "Service Plan 1" record is found. 

1) Cell value for field 2 and field 3 is found. Lets say field 2 = email marketing, and field 3 = Shipping cost
2) Back in the Tasks table we want to write "email marketing, shipping cost" on task 1 in the field "additional information"

In terms of workflow I'll want to run it from an automation matching conditions of task exists, and service plan is not blank, and additional fields required is not blank

Let me know if this helps to clear the picture up?