Split property value into 2, based on comma

[ChrisChiha]

Hello,

We have a property field that contains 2 values seperated by a comma. I would like to get the value of the property field that is before the comma only.Is there a way to achieve this?

For example:
Property value: Spain, Madrid
I want to fetch "Spain" from the property value and put it in another property to be used in a workflow later.

Thanks!

[Levi · Accepted solution]

Hi @ChrisChiha , maybe use the format data action in workflows. See this link. Otherwise export the propertie to Google Sheets or Excel and use the text to columns function with seperator ','. And than import the 2 colomns again in the right propertie. 

[Albusis]

You mention a code you created. This can be also achieved in HubL, and I find backend solutions more reliable. I use both front end and backend. The backend is simple in HubL. You take property, apply filter split, use a splitting character, or set of characters, and run a for loop. And voila, you have a list of values. 

{% set billing_codes_list = contact.billing_codes.split('~~, ') %}
<ul>{% for code in billing_codes_list %}
<li>{{ code }}</li>
{% endfor %}</ul>


in my case the billing_codes is a property, contact is object and "~~, " is a splitting set of characters. I use for values that can have dozens of characters including semicollon. But if you have simple values, like Madrid, Spain, you can just use "," as your splitting character. 

In my front-end solution, I use a script that takes values and even shortens them to a maximum of 40 characters per line so they don't take up too much space.

This is my HTML:

<div class="billing_results" id="billing_results">
<div class="column-left">
<div id="billingCodesData" style="display: none;">{{ contact.billing_codes }}</div>
<div id="billingCodesDisplay">&nbsp;</div>
</div>
</div>

And this is my script: 

function displaySplitData(rawData, displayTargetId) {
    var dataArray = rawData.split('~~').filter(item => item.trim() !== ''); // Splitting and filtering out empty strings
    var displayDiv = document.getElementById(displayTargetId);
    var charLimit = 40;

    dataArray.forEach(function(item) {
        item = item.trim();

        // Check if the item starts with ", " and remove it
        if (item.startsWith(", ")) {
            item = item.substring(2);
        }

        // Append '...' if the item's length is greater than the character limit
        var displayText = item.length > charLimit ? item.substring(0, charLimit) + '...' : item;

        var para = document.createElement("p");
        para.innerText = displayText;
        displayDiv.appendChild(para);
    });
}

function loadDataOnLoad() {
    displaySplitData(document.getElementById('billingCodesData').innerText, 'billingCodesDisplay');
    displaySplitData(document.getElementById('payerData').innerText, 'payerDisplay');
    displaySplitData(document.getElementById('taxonomyData').innerText, 'taxonomyDisplay');
    displaySplitData(document.getElementById('censusData').innerText, 'censusDisplay');
    displaySplitData(document.getElementById('stateData').innerText, 'stateDisplay');
}

[Levi · Accepted solution]

Hi @ChrisChiha , maybe use the format data action in workflows. See this link. Otherwise export the propertie to Google Sheets or Excel and use the text to columns function with seperator ','. And than import the 2 colomns again in the right propertie. 

[ChrisChiha · Accepted solution]

Hey @Levi ,
 
Thanks!
The export option worked for existing data.
I created a js cusotm code for all new added stuff to have it scalable for the future. the js code will look for the comma and take everything before that comma and store it in a different property field!
 
Best Regards!
@ChrisChiha 

[KcChano22]

@ChrisChiha can you show us to do that JS custom code please?

[ChrisChiha]

Hey @KcChano22 ,
 
Voila: (This code is basically splitting the property field "street" at the comma, and taking the first value and storing it in an output field.
You can then copy the output value to a another property field of your choice.)

const axios = require('axios');

exports.main = async (event, callback) => {
  const street = event.inputFields['street']
  

  var shortstreet = street.split(",")[0]
  
  callback({
    outputFields: {
      proposal_street: shortstreet
    }
  });
}