$100 Amazon Gift Card- High Chart Connect Nulls False

  • 1
  • Question
  • Updated 2 years ago
  • Answered
  • (Edited)
I am trying to get my Salesforce data to chart like this using the Skuid Charts (HighCharts): Note that there is a break in the graphed line when the result was left blank "null".


Here is a link to the JSFiddle HighChart page were this was made possible.
http://jsfiddle.net/gh/get/jquery/1.7...

Currently my data has blanks if no data was recorded, but instead of breaking the line, Skuid Charts shows the dataset with a 0 and no break in the line. In the image below 5/23 and 5/25 should be null values and should have a break. But instead it drops to 0. This erroneously makes it looks like the result was 0 instead of showing that no data was collected by breaking the line.


So I created three different columns of data in an attempt to get null results when the field was left blank. I got close but not quite there.
1. In column 2 below I used a UI only field formula set to return numbers. I used an If Statement to show the number of "null". According to the table results I was successful in getting numbers and the actual lowercase word "null" to show up instead of the blanks. From my research into HighCharts a lowercase null was necessary to create the break in the line.
2. I also tried using a Salesforce formula field called "Calculated Results" (the last column in the table) to show numbers and null results. Unfortunately, in this example I had to list the numbers as text. So not as good as option 1. But both options 1 and 2 graphed the exact same way.(see below)
3. The "Results" column is the actual data entry field you enter the raw data. Numbers are numbers and blanks show up as blanks. This column graphs like the 2nd graph above where it has one continuous line but drops to zero instead of breaking the line.
Here is my table of results.


I thought the UI Only Field would work awesome because it lists numbers and it shows the actual word "null" for the blank fields. It did successfully fail to graph the the null entries (which is awesome) but it also made the whole chart turn into data points with no connections. The same thing happened when graphing number 1 and 2 options.


Can anyone think of a way to connect the data points and leave a gap if it is null (i.e. no one entered any data for those points? Happy to send via email a $100 gift card to whomever can show me an example that will get me to the correct end result.

Thank you!
Photo of Rich Slack

Rich Slack

  • 4,134 Points 4k badge 2x thumb

Posted 2 years ago

  • 1
Photo of Stephen Sells

Stephen Sells, Official Rep

  • 16,856 Points 10k badge 2x thumb
I gave this the best I could. I'm coming to a sense that "no" is the general answer to "is this possible?" I hope someone worthy is able to pull the $100 out of the stone. I am not King Arthur. 

Because of this sneaky suspicion, I'm going to bring this up with the developers and see if it is a functionality they know how to implement. I'll keep you posted on the results of that meeting.

I mention this because, you might want to change the status from Question to Idea so that it receives visibility from our design team. Your call.
Photo of Rich Slack

Rich Slack

  • 4,134 Points 4k badge 2x thumb
Alright $200 :) to pull the sword from the stone.
Photo of Stephen Sells

Stephen Sells, Official Rep

  • 16,856 Points 10k badge 2x thumb
King Arthur shall remain King Anonymous! And generous is He! He refuses the bounty. What a glorious King indeed!

