1

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) in another entity named 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.

enter image description here

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, but I have NO idea what is wrong. So I am a bit lost here.

xChaax
  • 193
  • 5
  • 27

1 Answers1

0

Lookup is a relationship column (Foreign key) of related entity. You should check if there is a master record available to map the key or create the record of that type to map.

ColumnSet is not needed in first method while creating entity record. But you should map the columns from source file/table Rows. ColumnSet is useful like in second method while retrieving dataset.

For loop is unnecessary in 2nd method.

Just put the missing pieces in your snippet for you to understand, this can be improvised.

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;
}
Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
  • Yeah, Just noticed I,m not including `Service.Create or Service.Update` in my code. I just changed the `new_name` to exact name for ticket field in Ticket Entity. Thanks Mr Arun for the answer. – xChaax Oct 01 '17 at 03:56
  • Hi @Arun Vinoth. Above code still creating a new record even tho the ticket number already exist in CRM. – xChaax Oct 02 '17 at 06:58
  • new_ticketproduct will be always created. new_pricingapprovalticket will be referenced (either Create or fetching existing guid). Which one you are referring ? – Arun Vinoth-Precog Tech - MVP Oct 02 '17 at 10:40
  • Actually, what I want, if new_ticketproduct already exists in CRM. It will update instead of creating new. Currently when I checked, ny code skip if ‘TicketNumberId’ != Guid.Empty’ and straight away go to create a new record. – xChaax Oct 02 '17 at 10:47
  • 1
    then do similar exercise like what we are doing for getTicketNumber. implement one more method for getTicketProduct & verify if it exists, then create or update. btw, this is going broad for SO. pls limit it. – Arun Vinoth-Precog Tech - MVP Oct 02 '17 at 14:37
  • Hi, I m using queryexpression then service.retrieve to get existing record from crm. Then I m doing if else to check wether record exist or not. But insted Its still creating a new record. I m aware this is getting broad. So it’s possible for me to contact u personally ? Thanks – xChaax Oct 03 '17 at 10:10