concatenate distinct text fields from child Records

  • 1
  • Question
  • Updated 3 years ago
  • Answered
Hello,

I'm trying to accomplish the following if possible

trying to concatenate distinct a text field from child records

Example: Account has child records with all types of fruit.

I tried this possible solution from the tutorial : http://help.skuidify.com/s/tutorials/m/models-conditions-filters/l/102520-include-child-relationship...

But it does not accomplish the "distinct" part.

So if let's say 3 of 5 child records have banana

I would get : Banana, Kiwi, Banana, Banana, Apple

would there be any way to get : Banana, Kiwi, Apple and preferably in a existing aggregate model?



I'd like my fruit salad to have equal parts of all fruits ;-)


Thank you
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb

Posted 3 years ago

  • 1
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
sounds like a case for a custom field renderer. I don't see a way to do it without javascript.
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Ok Matt,

By any chance would you or anyone ever done this before and could point me in right direction or share an existing JS?

I'm quite a noob on JS

Thx for info
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Ok, create a ui-only field on your model, of type Text. Drop that field in your table, and choose a custom renderer for it. Maybe name it something like renderUniqueFruits.

Add a new javascript snippet of the same name, with code something like this:

var field = arguments[0],    value = arguments[1],
$ = skuid.$;
var model = field.model,
    row = field.row;
var childRecords = row.ChildRelationshipName.records;
var uniquearray=[];
$.each(childRecords, function(i,r){
    if (uniquearray.indexOf(r.FruitFieldName) == -1) {
        uniquearray.push(r.FruitFieldName);
    }
});
skuid.ui.fieldRenderers[field.metadata.displaytype].read(field,uniquearray);


I tested it out with Accounts and Contacts. Here's the XML:


<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="false" showheader="true" tabtooverride="Account">   <models>
      <model id="Account" limit="100" query="true" createrowifnonefound="false" sobject="Account" adapter="" type="">
         <fields>
            <field id="Name"/>
            <field id="CreatedDate"/>
            <field id="Contacts" type="childRelationship" limit="10">
               <fields>
                  <field id="Name"/>
                  <field id="Title"/>
                  <field id="MobilePhone"/>
                  <field id="Phone"/>
               </fields>
               <conditions/>
            </field>
            <field id="UniqueBusinessPhone" uionly="true" displaytype="TEXT" label="Unique Business Phone"/>
         </fields>
         <conditions/>
         <actions/>
      </model>
   </models>
   <components>
      <pagetitle model="Account" uniqueid="sk-2nEaSS-68">
         <maintitle>
            <template>{{Model.labelPlural}}</template>
         </maintitle>
         <subtitle>
            <template>Home</template>
         </subtitle>
         <actions>
            <action type="savecancel"/>
         </actions>
      </pagetitle>
      <skootable showconditions="true" showsavecancel="false" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" createrecords="true" model="Account" mode="read" uniqueid="sk-2nEaSS-69">
         <fields>
            <field id="Name" valuehalign="" type=""/>
            <field id="CreatedDate" allowordering="true"/>
            <field id="Contacts" type="CHILDREL" limit="100" valuehalign="" delimiter="&lt;p&gt;">
               <label>Contacts</label>
               <template>{{Name}}, {{Title}}
Mobile: {{MobilePhone}}
Business: {{Phone}}</template>
            </field>
            <field id="UniqueBusinessPhone" valuehalign="" type="CUSTOM" snippet="renderUniqueFruits"/>
         </fields>
         <rowactions>
            <action type="edit"/>
            <action type="delete"/>
         </rowactions>
         <massactions usefirstitemasdefault="true">
            <action type="massupdate"/>
            <action type="massdelete"/>
         </massactions>
         <views>
            <view type="standard"/>
         </views>
      </skootable>
   </components>
   <resources>
      <labels/>
      <css/>
      <javascript>
         <jsitem location="inlinesnippet" name="renderUniqueFruits" cachelocation="false">var field = arguments[0],
    value = arguments[1],
$ = skuid.$;
var model = field.model,
    row = field.row;
var childRecords = row.Contacts.records;
var uniquearray=[];
$.each(childRecords, function(i,r){
    if (uniquearray.indexOf(r.Phone) == -1) {
        uniquearray.push(r.Phone);
    }
});

skuid.ui.fieldRenderers[field.metadata.displaytype].read(field,uniquearray);
</jsitem>
      </javascript>
   </resources>
   <styles>
      <styleitem type="background" bgtype="none"/>
   </styles>
</skuidpage>
(Edited)
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Thanx Matt, you're the bomb!

I'll try it out shortly :)
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Ok, sorry to bother you again

I tried on my page , but the page just says loading, and does not load

