+ Start a Discussion
Sammy7Sammy7 

insert invoice on opportunity from quote line item

Hi, 
 So I created a custom object Invoice__c and Invoicelineitem__c. Basically, I want to copy all the quote line items to invoice line items. 
Below is my code: 

trigger Insertnewinvoice on Invoice__c (after insert) {
Invoice__c to=trigger.new[0];
Quote q=[Select id from Quote where id= :to.Quote__c];
list<QuoteLineItem> ql=[select id,ListPrice,PriceBookEntry.Product2Id ,Subtotal,TotalPrice from QuoteLineItem where QuoteId=:q.id];

   for(QuoteLineItem qli:ql){
       Invoicelineitem__c lm=new Invoicelineitem__c();
        lm.List_Price__c=qli.ListPrice;
         lm.Product__C=qli.PriceBookEntry.Product2Id;
         lm.Order__c=to.id;

insert lm;}}

Why am I getting error: "unexpected token 'Select id from Quote where id' ??
Best Answer chosen by Sammy7
UC InnovationUC Innovation
Please mark the best answer if this solution works for you! I actually had to work hard on this one :)

All Answers

UC InnovationUC Innovation

Hi Sammy,

I made some adjustments to your trigger. Some tips for you:

Avoid putting DML Statements in your for loop as you can hit governor limits if your trigger list is large enough. 
Try to bulkify your code by acting on the entire trigger list and not just on the first element of the list.
Also the query for quote is unnecessary since you already have the Id in the lookup.

Try this code out.

trigger Insertnewinvoice on Invoice__c (after insert) {
	// minimize DML statments
	List<Invoicelineitem__c> InvoicelineitemList = new List<Invoicelineitem__c>();
	Set<Id> quoteIdSet = new Set<Id>();
	
	for (Invoice__c to : trigger.new) {
		quoteIdSet.add(to.Quote__c);
	}

	list<QuoteLineItem> ql = [SELECT id,
									 ListPrice,
									 PriceBookEntry.Product2Id,
									 Subtotal,
									 TotalPrice 
							  FROM QuoteLineItem 
							  WHERE QuoteId IN :quoteIdSet];

    for(QuoteLineItem qli : ql){
		Invoicelineitem__c lm = new Invoicelineitem__c();
        lm.List_Price__c = qli.ListPrice;
        lm.Product__c = qli.PriceBookEntry.Product2Id;
        lm.Order__c = to.id;
		InvoicelineitemList.add(lm);
	}
	
	insert InvoicelineitemList;
}

Hope this helps!

AM
Sammy7Sammy7
I should explain that I have Invoice__C as master-detail to Quote and Invoicelineitem__C master-detail to Invoice_c.
Could the unexpected token be that there is no master-detail to Quotelineitem ??
UC InnovationUC Innovation
Well that 'unexpected token' is typically a syntax error and would have nothing to do with not having a master-datail.
UC InnovationUC Innovation
do you get any errors after using the code i sent?
 
Sammy7Sammy7
Thanks UC. Im a bit new to Apex. 
Can you please see my comments below. I really apperciate the help here:
trigger Insertnewinvoice on Invoice__c (after insert) {
	// minimize DML statments
	List<Invoicelineitem__c> InvoicelineitemList = new List<Invoicelineitem__c>(); // This is to hold //invoiceline items into
	Set<Id> quoteIdSet = new Set<Id>();  //this is to hold ids
	
	for (Invoice__c to : trigger.new) {
		quoteIdSet.add(to.id);   // This for loops adds ids for all items in trigger.new?
	}
//code below is fetching data from quotelineitems
	list<QuoteLineItem> ql = [SELECT id,
									 ListPrice,
									 PriceBookEntry.Product2Id,
									 Subtotal,
									 TotalPrice 
							  FROM QuoteLineItem 
							  WHERE QuoteId IN :quoteIdSet]; // Im confused about this line,what is this filter looking for?
// this clones from quotelineitems to invoicelineitems
    for(QuoteLineItem qli : ql){
		Invoicelineitem__c lm = new Invoicelineitem__c();
        lm.List_Price__c = qli.ListPrice;
        lm.Product__c = qli.PriceBookEntry.Product2Id;
      	InvoicelineitemList.add(lm);
	}
	//adds the record
	insert InvoicelineitemList;
}

 
UC InnovationUC Innovation
Hi Sammy,

