Dynamics Community Forum Thread Details

How to insert Ticket Number(lookup field) to MS Dynamics CRM Pricing Approval Products(Custom entity) using ssis's script component.

Hi,

I need to create or update Ticket Number lookup field in Pricing Approval Products. This ticket number(lookup field) is linked to Ticket(text field) another entity called as Pricing Approval Tickets.

This is my code in script component.

 public override void Input0_ProcessInputRow(Input0Buffer Row)
    {

        Entity ApprovalProductEnt = new Entity("new_ticketproduct");
        ColumnSet columns = new ColumnSet(true);
        columns = new ColumnSet(new String[] { "new_ticket",
            "new_productgroup",
            "new_producttype",
            "new_productitem" });

        Guid TicketNumberId = new Guid();
        TicketNumberId = getTicketNumber(Row.OppportunityID, ref organizationservice);

        //update
        if (TicketNumberId != Guid.Empty)
        {
            ApprovalProductEnt["new_ticket"] = new EntityReference("new_pricingapprovalticket", TicketNumberId);
        }
        else
        //create
        if (TicketNumberId != Guid.Empty)
        {
            ApprovalProductEnt["new_ticket"] = new EntityReference("new_pricingapprovalticket", TicketNumberId);
        }
    }

    public Guid getTicketNumber(string ticketnumber, ref IOrganizationService service)
    {
        Guid TicketNumberGuid = Guid.Empty;
        QueryExpression TicketNumberQuery = new QueryExpression { EntityName = "new_pricingapprovalticket", ColumnSet = new ColumnSet(true) };
        TicketNumberQuery.Criteria.AddCondition("new_ticketnumber", ConditionOperator.Equal, ticketnumber);
        EntityCollection TicketNumberQueryRetrieve = service.RetrieveMultiple(TicketNumberQuery);

        for (var i = 0; i <TicketNumberQueryRetrieve.Entities.Count; i++)
        {
            TicketNumberGuid = TicketNumberQueryRetrieve.Entities[0].GetAttributeValue<Guid>("new_ticket");
        }

        return TicketNumberGuid;
    }

When I start the SSIS package, Its run without error. But somehow there is no data inserted.

05020.Untitled.png
I have no problem to create a normal text field. But when it come to lookup field, internet solutions suggest to use EntityReference as per my code, butI have NO idea what is wrong. So I am a bit lost here.

public override void Input0_ProcessInputRow(Input0Buffer Row) { Entity ApprovalProductEnt = new Entity("new_ticketproduct"); ApprovalProductEnt["new_productgroup"] = Row.ProductGroup; ApprovalProductEnt["new_producttype"] = Row.ProductType; ApprovalProductEnt["new_productitem"] = Row.ProductItem; Guid TicketNumberId = new Guid(); TicketNumberId = getTicketNumber(Row.OppportunityID, ref organizationservice); //update if (TicketNumberId != Guid.Empty) { ApprovalProductEnt["new_ticket"] = new EntityReference("new_pricingapprovalticket", TicketNumberId); } else //create { Entity TicketEnt = new Entity("new_pricingapprovalticket"); TicketEnt["new_name"] = Row.OppportunityID; TicketNumberId = organizationservice.Create(TicketEnt); ApprovalProductEnt["new_ticket"] = new EntityReference("new_pricingapprovalticket", TicketNumberId); } organizationservice.Create(ApprovalProductEnt); } public Guid getTicketNumber(string ticketnumber, ref IOrganizationService service) { Guid TicketNumberGuid = Guid.Empty; QueryExpression TicketNumberQuery = new QueryExpression { EntityName = "new_pricingapprovalticket", ColumnSet = new ColumnSet(true) }; TicketNumberQuery.Criteria.AddCondition("new_ticketnumber", ConditionOperator.Equal, ticketnumber); EntityCollection TicketNumberQueryRetrieve = service.RetrieveMultiple(TicketNumberQuery); if (TicketNumberQueryRetrieve.Entities.Count > 0) TicketNumberGuid = TicketNumberQueryRetrieve.Entities[0].GetAttributeValue<Guid>("new_ticket"); return TicketNumberGuid; }

Quick Links

Leaderboard