+ Start a Discussion
SL TanSL Tan 

Is this concept workable?

Hi

I wonder if anyone has come across this scenario which I am trying to solve.

Firstly, I have a Custom Object called "Exclusive Contract" in which a customer can place order in advance for example 10,000 units of a product, and the customer is allowed to take delivery of the quantity over a period of months that will be stipulated in the Exclusive Contract.

Secondly, the business procedure requires that a Sub-Contract, say A, is issued when the customer takes the first delivery, for example say 2,000 units. (Sub-Contracts have to be issued as new details are added) This is followed by other Sub-Contracts B, C, etc, being issued until the customer takes up all the 10,000 units. The Sub-Contracts are also custom objects placed in the Related List of the "Exclusive Contract" object.

When Sub-Contract A is issued, the Sub-Contract A will show the original qty of 10,000 units, the required quantity of 2,000 units and the balance of 8,000 units. This means that when Sub-contract B is issued and if the quantity for B is 3,000 units, the Sub-Contract B must show a balance of 5,000 units (10,000 - 2,000 - 3,000 units)

My problem is how to handle the formula for the balance for Sub-Contracts B, C, onwards, because the coding must take care of the fact that there can be two or more unknown number of Sub-Contracts that will be issued until the original 10,000 units are taken up. I found that I could not use the Formula method as this can only use for the custom object concerned only.

Has anyone handled such a situation before and if so, would it be better to use an SControl or Custom Link? I am in favour of using a Custom Link with a URL method if this is at all possible but I cannot visualise if this method can handle formulas and how to go about it. Ideally what I hope to achieve is that when a user creates a new Sub-Contract, the field for the Balance Quantity automatically shows the balance against the Exclusive Contract once the user inputs the quantity for that particular Sub-Contract.

I really do hope someone has come across the same scenario and is able to advise me on how to manage this situation. Any pointers will be highly appreciated.
Thanks in advance
SL
sfdcfoxsfdcfox
There are several ways to approach this using today's technology (Apex Code would solve the problem elegantly, but it can not be used in production). You could create two buttons; one on the contract, and one on the sub-contract (I believe in making it possible to update records from as many places as possible). The code for this would look like:

SubContract (child) Custom Button/link:
{!RequireScript("/soap/ajax/9.0/connection.js")}
try
{ Contract__c = sforce.connection.retrieve('Total_Amount__c','Contract__c',['{!SubContract__c.Contract__c}'])[0]
SubContracts__c = sforce.connection.query('select id,amount__c from subcontract__c where contract__c = \'{!SubContract__c.Contract__c}\'')
Contract__c['Remaining_Amount__c'] = Contract__c.getFloat('Total_Amount__c')
QueryResults = new sforce.QueryResultsIterator(SubContracts__c)
while(QueryResults.hasNext())
{ SubContract__c = QueryResults.next()
Contract__c['Remaining_Amount__c'] -= SubContract__c.getFloat('Amount__c')
}
sforce.connection.update([Contract__c])
} catch(e)
{ alert('Error: '+e.message?e.message:e.faultCode?e.faultCode:e)
}
{!SubContract__c.Contract__c} would be replaced with {!Contract__c.Id} for the custom button for the Contract__c (parent) object. To create the buttons, choose Setup | Build | Custom Objects | <object name> | New <Custom Buttons and Links>; choose the "Behavior" type of "Execute JavaScript" and the "Content Source" to "OnClick JavaScript".

This is as about as easy as it gets. I even threw in error catching capabilities.

~ sfdcfox ~

SL TanSL Tan
Hi SFDCFox

Thanks a lot for your kind guidance and also the coding structure. As I am not familiar with coding I am most appreciative of this.

I have tested the link but every time there is a pop up message of "Undefined". What do you think could be my problem? I place the Custom Link button on the detail page of the SubContract (I am renaming it as Production Order)

I append below my changes to your coding. Basically I have made the following substitutions to your coding:
1. Contract = Customer Order
2. SubContract = Production Order
3. Total Amount = Total (in Customer Order object)
4. Remaining Amount = Balance Order Qty ( in Production Order object)
5. Amount = Prod Order Qty ( in Production Order Object)

I hope you can help point out where I went wrong this time. Please also advise me, if I put this Custom Link as a Detail Button, does this mean that the new Production Order has to be saved first, and then the User click on this Custom Link button in order to populate the answer for the Balance Order Qty? If so, is there some way that the answer to the Balance Order Qty is worked out by the system once the user save a new Production Order and the page will show the Balance Order Qty immediately?

Hope you can assist me here and sorry if my questions seem primitive
Thanks in advance
SL

{!RequireScript("/soap/ajax/9.0/connection.js")}
try
{ Customer_Order__c = sforce.connection.retrieve('Total__c','Customer_Order__c',['{!Production_Order__c.Customer_Order__c}'])[0]
Production_Order__c = sforce.connection.query('select Id,Prod_Order_Qty__c from Production_Order__c where Customer_Order__c = \'{!Production_Order__c.Customer_Order__c}\'')
Customer_Order__c['Balance_Order_Qty__c'] = Customer_Order__c.getFloat('Total__c')
QueryResults = new sforce.QueryResultsIterator(Production_Order__c)
while(QueryResults.hasNext())
{ Production_Order__c = QueryResults.next()
Customer_Order__c['Balance_Order_Qty__c'] -= Production_Order__c.getFloat('Prod_Order_Qty__c')
}
sforce.connection.update([Customer_Order__c])
} catch(e)
{ alert('Error: '+e.message?e.message:e.faultCode?e.faultCode:e)
}
sfdcfoxsfdcfox
I had some syntax errors in the code, which I found and corrected. Here's the new, corrected version using your own terminology:

Code:
{!RequireScript("/soap/ajax/9.0/connection.js")}
Customer_Order__c = sforce.connection.retrieve('Total__c','Customer_Order__c',['{!Production_Order__c.Customer_OrderId__c}'])[0]
Production_Order__c = sforce.connection.query('select Id,Prod_Order_Qty__c from Production_Order__c where Customer_Order__c = \'{!Production_Order__c.Customer_OrderId__c}\'')
Customer_Order__c['Balance_Order_Qty__c'] = Customer_Order__c.getFloat('Total__c')
QueryResults = new sforce.QueryResultIterator(Production_Order__c)
while(QueryResults.hasNext())
{ Production_Order__c = QueryResults.next()
Customer_Order__c['Balance_Order_Qty__c'] -= Production_Order__c.getFloat('Prod_Order_Qty__c')
}
sforce.connection.update([Customer_Order__c])

QueryResultsIterator should have been QueryResultIterator (I've been using this for ages, I'm unsure as to how I missed it). Also, I found out that relationship fields use "ID" as part of the field name for the ID value. For example, "Production_Order__c.Customer_Order__c" references the name of the object, while "Production_Order__c.Customer_OrderId__c" references the 15/18 character ID. I tested the code this time before I posted it, so I know it works now!

~ sfdcfox ~
SL TanSL Tan
Hi Sfdcfox
Thanks for your patience and kind advise
The revised code works perfectly this time, I am so pleased indeed!
It certainly help me tremendously to solve one of the issues of passing and updating data.
Thanks a million for your kind help - it's really wonderful
Best Regards
SL