I looked in console and saw this error

Uncaught TypeError: Cannot read property 'records' of undefined

So thinking I'm maybe doing something wrong or something to do with my page, I tried to do it directly in the test XML you sent me

And got same issue/error

Is because of the field type maybe? (text field)

here's my modfied XML in case it helps

<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="false" showheader="true" tabtooverride="Account">   <models>
      <model id="Account" limit="100" query="true" createrowifnonefound="false" sobject="Account" adapter="" type="">
         <fields>
            <field id="Name"/>
            <field id="CreatedDate"/>
            <field id="Contacts" type="childRelationship" limit="10">
               <fields>
                  <field id="Name"/>
                  <field id="Title"/>
                  <field id="MobilePhone"/>
                  <field id="Phone"/>
               </fields>
               <conditions/>
            </field>
            <field id="UniqueBusinessPhone" uionly="true" displaytype="TEXT" label="Unique Business Phone"/>
         <field id="Bank_Statements__r" type="childRelationship" limit="10">
<fields>
   <field id="Bank_Name__c"/>
</fields>
</field>
</fields>
         <conditions/>
         <actions/>
      </model>
   </models>
   <components>
      <pagetitle model="Account" uniqueid="sk-2nEaSS-68">
         <maintitle>
            <template>{{Model.labelPlural}}</template>
         </maintitle>
         <subtitle>
            <template>Home</template>
         </subtitle>
         <actions>
            <action type="savecancel"/>
         </actions>
      </pagetitle>
      <skootable showconditions="true" showsavecancel="false" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" createrecords="true" model="Account" mode="read" uniqueid="sk-2nEaSS-69">
         <fields>
            <field id="Name" valuehalign="" type=""/>
            <field id="CreatedDate" allowordering="true" valuehalign="" type=""/>
            <field id="Contacts" type="CHILDREL" limit="100" valuehalign="" delimiter="&lt;p&gt;">
               <label>Contacts</label>
               <template>{{Name}}, {{Title}}
Mobile: {{MobilePhone}}
Business: {{Phone}}</template>
            </field>
            <field id="UniqueBusinessPhone" valuehalign="" type="CUSTOM" snippet="renderUniqueFruits"/>
         </fields>
         <rowactions>
            <action type="edit"/>
            <action type="delete"/>
         </rowactions>
         <massactions usefirstitemasdefault="true">
            <action type="massupdate"/>
            <action type="massdelete"/>
         </massactions>
         <views>
            <view type="standard"/>
         </views>
      </skootable>
   </components>
   <resources>
      <labels/>
      <css/>
      <javascript>
         <jsitem location="inlinesnippet" name="renderUniqueFruits" cachelocation="false">var field = arguments[0],
    value = arguments[1],
    $ = skuid.$;
var model = field.model,
    row = field.row;
var childRecords = row.Bank_Statements__r.records;
var uniquearray=[];
$.each(childRecords, function(i,r){
    if (uniquearray.indexOf(r.Bank_Name__c) == -1) {
        uniquearray.push(r.Bank_Name__c);
    }
});

skuid.ui.fieldRenderers[field.metadata.displaytype].read(field,uniquearray);</jsitem>
      </javascript>
   </resources>
   <styles>
      <styleitem type="background" bgtype="none"/>
   </styles>
</skuidpage>


Thx again
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Dave,

There are no records because you haven't added any fields to the Bank_Statements__r child relationship.

Under your model fields on the left menu, click on the bank statements child relationship, and then select the Bank Name field from the list on the right. I think that should work for you.
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Hey Matt ,

Actually the field is already included, since the start

As shown on XML above:

<field id="Bank_Statements__r" type="childRelationship" limit="10">
<fields>
   <field id="Bank_Name__c"/>
</fields>

And here' SS

Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Ah, when I created a page with your xml it didn't show up, since I dint have your custom object. Let me take a deeper look. Did you try the page first without any modifications?
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
The error you're getting means that row.Bank_Records__r is returning undefined. In your snippet, add console.log(row) before you define childRecords, and take a look at the console to see what is actually in the row.
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Yes it worked Fine

Reason I'm wondering why my test version is not working. I copied exactly your steps as far as I know.

The only difference i can see is the type of field we use are different.

I use text field and you used a phone string?
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Thank you, that helped me find issue.

The log showed a lot of different accounts

So realized, I had no conditions set on model :(

Now 1 last stupid question, How do I add a space after the comma that separates entries?

as now it comes out: Apples,Kiwi . I would like to do Apples, Kiwi

Thx Again!

Will try to make it work on my main page now :)
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
I think adding something like this just before the filed renderer will work for you:

uniquearray.join(", ");
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Tried it

