Hello all,

I've been working on some API integration between Salesforce and SQL using Powershell. 

So far I've managed to get the majority working with querying Salesforce for some record details, grabbing information from SQL and inserting the SQL data into the Salesforce records. 

However, I'm having some trouble trying to get it to create a new record. 

We have a custom object (Usage_Billings__c) which the SQL data is put into. Inserting this into existing records is fine but if I try to create a new record it fails with error "Unexpected character ('P' (code 80)): was expecting comma to separate OBJECT entries at [line:3, column:33]","errorCode":"JSON_PARSER_ERROR"

I'm fairly new to API integrations and haven't had much luck with finding a solution online (according to some posts, what I'm doing should work fine). 

My Script:

(I've omitted our SF connection section which works fine as can gather the information fine as well as our sandbox URL)
 

$GUID = "810Pu000001MruoIAC"

$getContracturl = "https://MYSANDBOX.my.salesforce.com/services/data/v57.0/query?q=SELECT+FIELDS(ALL)+FROM+servicecontract+WHERE+id+=+'$GUID'+LIMIT+50"
$getContractResponse = Invoke-RestMethod -Uri $getContracturl -Method 'Get' -Headers $headers -TimeoutSec 0.03
$getContractResponse | ConvertTo-Json -Depth 10

$accountid = $getContractResponse.records.accountid
$dat_size = "150"
$lic_365 = "1"
$lic_google = "1"



$createUrl = "https://MYSANDBOX.my.salesforce.com/services/data/v57.0/sobjects/Usage_Billing__c"
$requestbody =@"
{

    "Service_Contract__c" : $GUID,
    "Stor__c" : $dat_size,
    "X365_Users__c" : $lic_365,
    "G_Users__c" : $lic_google
 


}
"@


Write-Host $requestbody

$createBilling = Invoke-RestMethod -Uri $createUrl -Method 'Post' -Headers $headers -Body $requestbody -TimeoutSec 0.03
$createBilling | ConvertTo-JSON

Anyone able to point me in the right direction?

All help appreciated.

Andy
 

Hello,

I have an account controller extension as follows which is used by a basic visualforce page to display service statistics for the customer accounts.
 

public with sharing class CSRCntrlExt2 {

    Public final Account acct;
    
    // Set Public Variables
    
    Public Integer tslaok30 {get; private set;}
    Public Integer tallraised30 {get; private set;}
        
    //Get Account Record
    
		public CSRCntrlExt2(ApexPages.StandardController stdController) {
        this.acct = (Account)stdController.getRecord();    

    //Get Number of tickets in last 30 days where SLA has been met.
    tslaok30 = [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30 AND Sla_Status__c!='Missed'];
                
	//Get Number of tickets raised in last 30 days
    tallraised30 = [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30];              
            
        }    
    
}

I'm wanting to get the percentage of the two variables but having trouble doing so.

I managed to use the following on the VF page:

<apex:outputtext>{! (tslaok30 / tallraised30)*100}%</apex:outputtext>

However, it appends a large number of decmial places to the number, which I don't need (just need whole number, rounded up if possible).

I've also been informed that its not good practice to do the maths in VF and I should use a wrapper class to output the data to another variable?

My skill with Apex is not great so was wondering if someone could help me get this sorted?

Any help appreciated. 

Thanks

Andy ​​​​​​​

Hello

I've put together an account controller extension for a Visualforce report page I'm working. The extension returns a number of integers from count queries on case objects:
 

public with sharing class CSRCntrlExt1 {
    
    Private final Account acct;
    
    // Set Public Variables
    
    Public Integer traisedlast30 {get; set;}
    Public Integer tclosedlast30 {get; set;}
    Public Integer tp1last30 {get; set;}
    Public Integer tp2last30 {get; set;}
    Public Integer tp3last30 {get; set;}
    Public Integer tp4last30 {get; set;}
    Public Integer traisedlast180 {get; set;}
    Public Integer tflexlast30 {get; set;}
    Public Integer tcasplast30 {get; set;}
    Public Integer tmanagedlast30 {get; set;}
       
        
    //Get Account Record
    
		public CSRCntrlExt1(ApexPages.StandardController stdController) {
        this.acct = (Account)stdController.getRecord();    

    //Get Number of tickets raised in last 30 days
    traisedlast30 = [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30];
            
    //Get Number of tickets closed in last 30 days
    tclosedlast30 = [Select count() FROM Case WHERE AccountId =:acct.Id AND status='Closed' AND ClosedDate <= LAST_N_DAYS:30];
            
    //Get Number of P1 tickets raised in last 30 days
    tp1last30 =  [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30 AND Priority = 'P1'];       
            
    //Get Number of P2 tickets raised in last 30 days
    tp1last30 =  [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30 AND Priority = 'P2']; 
            
    //Get Number of P3 tickets raised in last 30 days
    tp1last30 =  [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30 AND Priority = 'P3']; 
            
    //Get Number of P4 tickets raised in last 30 days
    tp1last30 =  [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30 AND Priority = 'P4']; 
    
    //Get Number of tickets raised in last 6 months
    traisedlast180 = [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:180];
            
    //Get Number of Flexsupport tickets raised in last 30 days
    tflexlast30 = [Select count() FROM Case WHERE AccountId=:acct.id AND CreatedDate <= LAST_N_DAYS:30 AND Type = 'Flexsupport'];
            
    //Get Number of CASP tickets raised in last 30 days
    tcasplast30 = [Select count() FROM Case WHERE AccountId=:acct.id AND CreatedDate <= LAST_N_DAYS:30 AND Type = 'CASP'];
    
    //Get Number of Managed Services tickets raised in last 30 days
    tmanagedlast30 = [Select count() FROM Case WHERE AccountId=:acct.id AND CreatedDate <= LAST_N_DAYS:30 AND Type = 'Managed Services']; 
            
            
        }    
            
}

I'm trying to put together a test class for this to allow me to upload it into production but really struggling with knowing where to start. So far I have:
 

@isTest
private class testCSRCntrlExt1 {
   
    static testMethod void Test1(){
        
        //Create Account
        Account acc = new Account(name='testAccount');
        insert acc;
        
        //Create Contact
        Contact con = new Contact(lastname='Test', Accountid=acc.id);
        insert con;
        
        //Create Case
        Case cas = new Case(accountid=acc.id, type='Flexsupport', Priority='P3', contactid=con.id, subject='Test', Status='new');
        insert cas;     
        
        //Create the Controller
        ApexPages.StandardController accountController = new ApexPages.StandardController(acc);
        
        //Create the instance of the Controller
        CSRCntrlExt1 ext1 = new CSRCntrlExt1(accountController);
        
        
        
    }  
   
}

Not even sure what I have is correct (still trying to get my head around Apex coding).

Anyone able to assist in creating a test class for this? I think all that's needed is assertion of values returned, but not sure on the syntax for that.

Any help appreciated as always. 

Andy

Hello

I have a visualforce page (CustomerServiceReport) that uses the standard Account controller and an extension (CustoemrServiceReportExtension) to pull through some basic information about cases relating to that Account (i.e. number of cases raised in last 30 days, number closed, etc). 

I want to expand on this to show the contacts and number of cases they have raised in the last 30 days (ideally restricted to the top 10 with ordered by highest to lowest). For the life of me I don't seem to be able to get it working. All I will need is the Contact's Name the case is raised under and the number of cases they have raised.

I'm fairly new to Visualforce and Apex so still trying to get to grips with it. This is a bit of a side-project (which may make my employer's life a bit easier) that I figure may help me improve my skills. 

Anyone able to help me out? I'm fine with it been a secondary extension if it makes things easier/tidier.  

I'm new to apex coding and have run into a bit of a problem which I'm hoping someone can help with. 

Basically, I've been tasked with a Salesforce deployment for my company a part of which requires a customer portal for viewing cases. Following some early prototyping we decided to use a piece of apex programming to display the case list in a format that is suited to our needs. The list basically consists of a drop down box that has two options, depending on the option chosen the class will pull data from Salesforce. this is being used on a visualforce page that is embedded into the community site.

I've used this code in Sandox with no issues but when I try to deploy to Production it fails due to only 71% code coverage. 
 

public class CommunityCaseList {

public string options;
       
    public List<Case> getNewCases() {
    
        if(options=='Open')
        {
        
        List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status != 'Closed' ORDER BY CreatedDate DESC];
            return results;
        }
    
        else if(options == 'Closed'){
            
            List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status = 'Closed' ORDER BY CreatedDate DESC];
            return results;            
        }
        
        else {
            
            List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status != 'Closed' ORDER BY CreatedDate DESC];
            return results;
            
        }
    
    
    
    }
    
    public string getOptions() {
        return this.options;
    }
    public void setOptions(String Opt) {
        this.options=opt;
        
    }
    public list<SelectOption> getItems() {
      List<selectOption> options = new List<SelectOption>();
        options.add(new Selectoption('Open','Open'));
        options.add(new Selectoption('Closed','Closed'));
        return options;
        
    }
    public pagereference preview() {
        return null;
    }    

}