This should help. One of our developers wrote this code himself. It contains snippets you can use in your own. The only caveat is you'll need to hit the re-render button to fix the appearance once you hit save. In all other respects it functions as we expect you'll like.
<skuidpage unsavedchangeswarning="yes" personalizationmode="server" showsidebar="true" useviewportmeta="true" showheader="true">    <models>
        <model id="Oppy" limit="20" query="true" createrowifnonefound="false" datasource="salesforce" type="" sobject="Opportunity" doclone="">
            <fields>
                <field id="Amount" overridemetadata="false" ogdisplaytype="CURRENCY" displaytype="CURRENCY" precision="" scale="" readonly="true" returntype="TEXT" defaultvaluetype="fieldvalue" defaultValue="test" placeholder="test">
                    <formula>IF(ISBLANK({{Amount}}),"null",{{Amount}})</formula>
                </field>
                <field id="Name"/>
                <field id="Id"/>
            </fields>
            <conditions/>
            <actions>
                <action>
                    <actions>
                        <action type="custom" snippet="ReRenderChart"/>
                    </actions>
                    <events>
                        <event>models.saved</event>
                        <event>models.loaded</event>
                        <event>row.updated</event>
                    </events>
                    <fields>
                        <field>Amount</field>
                        <field>Id</field>
                        <field>Name</field>
                    </fields>
                </action>
            </actions>
        </model>
    </models>
    <components>
        <skootable showconditions="true" showsavecancel="true" showerrorsinline="true" searchmethod="server" searchbox="true" showexportbuttons="false" pagesize="10" createrecords="true" model="Oppy" buttonposition="" mode="read" allowcolumnreordering="true" uniqueid="sk-_rmPZ-111">
            <fields>
                <field id="Name" hideable="true" uniqueid="fi-_roRY-125"/>
                <field id="Id" hideable="true" uniqueid="fi-_roRb-126"/>
                <field id="Amount" hideable="true" uniqueid="fi-_roRb-127" valuehalign="" type="" decimalplaces="3"/>
            </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>
        <buttonset model="Oppy" uniqueid="sk--Tx1a-1865">
            <buttons>
                <button type="multi" label="Re-Render">
                    <actions>
                        <action type="custom" snippet="ReRenderChart"/>
                    </actions>
                </button>
            </buttons>
        </buttonset>
        <skuidvis__chart model="Oppy" maintitle="{{Model.labelPlural}}" type="line" uniqueid="sk-_sIut-139" rendersnippet="ChartsMod">
            <dataaxes>
                <axis id="Ammount" integersonly="true"/>
            </dataaxes>
            <categoryaxes>
                <axis id="Names" categorytype="template" field="Name" template="{{Name}}"/>
            </categoryaxes>
            <serieslist>
                <series valuefield="Amount" splittype="none" modelId="Oppy" categoryField="Amount" type="line" splitfield="Id">
                    <actions/>
                </series>
            </serieslist>
            <colors/>
            <legend layout="horizontal" halign="center" valign="bottom"/>
            <renderconditions logictype="and"/>
        </skuidvis__chart>
    </components>
    <resources>
        <labels/>
        <javascript>
            <jsitem location="inlinesnippet" name="ChartsMod" cachelocation="false">var chartObj = arguments[0],
$ = skuid.$;
var points = chartObj.series[0].data; 
    try {              
        for (i = 0; i &lt; points.length; i++) {       
            var point = points[i];
                if (point.y &lt; 1) 
                {
                    point.y = null;
                }
        }
    }              
    catch (e) {
        console.log(e);
              }</jsitem>
            <jsitem location="inlinesnippet" name="ReRenderChart" cachelocation="false">var params = arguments[0],
$ = skuid.$;
skuid.$C("sk-_sIut-139").render(); 
console.log('save action fired');</jsitem>
        </javascript>
        <css/>
    </resources>
    <styles>
        <styleitem type="background" bgtype="none"/>
    </styles>
</skuidpage>
(Edited)
Photo of Rich Slack

Rich Slack

  • 4,134 Points 4k badge 2x thumb
YES!!! Thank you! This got me on the right track! Thank you so much! Please tell King Anonymous that he has made my month!!

There may be better ways to do it but my no skill/non programmer workaround that might help others is as follows. So the above snippet makes a break in the line if the value is Less than 1. There may be entries that are 0 that are legitimate where I don't want a break in the line. I only need a break in the line if the entry is blank (no data collected). So I took these steps:
1. Create a custom formula field called IsBlank__c IF(ISBLANK(RESULTS__c),"null","Number")
2. Create a UI Only Field, Formula, Number, IF({{IsBlank__c}}=="null",-123456789,{{Result__c}})
3. Change the Snippet to ==-123456789 or some other very unlikely number.
4. Set the Chart to graph the UI only field.

Voila! The chart will graph all number except the crazy number you selected which only shows up if the Results__c field is blank. If you show a table of your data you show the real Result__c field so the crazy number is never seen.

Total hack work around but it works!

Now, my only question I have to figure out is I have more than multiple series per Chart and this is only working on the first series. var points = chartObj.series[0].data; I assume I just have to add to the snippet for Series[1]
series[2] etc.

Thank you Stephen Sells and the Anonymous person behind the snippets. It means a lot! Thank you!
Photo of Rich Slack

