cancel
Showing results for 
Search instead for 
Did you mean: 
0 Kudos

CDS - SUBSTITUTE() and FIND() for Calculated Fields

Would like to have the ability to use SUBSTITUTE() and/or FIND() within a calculated field in CDS.

 

ie:

SUBSTITUTE( origstring, oldtext, newtext )

would search the string "origstring" for all instances of "oldtext" and replace with "newtext", having the resulting string as the return value.  

  

FIND( findstring, withinstring, startingpos )

would return the position of "findstring" inside of "withinstring", starting from "startingpos" (optional).

 

The purpose of both of these would be to have a calculated field that can be calculated based on other fields, where the value to be replaced may be in the "middle" of the original string.

 

ie: My particular use case is as follows:

 

I have a custom entity - let's called it "Resource" that has the following fields:

 

  • AccessMethod - option set with values "Url" and "DocumentId" (more potentially in the future)
  • Url - just a URL
  • DocumentID - a text field specifying a "document ID" for a particular document
  • ResourceLocation - a Lookup field to a ResourceLocation entity that contains its own TemplateUrl field (also a URL)

So the idea is that for a particular Resource record, I want to have a calculated field called AccessUrl that determines the actual URL to access the resource based on the value of AccessMethod and the other fields.  ie: something like:

 

If AccessMethod equals "Url"
   Set AccessUrl to Url
ELSE IF
    If AccessMethod equals "DocumentID"
         Set AccessUrl to Substitute( (ResourceLocation) TemplateUrl, DocumentID)

 

For example, let's say that I have a "replaceable parameter" titled {$DocumentID} that I want to use in TemplateUrl to specify where to insert the DocumentID field in order to create AccessUrl, ie:

 

 

where TemplateUrl is:

 

    https://www.mygreatwebsite.com/document?docid={$DocumentID}&display=fullscreen&accesstoken=12345

 

and DocumentID field is 2ak2asd, for instance.  I'd want to be able to have a calculated field, AccessUrl that becomes

 

    https://www.mygreatwebsite.com/document?docid=2ak2asd&display=fullscreen&accesstoken=12345

 

In this example (which is my real-life requirement) the location of {$DocumentID} is not known (it can be anywhere in the URL) or for that matter - it may not even necessarily be present.  By being able to have a SUBSTITUTE() function, I can handle this.

 

ALTERNATIVELY a FIND() function - such as that also asked for above, would also allow me to do this, albeit with a bit more difficulty, as I could then get the position of the sring ({$DocumentID}), and then use TRIMLEFT() and TRIMRIGHT() with CONCAT() to do the same thing (note: this would imply when writing the calulation that I know the length of the string {$DocumentID} (which I do of course) and that its not a value in another field or else I'd also need a LEN( string ) function to return the lenght of the string.  

Would prefer SUBSTITUTE() as its easier, but either would deliver the functionality.  

 

There are actually a lot of different uses for this I would have, but the above is the simplest.  Longer term I'd alos like to use this in cases where there might actually be two or three fields that would be replaced (say a UserID or Password, etc)


Currently I have to manually calculate this field in PowerApps (using AddColumns()) as well as anywhere else I wish to use it (ie: in a Flow, etc) which can be problematic to maintain consistently (and hence is the real benefit of calculated fields).

 

 

Status: New