I've read up on it and believe I need to create a test class to run the code but when I'm trying to create one it displays the code as incorrect. This is the test class:
 

@isTest

private class CommunityCaseListTest {
 
    static testMethod void validateCommunityCaseList() {


public string options;
       
    public List<Case> getNewCases() {
    
        if(options=='Open')
        {
        
        List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status != 'Closed' ORDER BY CreatedDate DESC];
            return results;
        }
    
        else if(options == 'Closed'){
            
            List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status = 'Closed' ORDER BY CreatedDate DESC];
            return results;            
        }
        
        else {
            
            List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status != 'Closed' ORDER BY CreatedDate DESC];
            return results;
            
        }
    
    
    
    }
    
    public string getOptions() {
        return this.options;
    }
    public void setOptions(String Opt) {
        this.options=opt;
        
    }
    public list<SelectOption> getItems() {
      List<selectOption> options = new List<SelectOption>();
        options.add(new Selectoption('Open','Open'));
        options.add(new Selectoption('Closed','Closed'));
        return options;
        
    }
    public pagereference preview() {
        return null;
    }    

}

}

I'm not quite sure what I'm doing wrong or if there's an easier way to achieve the code coverage required. The tutorials I've read so far are for creating test classes that manipulate data whereas this is simply used to get data.

