+ Start a Discussion
Nerv123Nerv123 

Master - detail Scontrol help please

The situation is that an employee can submit time against several projects for a particular month/year.

 

Parent/Master Table: Key Staff Assignment

Fields: Project, Employee

 

Child/Detail Table: Assigning Project Time By Employee

Fields: Year, Month, Number of Days, Monthly Utilization of ALL Projects

 

When the Button “Recalculate Monthly Utilization” on the child Detail Page is clicked, then we have to assign a custom field,

Monthly Utilization of ALL Projects = Sum of All the Project records for that Employee for that month and year (thus, there should be an exact match on employee, month and year)

 

How do I write the Scontrol for this? I took the Scontrol from the cookbook for Rollup Summary fields and am trying to modify it with no success...can anyone take a look and help this newbie, please. This code does look like it is over-engineering a possibly simple solution, but I cant figure out how to work it.

 

<html>
<head>
<script type="text/javascript" src="/js/functions.js"></script>
<script src="/soap/ajax/10.0/connection.js"></script>
<style>
.data {
 font-family:'Arial','Helvetica','sans-serif';
 font-size:12px;
 padding: 2px 0px;
 background-color: #F0F0F0;
}
</style>
<script type="text/javascript">
/* The getTotal() function contains all of the code that should
execute after the page is rendered. The function's
separation allows the query processing to be performed
asynchronously. */
function getTotal() {

 /* This establishes the state that you need when the callback is called. */
 var state = {
  output : document.getElementById("total"),
  /* This code is part of the standard AJAX toolkit code, but startTime is not used in this example. */
  startTime : new Date().getTime()
 };

 /* This is the callback handler, which tells the function what to do when the response from the query comes back. */
 var callback = {
  //call calculateResult if the request is successful
  onSuccess: calculateResults,
  //call queryFailed if the api request fails
  onFailure: queryFailed,
  source: state
 };

 /* This next call is the key to the function.
  The second argument is the callback handler. Since this code is part of an s-control, there's no need to
  log in to Salesforce before executing this call--the context is already established. Also notice the use of the
  Account.Id merge field, which is populated from the context of the page on which this s-control resides. */


 sforce.connection.query("SELECT Assigning_Project_Time_by_Employee__c.Number_of_Days__c, Name, Id FROM Assigning_Project_Time_by_Employee__c+
          " WHERE
dummyYear = Assigning_Project_Time_by_Employee__c.Year__c AND
dummyMonth = Assigning_Project_Time_by_Employee__c.Month__c AND
//Employee = '{!Assigning_Project_Time_by_Employee__c.ECS_Employee__c}' AND
Assigning_Project_Time_by_Employee__c.Number_of_Days__c <> 0",callback);
}


/* The calculateResults() function processes the results if the
query is successful. The first argument is the response from the
sforce query and the second argument is the handle to the state
variable which includes the section of the page to write to. */
function calculateResults(queryResult, source) {

 //Declare and initialize the variable that holds the total.
 var total = 0;

 //Assign the results of the query to an array.
 records = queryResult.getArray('records');

 //Iterate over the results and sum the total.
 for(var n in records) {
  var opp = records[n];
  //Add the current premium's amount to the running total.
  total += opp.getFloat('{!Assigning_Project_Time_by_Employee__c.Number_of_Days__c}');
//  total += opp.getFloat(Assigning_Project_Time_by_Employee__c.Number_of_Days__c);

 }

 //Get the appropriate currency symbol using the
 //getUserInfoResult utility call
 var guir = sforce.connection.getUserInfo();

 //Assign the value to the output area if there was no error.
 //The formatCurrency() function is called here (see "Formatting a Currency in an S-Control")
 //source.output.innerHTML = guir.currencySymbol + formatCurrency(total);

                     source.output.innerHTML = total;
}


//The queryFailed() function assigns an error message if the
//SOQL query failed.
function queryFailed(error, source) {
 source.output.innerHTML = "ERROR!";
 alert(error);
}
</script>

<!-- This syntax pulls in an s-control snippet that formats the
Javascript number into the appropriate currency display
(commas and 2 decimal places). See "Formatting a Currency in an S-Control" -->
 {!INCLUDE($SControl.Format_Number_as_Currency)}

</head>

<!-- The onLoad event calls the getTotal() function to process the
 calculation -->
<body onLoad="getTotal();">
 <!-- This element is initialized with an informational message which
 is asynchronously updated with the value we want. -->
 <div class="data" id="total">Loading...</div>
</body>
</html>

jrotensteinjrotenstein
I'm a little confused about where you're getting your "input".

Do people click the button only after having saved the Assigning_Project_Time_by_Employee record, or is it while editing? Since you want to update a field on the screen, it sounds like it is while editing, which means the values typed on the screen are not yet saved to the Salesforce Database, so your SOQL query can't access the values.

Also, your query is accessing variable names directly, eg:

   dummyMonth = Assigning_Project_Time_by_Employee__c.Month__c

These variables are not available in JavaScript. Rather, you should put them in curly braces so that Salesforce will insert the values in place, much like you have in your commented-out line:

   Employee = '{!Assigning_Project_Time_by_Employee__c.ECS_Employee__c}'

I would recommend that you get your browser to Show Errors so that you can see where your code is failing. It should highlight errors such as the above. Start with a simpler version of your script (eg just updating the output field, then trying to retrieve a value, and finally doing the calculations) so that you can learn what the Cookbook example is doing. You may find it easier to write a synchronous query without all that Callback stuff, especially if you haven't done S-Controls before.