var field = arguments[0],
    value = arguments[1],
    $ = skuid.$;
var model = field.model,
    row = field.row;
    console.log(row);
var childRecords = row.Bank_Statements__r.records;
var uniquearray=[];
$.each(childRecords, function(i,r){
    if (uniquearray.indexOf(r.Bank_Name__c) == -1) {
        uniquearray.push(r.Bank_Name__c);
    }
});
uniquearray.join(", ");
skuid.ui.fieldRenderers[field.metadata.displaytype].read(field,uniquearray);

But did not work

But it's ok this is a minor issue, i'll figure it out eventually, the most important, you already helped me accomplish
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
What is it returning? Did you get anything at all, or are you still getting the same?

Well, let's get it right! We're so close.

console.log(uniquearray) before and after the .join() statement to see what they look like.

another thing you could try is to set value like this:

value = uniquearray.join(", ");
skuid.ui.fieldRenderers[field.metadata.displaytype].read(field,value);
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Sorry I did not want to bother you more, you already been so helpful.

But i agree . so close :)

I don't see any error in log . and it seems in console i see a space between both




Solution #2 worked like a charm!

You deserve your forum title of Champion :)


Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Haha, thanks. I'm glad you got it working.
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
I thought it would be simple to get this field's (the UI field containing results on model Account )data into my aggregate model (the one where i need this data, as mentioned on original post) by using UI formula field.. but it seems I was wrong

I'm unable to add it via formula field or any other way I could think of

Seems i cannot lookup UI fields from other model

My use case is the following:

I have a model calls bk_statements
and another to aggregate those statements named bk_statements_AGG

For the AGG model i have a template that summarize a lot of different info, including the # Unique Bk Statements

I want to add next to this number, the Distinct Field's Data you helped me create.

So basically I want that template to show example:

# of Unique Account: 3 ( Apple, Banana, Kiwi)

Any idea?
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
You can definitely access fields from other models with formula fields or just straight into templates with merge syntax. What have you tried? Have you looked at the MODEL_LOOKUP() function in formula fields?

If you have to, you can go with javascript again to make sure you're correclty matching the row in the basic model to the row in the Agg model... but i don't think you should have to do that.

There's also a chance that instead of using child relationships you should just create a new model on the child object to capture the unique names that you want, but I'm not sure what all you've got going on for your page and objects.
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
I'm a little confused about how the objects are related, parent and child. Bank Statements is a child of Account, right?

We were using the Account model to get the list of unique 'fruits.'

If bk_statements and bk_statements_AGG are both on the Bank Statements object (not the account object) you should be able to connect the values with the account id, right?
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Correct, Bk statement is a child of account

Yes, Both bk_statements and bk_statements_AGG Models are based on SF custom object Bank_statement

I tried the following:

From Bk_Statements model & bk_statements_AGG, in fields, went to Account relationship , but the Account UI field (Named UniqueBankName) not showing up in that list


Then tried Via UI Formula Field On Both Models  ( Bk_Statements model & bk_statements_AGG) with "insert field" function



Then tried via Model Lookup function in UI field on both models (this part, I'm not sure i got syntax or proper lookup values correctly

MODEL_LOOKUP("ModelName","FieldNametoBeReturned","FieldNameToMATCHinCurrentModel",{{MatchingValueFromCurrentModel}})

MODEL_LOOKUP("Account","UniqueBankName","FieldNameToMATCHinCurrentModel",{{MatchingValueFromCurrentModel}})

Not sure what these 2 parameters should be replaced with: "FieldNameToMATCHinCurrentModel",{{MatchingValueFromCurrentModel}}). I tried a result field in AGG and tried a bunch of other things, but no idea lol





The reason I assumed you had to do the snippet on the account model, is that that's the only thing that group those records together.
But technically the place where I need the distinct names, is the Bk Agg model ( i do  not need it on account)





Hope it's clearer and i apologize for my confusing posts lol
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Dave, you can't get to ui-only fields through relationships, as far as I know.

Instead of building your renderer to concatonate the unique names on the account object through the child relationships, let's build it on the aggregate model.

