concatenate distinct text fields from child Records

Dave · December 16, 2015, 8:45pm

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

Matt_Sones · December 16, 2015, 9:26pm

sounds like a case for a custom field renderer. I don’t see a way to do it without javascript.

Dave · December 16, 2015, 10:20pm

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

Matt_Sones · December 17, 2015, 6:52pm

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&#46;$;
var model = field&#46;model,
    row = field&#46;row;
var childRecords = row&#46;ChildRelationshipName&#46;records;
var uniquearray=[];
$&#46;each(childRecords, function(i,r){
    if (uniquearray&#46;indexOf(r&#46;FruitFieldName) == -1) {
        uniquearray&#46;push(r&#46;FruitFieldName);
    }
});
skuid&#46;ui&#46;fieldRenderers[field&#46;metadata&#46;displaytype]&#46;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&#46;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="&amp;lt;p&amp;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&#46;$;
var model = field&#46;model,
    row = field&#46;row;
var childRecords = row&#46;Contacts&#46;records;
var uniquearray=[];
$&#46;each(childRecords, function(i,r){
    if (uniquearray&#46;indexOf(r&#46;Phone) == -1) {
        uniquearray&#46;push(r&#46;Phone);
    }
});

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

Dave · December 17, 2015, 8:19pm

Thanx Matt, you’re the bomb!

I’ll try it out shortly

Dave · December 17, 2015, 9:37pm

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

{{Model.labelPlural}}

Home

Contacts
{{Name}}, {{Title}}
Mobile: {{MobilePhone}}
Business: {{Phone}}

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);

Thx again

Matt_Sones · December 18, 2015, 1:49pm

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.

Dave · December 18, 2015, 2:32pm

Hey Matt ,

Actually the field is already included, since the start

As shown on XML above:

And here’ SS

Matt_Sones · December 18, 2015, 2:40pm

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?

Matt_Sones · December 18, 2015, 2:43pm

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.

Dave · December 18, 2015, 2:44pm

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?

Dave · December 18, 2015, 3:12pm

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

Matt_Sones · December 18, 2015, 3:21pm

I think adding something like this just before the filed renderer will work for you:

uniquearray.join(", ");

Dave · December 18, 2015, 3:34pm

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

Matt_Sones · December 18, 2015, 3:39pm

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);

Dave · December 18, 2015, 3:50pm

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

Matt_Sones · December 18, 2015, 4:02pm

Haha, thanks. I’m glad you got it working.

Dave · December 18, 2015, 6:02pm

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?

Matt_Sones · December 18, 2015, 6:40pm

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.

Matt_Sones · December 18, 2015, 6:46pm

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?