Apex Code Development You need to sign in to do that
Don't have an account?
Mariam Ibrahim 10 Apex code to auto sum up total assets under each product
Hi,
I am new to apex code I would appreciate any assistance on the following question:
I am trying to write apex code that auto sums up the total assets per product . I have a custom field in product object called "Initial stock", and in the asset object, I have a custom picklist field called "Status" with values like "check-in" and "Checkout", and also a product field which is a lookup field to the standard product object.
My business idea is thus: Every new or existing asset should have "Check-in" as the asset status and a trigger should auto sum up all the checked-in assets under each product and put the number in the "Initial stock" field in the product table.
And when the asset status changes to "Checkout", a custom field called "Consumed stock" should be auto updated with the number of assets checked out per product.
Any help will be highly appreciated!
I am new to apex code I would appreciate any assistance on the following question:
I am trying to write apex code that auto sums up the total assets per product . I have a custom field in product object called "Initial stock", and in the asset object, I have a custom picklist field called "Status" with values like "check-in" and "Checkout", and also a product field which is a lookup field to the standard product object.
My business idea is thus: Every new or existing asset should have "Check-in" as the asset status and a trigger should auto sum up all the checked-in assets under each product and put the number in the "Initial stock" field in the product table.
And when the asset status changes to "Checkout", a custom field called "Consumed stock" should be auto updated with the number of assets checked out per product.
Any help will be highly appreciated!
<pre>
public trigger UpdateProductStockCounts on Asset ( after update )
{
// map which stock counts go into which product fields
Map<String,String> stockFields = new Map<String,String>
{
'Available' => 'Available_Stock__c',
'Not Available' => 'Consumed_Stock__c'
};
// figure out which products need to be updated
Set<Id> productIds = new Set<Id>();
for ( Asset asset : Trigger.new )
{
Asset oldAsset = Trigger.oldMap.get( asset.Id );
if ( asset.Status__c != oldAsset.Status__c )
{
productIds.add( asset.Product2Id );
}
}
if ( !productIds.isEmpty() )
{
// use aggregate query to subtotal asset counts by product and status
Map<Id,Product2> productsToUpdate = new Map<Id,Product2>();
for ( AggregateResult result :
[ SELECT COUNT(Status__c) stock, Status__c, Product2Id
FROM Asset
WHERE Product2Id IN :productIds
GROUP BY ROLLUP(Product2Id, Status__c)
]
)
{
Id productId = (Id) result.get( "Product2Id" );
if ( !productsToUpdate.containsKey( productId ) )
{
productsToUpdate.put( productId, new Product2( Id = productId ) );
}
Product2 product = productsToUpdate.get( productId );
String status = (String) result.get( "Status__c" );
Decimal stock = (Decimal) result.get( "stock" );
String field = stockFields.get( status );
if ( field != null )
{
product.put( field, stock );
}
}
update productsToUpdate;
}
}
</pre>
All Answers
You should be able to use an aggregate query in a trigger to do this. (Disclaimer: This code is untested and may contain typos.)
Note that to add new totals (e.g. for a new status "Missing") just add another line to the 'stockFields' map. The rest of the code will remain unchanged.
<pre>
public trigger UpdateProductStockCounts on Asset ( after update )
{
// map which stock counts go into which product fields
Map<String,String> stockFields = new Map<String,String>
{ 'Check In' => 'Initial_Stock__c'
, 'Check Out' => 'Consumed_Stock__c'
};
// figure out which products need to be updated
Set<Id> productIds = new Set<Id>();
for ( Asset asset : Trigger.new )
{
Asset oldAsset = Trigger.oldMap.get( asset.Id );
if ( asset.Status__c != oldAsset.Status__c )
{
productIds.add( asset.Product__c );
}
}
// use aggregate query to subtotal asset counts by product and status
Map<Id,Product2> productsToUpdate = new Map<Id,Product2>();
for ( AggregateResult result :
[ SELECT COUNT(Status__c) stock, Status__c, Product__c
FROM Asset
WHERE Product__c IN :productIds
GROUP BY ROLLUP(Product__c, Status__c)
]
)
{
Id productId = (Id) result.get( "Product__c" );
if ( !productsToUpdate.containsKey( productId ) )
{
productsToUpdate.put( productId, new Product2( Id = productId ) );
}
Product2 product = productsToUpdate.get( productId );
String status = (String) result.get( "Status__c" );
Decimal stock = (Decimal) result.get( "stock" );
String field = stockFields.get( status );
if ( field != null )
{
product.put( field, stock );
}
}
update productsToUpdate;
}
</pre>
Thanks for helping out!
I am having an error message on the product field in the asset object. Apparently, the product field is a standard object in the asset object, so I took off the __c on the code you wrote, and I keep getting error message that product2 field is an invalid field. Please can you help me figure out what I am missing out?
* Also, the values in status picklist field of the Asset object have been changed to 'Available' and 'Not Available' and its reflected in the code.
* I will be very grateful if you can explain to me what the code from line 34 to 46 is doing.
Thank you for your help!
trigger UpdateProductStockCounts on Asset (after update) {
// map which stock counts go into which product fields
Map<string, string> stockfields= new Map< string, string>
{
'Available'=> 'Available_Stock__c',
'Not Available' => 'Consumed_Stock__c'
};
// figure out which products need to be updated
Set<Id>productIds = new Set<Id>();
for ( Asset asset : Trigger.new )
{
Asset oldAsset = Trigger.oldMap.get(asset.id);
if ( asset.Status != oldAsset.Status)
{
productIds.add(asset.Product);
}
}
//use aggregate query to subtotal asset counts by product and status
Map<Id,Product2> productsToUpdate =new Map<Id,Product2>();
for ( AggregateResult result:
[ SELECT Count(Status) stock, status,product2
from Asset
where Product IN : productIds
Group BY ROLLUP(Product2,status)]
)
if(!productIds.isEmpty())
{
Id ProductId = (Id) result.get("Product");
if(!productsToUpdate.containsKey(ProductId))
{
productsToUpdate.put(productId, new Product2(Id= productid));
}
Product2 product = productsToUpdate.get(productId);
String status =(String) result.get (Status);
Decimal stock = (Decimal) result.get (Stock);
String field = stockFields.get(status);
if(field ! = null)
{
product.put(field,stock);
}
}
update productsToUpdate;
}
<pre>
public trigger UpdateProductStockCounts on Asset ( after update )
{
// map which stock counts go into which product fields
Map<String,String> stockFields = new Map<String,String>
{
'Available' => 'Available_Stock__c',
'Not Available' => 'Consumed_Stock__c'
};
// figure out which products need to be updated
Set<Id> productIds = new Set<Id>();
for ( Asset asset : Trigger.new )
{
Asset oldAsset = Trigger.oldMap.get( asset.Id );
if ( asset.Status__c != oldAsset.Status__c )
{
productIds.add( asset.Product2Id );
}
}
if ( !productIds.isEmpty() )
{
// use aggregate query to subtotal asset counts by product and status
Map<Id,Product2> productsToUpdate = new Map<Id,Product2>();
for ( AggregateResult result :
[ SELECT COUNT(Status__c) stock, Status__c, Product2Id
FROM Asset
WHERE Product2Id IN :productIds
GROUP BY ROLLUP(Product2Id, Status__c)
]
)
{
Id productId = (Id) result.get( "Product2Id" );
if ( !productsToUpdate.containsKey( productId ) )
{
productsToUpdate.put( productId, new Product2( Id = productId ) );
}
Product2 product = productsToUpdate.get( productId );
String status = (String) result.get( "Status__c" );
Decimal stock = (Decimal) result.get( "stock" );
String field = stockFields.get( status );
if ( field != null )
{
product.put( field, stock );
}
}
update productsToUpdate;
}
}
</pre>