Use your grouping field on the agg model (which I'm assuming is Account?), and add it as another column to your table. Then use a custom renderer, something like this:

var field = arguments[0],
    value = arguments[1],   // this will be your account id.
    $ = skuid.$;
var model = skuid.$M('bk_statements');

// Here we'll iterate through all the rows of your bk_statements model,
// and find the ones where the Account__c field matches the Account Id.
// (change that below if your field name is different) 

var uniquearray=[];
$.each(model.getRows(), function(i,r){
if (r.Account__c == value) {    
if (uniquearray.indexOf(r.Bank_Name__c) == -1) {
        uniquearray.push(r.Bank_Name__c);
    }
}
});
var uniqueString = uniquearray.join(", ");

skuid.ui.fieldRenderers.TEXT.read(field,uniqueString);
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Thx a lot Matt,

I'm trying it atm,

Now, you mentioned grouping,  I do not have any grouping on that agg model, just a condition:



But it seems to work correctly as is.

Here are the new issues I got

1- Realized it's case sensitive , so Apple and APPLE , shows as 2 different results.
Not a big problem , as technically i can use a SF formula field to copy over that info and transform all into uppercase. and then run that Snippet on that field instead

2- No matter what I try , i cannot Seem to add that field as part of a template merge

If I show the field by itself (not referenced in template field) it shows fine



Now if i try to make it part of the template field above in SS (in yellow), it does not work
it's suppose to show the names right after the last line.

-That template field is based on same model as the uniqueOnBk field (all on Bk Agg)

Here's template merge i used:
All the merges below are UI only fields, and all work except last 1

<span style="font-weight:normal;">Range:</span> {{Start_date}} <span style="font-weight:normal;">to</span> {{End_date}}
<span style="font-weight:normal;"># of Statements:</span>  {{countStatementDatec}}
<span style="font-weight:normal;"># of unique Bank Accounts:</span> {{countdistinctBankNameAcco}} - {{UniqueBank}}

Looked in console, i do not see any error regarding this


For now it does the job, so if busy or too complicated, it's OK do not bother. I'll leave it as a separate field right under that template field (like it is now)

At this point I'm more curious than anything on the reasons why i cannot make this work

Thx again!
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Dave,

You can use .toUpperCase() or .toLowerCase() in javascript instead of SFDC formula field, if you want.

The issue with the template is that we're using a field renderer to display the distinct bank names. We're not actually saving that value to the model, so that we can access it with merge syntax. Add an updateRow() to the end of your custom renderer snippet.

Here's a new version with both changes:

var field = arguments[0],
    value = arguments[1],   // this will be your account id.
    $ = skuid.$;
var model = skuid.$M('bk_statements');
// Here we'll iterate through all the rows of your bk_statements model, 
// and find the ones where the Account__c field matches the Account Id.
// (change that below if your field name is different) 
var uniquearray=[];
$.each(model.getRows(), function(i,r){
if (r.Account__c == value) {    
if (uniquearray.indexOf(r.Bank_Name__c.toUpperCase()) == -1) {
        uniquearray.push(r.Bank_Name__c.toUpperCase());
    }
}
});
var uniqueString = uniquearray.join(", ");
skuid.ui.fieldRenderers.TEXT.read(field,uniqueString);
var AgModel = skuid.$M('NAMEOFYOURMODEL');  //If we don't already have this... ?
AgModel.updateRow(AgModel.getFirstRow(), {'UniqueBank' : uniqueString});
(Edited)
Photo of Dave

Dave

  • 5,538 Points 5k badge 2x thumb
Good morning,

The Uppercase change worked fine, tyvm

but I still cannot use that field in a template

here's my snippet in case i misunderstood an instruction

var field = arguments[0],
    value = arguments[1],   // this will be your account id.
    $ = skuid.$;
var model = skuid.$M('Bank_statements');

// Here we'll iterate through all the rows of your bk_statements model,
// and find the ones where the Account__c field matches the Ac count Id.
// (change that below if your field name is different)

var uniquearray=[];
$.each(model.getRows(), function(i,r){
if (r.Account__c == value) {   
if (uniquearray.indexOf(r.Bank_Name__c.toUpperCase()) == -1) {
        uniquearray.push(r.Bank_Name__c.toUpperCase());
    }
}
});
var uniqueString = uniquearray.join(", ");

skuid.ui.fieldRenderers.TEXT.read(field,uniqueString);

var AgModel = skuid.$M('Bnk_aggregate1');  //If we don't already have this... ?
AgModel.updateRow(AgModel.getFirstRow(), {'UniqueBank' : uniqueString});

Thx
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Dave,
To address your template issue: the code assumes that you have a ui-only field on your Bnk_Aggregate1 model called 'UniqueBank'.

check the console to see if that value for that model/field is updating correctly:. Just paste this line into the console: 
skuid.$M('Bnk_aggregate1').getFieldValue(skuid.$M('Bnk_aggregate1').getFirstRow(),'UniqueBank');
and see what it returns.
If that value is what you expect, but it isn't showing up in the template, you may need to just rerender the template:
skuid.$C('MyTemplateUniqueId').render();
Try that in the console, if you know the value for UniqueBank is correct (replacing MyTamplateUniqueId with whatever you put as the id for your template component).

If that works, then add the render() line to the end of your custom renderer code.