If anyone can help point me int he right direction it would be greatly appreciated. 

Many thanks

Andy

Hello all,

I've been working on some API integration between Salesforce and SQL using Powershell. 

So far I've managed to get the majority working with querying Salesforce for some record details, grabbing information from SQL and inserting the SQL data into the Salesforce records. 

However, I'm having some trouble trying to get it to create a new record. 

We have a custom object (Usage_Billings__c) which the SQL data is put into. Inserting this into existing records is fine but if I try to create a new record it fails with error "Unexpected character ('P' (code 80)): was expecting comma to separate OBJECT entries at [line:3, column:33]","errorCode":"JSON_PARSER_ERROR"

I'm fairly new to API integrations and haven't had much luck with finding a solution online (according to some posts, what I'm doing should work fine). 

My Script:

(I've omitted our SF connection section which works fine as can gather the information fine as well as our sandbox URL)
 

$GUID = "810Pu000001MruoIAC"

$getContracturl = "https://MYSANDBOX.my.salesforce.com/services/data/v57.0/query?q=SELECT+FIELDS(ALL)+FROM+servicecontract+WHERE+id+=+'$GUID'+LIMIT+50"
$getContractResponse = Invoke-RestMethod -Uri $getContracturl -Method 'Get' -Headers $headers -TimeoutSec 0.03
$getContractResponse | ConvertTo-Json -Depth 10

$accountid = $getContractResponse.records.accountid
$dat_size = "150"
$lic_365 = "1"
$lic_google = "1"



$createUrl = "https://MYSANDBOX.my.salesforce.com/services/data/v57.0/sobjects/Usage_Billing__c"
$requestbody =@"
{

    "Service_Contract__c" : $GUID,
    "Stor__c" : $dat_size,
    "X365_Users__c" : $lic_365,
    "G_Users__c" : $lic_google
 


}
"@


Write-Host $requestbody

$createBilling = Invoke-RestMethod -Uri $createUrl -Method 'Post' -Headers $headers -Body $requestbody -TimeoutSec 0.03
$createBilling | ConvertTo-JSON

Anyone able to point me in the right direction?

All help appreciated.

Andy
 

Hello

I've put together an account controller extension for a Visualforce report page I'm working. The extension returns a number of integers from count queries on case objects:
 

public with sharing class CSRCntrlExt1 {
    
    Private final Account acct;
    
    // Set Public Variables
    
    Public Integer traisedlast30 {get; set;}
    Public Integer tclosedlast30 {get; set;}
    Public Integer tp1last30 {get; set;}
    Public Integer tp2last30 {get; set;}
    Public Integer tp3last30 {get; set;}
    Public Integer tp4last30 {get; set;}
    Public Integer traisedlast180 {get; set;}
    Public Integer tflexlast30 {get; set;}
    Public Integer tcasplast30 {get; set;}
    Public Integer tmanagedlast30 {get; set;}
       
        
    //Get Account Record
    
		public CSRCntrlExt1(ApexPages.StandardController stdController) {
        this.acct = (Account)stdController.getRecord();    

    //Get Number of tickets raised in last 30 days
    traisedlast30 = [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30];
            
    //Get Number of tickets closed in last 30 days
    tclosedlast30 = [Select count() FROM Case WHERE AccountId =:acct.Id AND status='Closed' AND ClosedDate <= LAST_N_DAYS:30];
            
    //Get Number of P1 tickets raised in last 30 days
    tp1last30 =  [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30 AND Priority = 'P1'];       
            
    //Get Number of P2 tickets raised in last 30 days
    tp1last30 =  [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30 AND Priority = 'P2']; 
            
    //Get Number of P3 tickets raised in last 30 days
    tp1last30 =  [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30 AND Priority = 'P3']; 
            
    //Get Number of P4 tickets raised in last 30 days
    tp1last30 =  [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:30 AND Priority = 'P4']; 
    
    //Get Number of tickets raised in last 6 months
    traisedlast180 = [Select count() FROM Case WHERE AccountId =:acct.Id AND CreatedDate <= LAST_N_DAYS:180];
            
    //Get Number of Flexsupport tickets raised in last 30 days
    tflexlast30 = [Select count() FROM Case WHERE AccountId=:acct.id AND CreatedDate <= LAST_N_DAYS:30 AND Type = 'Flexsupport'];
            
    //Get Number of CASP tickets raised in last 30 days
    tcasplast30 = [Select count() FROM Case WHERE AccountId=:acct.id AND CreatedDate <= LAST_N_DAYS:30 AND Type = 'CASP'];
    
    //Get Number of Managed Services tickets raised in last 30 days
    tmanagedlast30 = [Select count() FROM Case WHERE AccountId=:acct.id AND CreatedDate <= LAST_N_DAYS:30 AND Type = 'Managed Services']; 
            
            
        }    
            
}

I'm trying to put together a test class for this to allow me to upload it into production but really struggling with knowing where to start. So far I have:
 

@isTest
private class testCSRCntrlExt1 {
   
    static testMethod void Test1(){
        
        //Create Account
        Account acc = new Account(name='testAccount');
        insert acc;
        
        //Create Contact
        Contact con = new Contact(lastname='Test', Accountid=acc.id);
        insert con;
        
        //Create Case
        Case cas = new Case(accountid=acc.id, type='Flexsupport', Priority='P3', contactid=con.id, subject='Test', Status='new');
        insert cas;     
        
        //Create the Controller
        ApexPages.StandardController accountController = new ApexPages.StandardController(acc);
        
        //Create the instance of the Controller
        CSRCntrlExt1 ext1 = new CSRCntrlExt1(accountController);
        
        
        
    }  
   
}

Not even sure what I have is correct (still trying to get my head around Apex coding).

Anyone able to assist in creating a test class for this? I think all that's needed is assertion of values returned, but not sure on the syntax for that.

Any help appreciated as always. 

Andy

Hello

I have a visualforce page (CustomerServiceReport) that uses the standard Account controller and an extension (CustoemrServiceReportExtension) to pull through some basic information about cases relating to that Account (i.e. number of cases raised in last 30 days, number closed, etc). 

I want to expand on this to show the contacts and number of cases they have raised in the last 30 days (ideally restricted to the top 10 with ordered by highest to lowest). For the life of me I don't seem to be able to get it working. All I will need is the Contact's Name the case is raised under and the number of cases they have raised.

I'm fairly new to Visualforce and Apex so still trying to get to grips with it. This is a bit of a side-project (which may make my employer's life a bit easier) that I figure may help me improve my skills. 

Anyone able to help me out? I'm fine with it been a secondary extension if it makes things easier/tidier.  

I'm new to apex coding and have run into a bit of a problem which I'm hoping someone can help with. 

Basically, I've been tasked with a Salesforce deployment for my company a part of which requires a customer portal for viewing cases. Following some early prototyping we decided to use a piece of apex programming to display the case list in a format that is suited to our needs. The list basically consists of a drop down box that has two options, depending on the option chosen the class will pull data from Salesforce. this is being used on a visualforce page that is embedded into the community site.

I've used this code in Sandox with no issues but when I try to deploy to Production it fails due to only 71% code coverage. 
 

public class CommunityCaseList {

public string options;
       
    public List<Case> getNewCases() {
    
        if(options=='Open')
        {
        
        List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status != 'Closed' ORDER BY CreatedDate DESC];
            return results;
        }
    
        else if(options == 'Closed'){
            
            List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status = 'Closed' ORDER BY CreatedDate DESC];
            return results;            
        }
        
        else {
            
            List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status != 'Closed' ORDER BY CreatedDate DESC];
            return results;
            
        }
    
    
    
    }
    
    public string getOptions() {
        return this.options;
    }
    public void setOptions(String Opt) {
        this.options=opt;
        
    }
    public list<SelectOption> getItems() {
      List<selectOption> options = new List<SelectOption>();
        options.add(new Selectoption('Open','Open'));
        options.add(new Selectoption('Closed','Closed'));
        return options;
        
    }
    public pagereference preview() {
        return null;
    }    

}

