Ensuring a text field in Dataverse is always trimmed

[Rydman]

Hi,

I've run into a bit of a snag. In a model-driven app the user can enter a folder name into a text field. A cloud flow kicks in when a record with this info is created or updated and creates the folder in SharePoint. After some head-scratching and experimentation, I've concluded that the SharePoint connector chokes on folder names with trailing (and perhaps leading?) blanks (strangely, I get a 502 Bad Gateway error after several attempts and an eventual timeout).

I could obviously trim the string I get from the record in the flow but that seems to be a shaky solution. I (or somebody else) may forget to do this in future flows so I'd like to fix the problem as far upstream as possible, preferably ensuring that leading/trailing blanks never end up in the field in the first place.

One thought I had was to have a separate flow triggered on Add/Change to just trim the field, but since the problematic flows already trigger on this I'm pretty sure I can't be sure the "trim" flow triggers before the "create folder" flows (yes, at this point I have two, one for Add and one for Change). Again, I could add the fix to the flows but that would pretty much land me with the same problem.

Can Business Rules somehow be leveraged here?

Any tips on how to achieve this greatly appreciated!

[ChrisPiasecki · Accepted solution]

Hi @Rydman,

From what I recall and unless this has changed, Dataverse will automatically trim the white space entered in a model driven form column before saving the record.

If the above is not consistent with what you're experiencing, then you could use a JavaScript web resource to do it programmatically by using the OnSave event or use a Plugin in the Pre-Operation stage to trim the white space server side. 

For a low code way, once the Formula Column field becomes generally available in Dataverse (could be any week now), you could create a formula column and use the Trim functions .

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[Rydman]

Thanks  @ChrisPiasecki!

I will monitor the availability of the Formula Column filed and give it a try. The way I read the description I would need the "raw" field which is updated by a form, and a calculated field which I'll use in my SharePoint scenario. I don't want duplicate fields, the JavaScript way seems the better choice.

[ChrisPiasecki]

Hi @Rydman,

That's correct, you'd have the actual value and then a formula column. It does mean you need an extra column, but since it's value is computed on retrieve, it won't take up any storage. Using JavaScript avoids this but requires code.

Either is fine so it comes down to preference and ability to maintain. If you have developers then JavaScript is less of an issue to maintain. Formulas can be maintained by a broader set of users since its no code. 

---
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.

[Rydman]

Hi  @ChrisPiasecki,

Ok, the "virtuality" of the calculated field seals the deal for me - that's definitely the way to go. Thanks!

Microsoft's communication indicates that the feature should be GA by now but it won't show up in my editor. You mentioned "within a couple of weeks" which I take to mean that your guess is as good as mine when it comes to when exactly it will be available?

Anyway, I'll flag your original post as a solution.

Cheers!

[ChrisPiasecki]

Hi @Rydman,

Those are target dates, therefore can be subject to change. When it is rolled out you'll see a green checkmark next to it. It will also depend on your region as well since they are staggered as you can see here. So we just have to be a bit patient 🙂