So this trigger is actually a bit more complicated than I thought. If I'm understanding this correctly your data model is like this:

QuoteLineItem belongs to Quote.
Invoice belongs to Quote.
InvoiceLineItem belongs to Invoice.

For each QuoteLineItem that belongs to a quote, you want to create an InvoiceLineItem for ALL invoices that belongs to the quote. This means that if a quote has 5 QuoteLineItems, and you insert 5 Invoices at the same time to one quote, you will end up with 25 InvoiceLineItems.

I've modified the trigger to facilitate this logic:
 
trigger Insertnewinvoice on Invoice__c (after insert) {
	Set<Id> quoteIdSet = new Set<Id>();
	List<Invoicelineitem__c> InvoicelineitemList = new List<Invoicelineitem__c>();
	
	// Construct the set of quote IDs that the triggering invoices belong to
	for (Invoice__c i: trigger.new)
	{
		quoteIdSet.add(i.Quote__c);
	}
	
	// Getting all the quote line items that belong to all the quotes of all triggering invoices
	list<QuoteLineItem> quoteLineItemList =[select id,ListPrice,PriceBookEntry.Product2Id ,Subtotal,TotalPrice, QuoteId from QuoteLineItem where QuoteId IN: quoteIdSet];
	
	for (Invoice__c i: trigger.new)
	{
		List<QuoteLineItem> quoteLineItemsForThisInvoice = new List<QuoteLineItem>();
		for (QuoteLineItem qli: quoteLineItemList)
		{
			// If this QuoteLineItem belongs to this Invoice's quote, then add to the list
			if (qli.QuoteId == i.Quote__c)
			{
				quoteLineItemsForThisInvoice.add(qli);
			}
		}
		
		// Loop through the QuoteLineItems belonging to the Quote that holds this Invoice
		// Copy the QuoteLineItem and add the InvoiceLineItems to this Invoice
		for (QuoteLineItem qli : quoteLineItemsForThisInvoice)
		{
			Invoicelineitem__c lm = new Invoicelineitem__c();
			lm.List_Price__c = qli.ListPrice;
			lm.Product__C = qli.PriceBookEntry.Product2Id;
			lm.Order__c == i.id;

			InvoicelineitemList.add(lm);
		}
	}
	
	// Bulkify the insert
	insert InvoicelineitemList;
}
Let me know if this works!
Sammy7Sammy7
Hmm, after looking at your analysis, Ive definitely made a mistake. I thought about this a bit more; I apologize as Im learning this as I go.
Basically, I want the quote along with its quotelineitems to pass to invoice__c and its invoicelineitems__c.
For instance, if quote A has 2 lineitems, then I want the same Invoice A with 2 line items

So lets say when opportunity == closed, then create the invoice along with the invoicelineitems from the quote and corresponding quotelineitems.
I guess this is my pseudo code for the trigger:

//Then I guess the trigger should be on opportunity instead?
trigger Insertnewinvoice on opportunity (after insert) {
Set <id> oppid= new set<id> ();
List<Invoice__c> Invoicelist = new List<Invoice__c>();
List<Invoicelineitem__c> InvoicelineitemList = new List<Invoicelineitem__c>();

//get all the new closed opps and add to set oppid
for (opportunity o: Trigger.new) {
if(o.isclosed==true) { oppid.add (o.id) }

for (Opportunity opp:oppid) {
//getquoteids
list<Quote> qid =[select id From quote where .....]  // not sure of syntax but need the quoteids belonging to that closed opportunity
//get the quotelineitemlist
list<QuoteLineItem> quoteLineItemList =[select id,ListPrice,PriceBookEntry.Product2Id ,Subtotal,TotalPrice, QuoteId from QuoteLineItem where ...]
//this is tricky, I dont want to mix the line items from two separate quotes....how to accomplish this?


//create the invoice for each quote
      for(quote q: qid) {
Invoice__c inv= new invoice__c ();
inv.invoicenumber= q.number ; //copy over the quote number
invoicelist.add (inv);

//finally create those invoicelineitems
           for(quotelineitem qli: quotelineitemlist) {
            Invoicelineitem__c ili= new invoicelineitem__c();
            ili.List_price__c= qli.ListPrice;
            ili.Product_Code__c= qli.Pricebookentry.Product2id;
            Invoicelineitemlist.add (ili); 
                                                                     }
                            }

    }
insert invoicelist;
insert invoicelineitemlist;
}
// Its getting late, but I just had another thought... Quote and quotelineitem are in a relationship and I have invoice and invoicelineitem in a relationship...
so I should be able to traverse, for instance invoice__c.invoicelineitem__c.List_price__c=  quote.quotelineitem.Listprice ??
This way the invoice and invoicelineitems stay grouped like quotes and quotelineitems. Any help is appreciated as Im learning this as I go and my job does depend on this :(

Thanks.





 
UC InnovationUC Innovation
Hey Sammy,

So now I understand your data model. It looks like you have Quotes and Invoices both belonging under Opportunities, and QuoteLineItems and InvoiceLineItems under those, respectively. 

So the logic is, everytime an opportunity is updated to be closed, you want to insert one invoice per quote and one invoicelineitem per quotelineitem. You also need to maintain the same relationship between quotes/quotelineitems and invoice/invoicelineitems.

This trigger is actually kind of complicated... I tried to put a lot of comments. I just tested this on my own org, and it works fine. Let me know how it works out for you.
 
trigger CreateInvoice on Opportunity (after update, after insert) {
	List<Invoice__c> invoiceList = new List<Invoice__c>();
    List<InvoiceLineItem__c> invoiceLineItemList = new List<InvoiceLineItem__c>();
	Set<Id> oppIdSet = new Set<Id>();
	
	// We only care about opportunities that are closed
	for(Opportunity o : trigger.new)
	{
        if (o.isClosed)
        {
            oppIdSet.add(o.id);
        }
	}

	// Need to grab all the quotes and their subsequent quotelineitems under all the opportunities that fired the trigger
	List<Quote> quoteList = [SELECT id, number, opportunity__c, 
				(SELECT id, ListPrice, PriceBookEntry.Product2Id, Subtotal, TotalPrice FROM quotelineitems__r) 
				FROM Quote WHERE Opportunity__c in: oppIdSet]; // Opportunity__c is the lookup field to opportunity on quote object, quotelineitems__r is the child relationship name
    
    // Create an invoice for each quote
    for (Quote q : quoteList)
    {
        Invoice__c newInvoice = new Invoice__c();
        newInvoice.invoicenumber = q.number;
        newInvoice.opportunity__c = q.opportunity__c;
        invoiceList.add(newInvoice);
    }
    
	// We need to insert the invoices first, before being able to insert the invoicelineitems under it.
    insert invoiceList;
	
	// Construct this set as a copy of the invoiceList so we can remove from it easily later
    Set<Invoice__c> invoiceSet = new Set<Invoice__c>(invoiceList);
    
    // Create the invoicelineitems
    for (Quote q : quoteList)
    {
        for (QuoteLineItem qli : q.quotelineitems__r)
        {
            InvoiceLineItem__c newInvLineItem = new InvoiceLineItem__c();
			newInvLineItem.List_price__c = qli.ListPrice;
            newInvLineItem.product_code__c = qli.Pricebookentry.Product2id;
            
			// We need to relate the invoicelineitem to the correct invoice
            for (Invoice__c i : invoiceSet)
            {
				// Using quote number as an identifier
                if (i.invoicenumber == q.number)
                {
                    newInvLineItem.Invoice__c = i.id;
                }
            }
            invoiceLineItemList.add(newInvLineItem);
        }
        
		// We need to remove the invoice we just used from the set, just in case there are quotes with duplicate quote numbers.
        for (Invoice__c i : invoiceSet)
        {
            if (i.name == q.name)
            {
                invoiceSet.remove(i);
            }
        }
    }
    
    insert invoiceLineItemList;
}




 
UC InnovationUC Innovation
Please mark the best answer if this solution works for you! I actually had to work hard on this one :)
This was selected as the best answer
Sammy7Sammy7
I changed the code a bit because I was getting some errors when I save the Opportunity as "closed":
Error:Apex trigger CreateInvoice caused an unexpected exception, contact your administrator: CreateInvoice: execution of AfterUpdate caused by: System.DmlException: Insert failed. First exception on row 0; first error: REQUIRED_FIELD_MISSING, Required fields are missing: [Invoice]: [Invoice]: Trigger.CreateInvoice: line 30, column 1
 
trigger CreateInvoice on Opportunity (after update, after insert) {
    List<Invoice__c> invoiceList = new List<Invoice__c>();
    List<InvoiceLineItem__c> invoiceLineItemList = new List<InvoiceLineItem__c>();
    Set<Id> oppIdSet = new Set<Id>();
    
    // We only care about opportunities that are closed
    for(Opportunity o : trigger.new)
    {
        if (o.isClosed)
        {
            oppIdSet.add(o.id);
        }
    }

    // Need to grab all the quotes and their subsequent quotelineitems under all the opportunities that fired the trigger
    List<Quote> quoteList = [SELECT id, quotenumber, Opportunityid,   
                (SELECT id, ListPrice, PriceBookEntry.Product2Id, Subtotal, TotalPrice FROM quotelineitems) 
                FROM Quote WHERE Opportunityid in: oppIdSet]; // Opportunity__c is the lookup field to opportunity on quote object, quotelineitems__r is the child relationship name
    
    // Create an invoice for each quote
    for (Quote q : quoteList)
    {
        Invoice__c newInvoice = new Invoice__c();
        newInvoice.invoicenumber__c = q.quotenumber;
        newInvoice.opportunityid__c = q.opportunityid;
        invoiceList.add(newInvoice);
    }
    
    // We need to insert the invoices first, before being able to insert the invoicelineitems under it.
    insert invoiceList;
    
    // Construct this set as a copy of the invoiceList so we can remove from it easily later
    Set<Invoice__c> invoiceSet = new Set<Invoice__c>(invoiceList);
    
    // Create the invoicelineitems
    for (Quote q : quoteList)
    {
        for (QuoteLineItem qli : q.quotelineitems)
        {
            InvoiceLineItem__c newInvLineItem = new InvoiceLineItem__c();
            newInvLineItem.List_price__c = qli.ListPrice;
            newInvLineItem.product_code__c = qli.Pricebookentry.Product2id;
            
            // We need to relate the invoicelineitem to the correct invoice
            for (Invoice__c i : invoiceSet)
            {
                // Using quote number as an identifier
                if (i.invoicenumber__c == q.quotenumber)
                {
                    newInvLineItem.id = i.id;   // I think you mean id here not "Invoice__c"
                }
            }
            invoiceLineItemList.add(newInvLineItem);
        }
        
        // We need to remove the invoice we just used from the set, just in case there are quotes with duplicate quote numbers.
        for (Invoice__c i : invoiceSet)
        {
            if (i.name == q.name)
            {
                invoiceSet.remove(i);
            }
        }
    }
    
    insert invoiceLineItemList;
}