Rule that Auto-Creates New Fields in MDA

Hello Alyssa,

I'm going to assume you only want to keep the comments and don't need anything else.

Build an MDA table with fields to track these point:

SF Account ID (look up to Account ID in SFDC)

Gainsight Company ID (look up to company)

Created Date

Comments

Build a rule that fetches these 4 data elemants from the Scorecard table you want the comments from.

Add a filter for Comments not equal to blank.

Add a second filter for the crated date range you want to the rule to fetch and right data through. (example -1day to just look up yesterday's comments)

Then the rule action is to upsert the data based on the Company ID and the Date.

Run the as frequently as needed to keep the data fresh.

New great thing to try and do is build a report in report builder first, then you can check the data.

Hi Todd,

Thanks so much for the quick reply. You're correct--I'm just looking for historical comments.

Am I mistaken in my understanding that the rule you've described would overwrite the comments each time it's run? I'm looking to store all historical comments, and I know per https://community.gainsight.com/conversations/is-it-possible-to-go-back-and-see-scorecard-comments-history-5bc73e0de4b04588aaf8611b that comment history is not currently stored.

Sorry I thought you wanted to start logging new comment as they came in and build out the comments history. The rule I outlined would write a new rule based on the creation date and the company id. This should use both time and company id to write new rows of data when comments are changed.

Now i'm a bit less confident. I'm wondering if the Created date is linked to the comment at all or if there is any date field linked to comments.

If you can isolate a date field you can use that as an additional upsert fitler. T hat should keep you form overwriting past comments.

Hi Alyssa,

What you'd want to do in order to accomplish this is first copy all current comment data to your custom MDA table. Fields you'd need (at a minimum): Account Name, Account ID, Measure Id Name, Comments and Modified Date (and probably Current score Id Score and any other data you'd want to know like Last Updated by, etc)

Once you have all that data in the MDA table you have your baseline. Now you need a rule to run daily that compares the current comments with what you have in your baseline. To do that, use a bionic rule with three fetch tasks.

1st fetch task pulls all current scorecard data: Account Name, Account ID, Measure Id Name, Comments and Modified Date (and any other data like Last Updated by, etc)

2nd fetch pulls all the historical comments from the MDA table: Account Name, Account ID, Measure Id Name, Comments and Modified Date

3rd fetch pulls the oldest comment entry per each Account and each Measure Id: Account ID, Measure Id Name and MAX on Modified Date (this will give you the most recent update for each measure on each account.

Then merge as follows:

Merge 3rd fetch with 2nd fetch using a left merge using all three fields to merge on: Account ID, Measure Id name and Modified Date. Then in your output, add the Account Name and Comments. Your merged data will have the Account ID, Account Name, Measure Id Name, Modified Date and Comments

Then using the output of that merge, perform a second merge with the data from your first fetch, right merge (first fetch data) to bring in all the current scorecard data (Account Name, Account ID, Measure Id Name, Comments and Modified Date (and any other data like Last Updated by, etc)) and bring in any records from the first merge that might match, merging on Account ID and Measure Id Name. Label the output fields for the data that came from your MDA sources with a prefix like "historical" (since you can't have duplicate named output fields.

Finally, transform the output of that second merge by filtering on records where "Historical Comments" does not equal "Comments" - this will identify any changes to the comments.

Use the output of this final step to write new records to the MDA table with the current comments, Measure ID, Modified date (and any other info you wanted to appear in the historical record).

The advantage of this method (vs just blindly creating a complete weekly historical archive every week even if the data didn't change) is that you'll be able to map clearly for any measure ID the comments and when they changed and by whom.

This is amazing--thank you Dan!