I've read up on it and believe I need to create a test class to run the code but when I'm trying to create one it displays the code as incorrect. This is the test class:
 

@isTest

private class CommunityCaseListTest {
 
    static testMethod void validateCommunityCaseList() {


public string options;
       
    public List<Case> getNewCases() {
    
        if(options=='Open')
        {
        
        List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status != 'Closed' ORDER BY CreatedDate DESC];
            return results;
        }
    
        else if(options == 'Closed'){
            
            List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status = 'Closed' ORDER BY CreatedDate DESC];
            return results;            
        }
        
        else {
            
            List<Case> results = [SELECT id,CaseNumber,Status,Priority,Accountid,Account.Name,Contactid,contact.name,subject,CreatedDate,ClosedDate FROM Case WHERE Status != 'Closed' ORDER BY CreatedDate DESC];
            return results;
            
        }
    
    
    
    }
    
    public string getOptions() {
        return this.options;
    }
    public void setOptions(String Opt) {
        this.options=opt;
        
    }
    public list<SelectOption> getItems() {
      List<selectOption> options = new List<SelectOption>();
        options.add(new Selectoption('Open','Open'));
        options.add(new Selectoption('Closed','Closed'));
        return options;
        
    }
    public pagereference preview() {
        return null;
    }    

}

}

I'm not quite sure what I'm doing wrong or if there's an easier way to achieve the code coverage required. The tutorials I've read so far are for creating test classes that manipulate data whereas this is simply used to get data.

If anyone can help point me int he right direction it would be greatly appreciated. 

Many thanks

Andy