Skip to main content
dave_derington
dave_derington
Helper ⭐️
October 12, 2017

MDA - Formula Fields / Strings

  • October 12, 2017
  • 9 replies
  • 51 views
Hey Team - I've been working on building out some reports that require me to have a couple different joins on data.  Specifically, I wanted to duplicate a column in an MDA Object so I could perform a different join.

In SFDC, this could be accomplished easily with a formula field with a string operation.  From a previous thread, we don't have those.

I tried to build a Rule that literally copied a field from one column to another ... but that fails.

Ultimately I had to create a rule based on a wholly different object.  Pretty inefficient overall because this query looks at a lot more data.

Whatever the case, I'm doubling-down with my assertion that we need to allow some string operations in the MDA.  Would avoid having to make even more rules.

      9 replies

      devin_stevens
      devin_stevens
      Helper ⭐️
      October 12, 2017
      +1 on more complex formulas (including date and string/text outputs). I know the product team is aware and think things are already in motion here.

      Surprised your rule failed? I've made this work in the past (duplicating column data using a rule).

      Also, interesting that your needing multiple joins from a single field. I haven't had this come up for me, curious what you're doing that requires this? Do you think we'll see more of this in the future?
      dave_derington
      dave_deringtonAuthor
      Helper ⭐️
      October 12, 2017
      This case may be weird.  I'm trying to do two separate joins against an email field.  If you want to look in GonG, the object is "LMS Course Agg" and reports are in the "Training" folder which derive from it.

      May be ill-conceived but I'm trying to help automate delivery and reminders of internal training to Gainsters.  Forces some creativity.  Ultimately I think I'm going to just have to create a new object and a bionic rule to get data I need rather than go this route.

      Going back to the issue at hand, I had two email fields.  Created a rule to copy one to the other.  Unfortunately, I kept seeing "include in identifiers" pop up even though I hadn't selected it.  Prevented it from working so I found another workaround.
      jitin_mehndiratta
      jitin_mehndiratta
      Helper ⭐️⭐️⭐️
      October 13, 2017
      Hi Dave, 

      For Fall release, following formula functions related to string and date will be available in transformation task of rules engine.

      1. ToUpperCase(d),
      2. ToLowerCase(D),
      3. Start of Week(),
      4. End of Week(),
      5. Start of Month(),
      6. End of Month().
      7. Num of Days (Date - Date(Datetime)),
      8. Date (DateTime),
      9. Start of Calendar Quarter(),
      10. End of Calendar Quarter()
      11. Concat (a,b,"whatever",...), 

      We plan to have additional functions like subtring, length, position etc. in the upcoming releases. 

      Regards,
      Jitin
        dan_ahrens
        dan_ahrens
        Expert ⭐️⭐️⭐️
        October 13, 2017
        This is fabulous news, Jitin!!

        We can finally do true efforts to outcomes reporting without a bunch of hacky workarounds. 
        VP of Customer Success at Forcepoint
        dave_derington
        dave_deringtonAuthor
        Helper ⭐️
        October 13, 2017
        This is fantastic news.  Can't wait (really)!
        jitin_mehndiratta
        jitin_mehndiratta
        Helper ⭐️⭐️⭐️
        January 29, 2018
        We are introducing few more string formulas in winter release:
        Left, Right, Position, Length, Substring

        1. Extract the left part of the string with first appearance of @ character: [list=1]
        2. LEFT(“Jitin@gainsight.com”, “@”)    = Jitin
        3. LEFT(“Jitin@1987@gainsight”, “@”) = Jitin
        Extract the right part of the string with @ character:

        1. RIGHT(“Jitin@gainsight.com”, “@”)   = gainsight.com
        2. RIGHT(“Jitin@1987@gainsight”, “@”) = gainsight
        POSITION(‘substring’ in ‘string’’)


      • LENGTH


      • SUBSTRING(string,x,y) - Whether one parameter can be a function.

        [/list]
          • dave_derington
          dave_deringtonAuthor
          Helper ⭐️
          January 29, 2018
          Absolutely awesome Jitin!  
          sai_ram
          sai_ram
          Expert ⭐️⭐️
          February 22, 2018
          Hi All, You can find all the above enhancements in our Winter Release. Our Winter Release notes is out,please refer this article. This will help you in configuring. Thanks for sharing 🙂
          sai_ram
          sai_ram
          Expert ⭐️⭐️
          May 22, 2019
          Hi All, Here is a good news about implementation of Formula fields in MDA.

          With this release, Gainsight now allows you to create calculated fields by using String and Date functions. You can find these functions on the Create Calculated Field window.

          You can create a calculated field by using the existing fields in that object, as arguments of the function. Apart from the existing fields, you can also use other applicable formula functions as arguments while building a formula field.

          For more information please follow this article.

          Terms & ConditionsAccessibility statement