Rich Slack

  • 4,134 Points 4k badge 2x thumb
Alright, i am back to beg.  I am using this code that was generously given to me:

var chartObj = arguments[0], $ = skuid.$;var series = chartObj.series[0].data;
    try {
        
        for (i = 0; i < series.length; i++) {
            
            var point = series[i];
                if (point.y == -123456789) 
                {
                    point.y = null;
                    
                }
        }
    } 
        catch (e) {
        console.log(e);
}
I made some minor tweaks to the code and it works perfectly for one series. However, my chart has multiple series (up to 15).  How do I define multiple series and make it work for all series on a chart not just the first one?  I tried identifying a chartobj.series[1].data etc. but it broke the page.  It seems like I need it to loop through the data and define all the series and then loop through all the series and change the value of those equaling -123456789 to null.  

I read a lot of posts last night and looked at a lot of examples but rarely did they deal with the subject of multiple series.

Thank you!
(Edited)
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,050 Points 10k badge 2x thumb
When you say that changing the code to chartobj.series[1] broke your page, what errors did you see?
Photo of Josh Merritt

Josh Merritt

  • 918 Points 500 badge 2x thumb
Hi Rich,
I didn't fully follow your original posting, but you should be able to use a simple "chart.series.length" to iterate through each series on your chart. It looks like you're almost there. Here's a stab at updating your code, although you may need to add some checks that the series has a length. 

var chartObj = arguments[0], $ = skuid.$;
var seriesLength = chartObj.series.length;
    try {
        
        for (i = 0; i < seriesLength; i++) {
            for(j = 0; j < chartObj.series[i].length; j++) {             var point = chartObj.series[i].data[j];
                if (point.y == -123456789) 
                {
                    point.y = null;  
                } )
        }
    } 
        catch (e) {
        console.log(e);

 
Here's also a helpful example of something similar thanks to Pat:
https://community.skuid.com/skuid/topics/cumulative-chart
(Edited)
Photo of Rich Slack

Rich Slack

  • 4,134 Points 4k badge 2x thumb
Thanks for sticking with me to help me find the resolution.

To show what is happening with multiple series on the same chart this is what I am seeing with the snippet left as is.
var chartObj = arguments[0], $ = skuid.$;var series = chartObj.series[0].data;
    try {
        
        for (i = 0; i < series.length; i++) {
            
            var point = series[i];
                if (point.y == -123456789) 
                {
                    point.y = null;
                    
                }
        }
    } 
        catch (e) {
        console.log(e);
}


Note the blue line actually breaks like I want it to. Whereas the black line shoots downward presumably toward -123456789.  :)  Ideally both lines would break like the blue line.
_______

Hi Mark,
In answer to your question, if I keep the same code as posted above and all I do is open the snippet and change the 0 in:
chartObj.series[0].data;

to 1 for example:

chartObj.series[1].data;The page no longer loads and the error in the console log is:
TypeError: chartObj.series[1] is undefined.
_______
Josh,
Thank you very much for jumping in. I tried the code and something is off still.  I am sure you are on the right track, it is just doesn't appear to do anything with the addition of the for(J=0; etc..  I will read up on post you added to see if I can't see another way of looking at it.

This is the code I am trying using .length.

var chartObj = arguments[0], $ = skuid.$;var seriesLength = chartObj.series.length;        for (i = 0; i < seriesLength; i++)             for(j = 0; j < chartObj.series[i].length; j++) {             var point = chartObj.series[i].data[j];                if (point.y == -123456789)                 {                    point.y = null;                  }         }    }

This code doesn't break the page, but the black line still drops down vertically toward -123456789. The blue line continues to work correctly. I need blue line behavior on every line.

Thank you both!
Rich
(Edited)
Photo of Josh Merritt

Josh Merritt

  • 918 Points 500 badge 2x thumb
You might need to define your variables i and j using var i = 0, rather than just i = 0. If that doesn't work, we'll have to hope for someone else to chime as that's all the tricks in my bag.

There's a standard programming operator called forEach that is used for processing arrays, so that's another option to try if you can't get your for statements to iterate through all the data.
Photo of Rich Slack

Rich Slack

  • 4,134 Points 4k badge 2x thumb
Thanks Josh! I will give it a shot.