Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Please: SQL to FetchXML question

  • 1.  Please: SQL to FetchXML question

    Posted Feb 12, 2020 11:02 AM
      |   view attached
    Good day!

    I have incidents converting the following query to fetchxml:



    SELECTSELECT g.eve_name 'Grupo de Campana', e.eve_codigo 'Codigo Solicitud', e.eve_fechapropuesta 'Fecha Inicio', a.eve_codigocrm 'Codigo Institucion', i.eve_institucionname 'Nombre Institucion', a.eve_distritoname 'Distrito', a.eve_delegacionname 'Delegacion', e.eve_tipoaccionname 'Tipo Accion', e.statuscodename 'Estado', e.eve_eventoprogramadoname 'Descripcion Evento', e.eve_expositorprogramadoname 'Nombre Expositor', p.eve_codigopromotor 'Codigo Promotor', e.owneridname 'Nombre Solicitante (Promotor)', e.eve_supervisorname 'Supervisor', e.eve_equipomultimedianame 'Equipo', h.eve_numerolinea 'Nro.', h.eve_fechahorainicio 'Hora Inicio', h.eve_fechahorafin 'Hora Final', h.eve_asistentesesperados 'Asistencia Esperados', h.eve_asistentes 'Asistencia Real', h.eve_especialidades 'Especialidades', h.eve_grados 'Grados', CONVERT(NVARCHAR,((h.eve_fechahorafin)-(h.eve_fechahorainicio)), 108) 'Horas Cronologicas', e.eve_contactoresponsablename 'Contacto', e.eve_lineanegocioname 'Linea de negocio'FROM eve_grupocampana g INNER JOIN eve_solicitudevento e ON e.eve_grupocampana=g.eve_grupocampanaid LEFT JOIN eve_programacionsolicitudevento h ON h.eve_solicitudevento=e.eve_solicitudeventoid INNER JOIN eve_promotor p ON p.eve_promotorid=e.eve_promotor INNER JOIN eve_institucionsolicitudevento i ON i.eve_solicitudevento=e.eve_solicitudeventoid INNER JOIN account a ON a.accountid=i.eve_institucionWHERE e.eve_codigo='STYE-008599'



    Please your support to achieve it.

    I enclose the query with the best format.

    ------------------------------
    Carlos Romero
    MyGeoBusiness
    ------------------------------

    Attachment(s)

    txt
    query.txt   3K 1 version
    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 2.  RE: Please: SQL to FetchXML question

    MICROSOFT MVP
    Posted Feb 13, 2020 12:58 AM
    I suggest you try the SQL 4 CDS tool in XrmToolBox.
    https://markcarrington.dev/sql-4-cds/
    https://www.xrmtoolbox.com/

    ------------------------------
    Jonas Rapp
    MVP
    Sweden
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 3.  RE: Please: SQL to FetchXML question

    TOP CONTRIBUTOR
    Posted Feb 13, 2020 04:26 AM
    Thanks @Jonas Rapp!

    SQL 4 CDS will handle all this except the calculation:

    CONVERT(NVARCHAR,((h.eve_fechahorafin)-(h.eve_fechahorainicio)), 108)

    There isn't an equivalent for this in FetchXML​​. To get this you'll either need to add a calculated field to your eve_programacionsolicitudevento entity and select that field, or select the two individual fields and do the calculation yourself afterwards.

    ------------------------------
    Mark Carrington
    Chief Technologist
    Data8
    Chester
    ------------------------------

    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


  • 4.  RE: Please: SQL to FetchXML question

    GOLD CONTRIBUTOR
    Posted Feb 13, 2020 02:22 AM
    Look at the fantastic SQL4CDS in XrmToolBox! Will do exactly what you want. 




    The first step toward cloud success. - Migrate from CRM to D365 with expert guidance from Microsoft. I'm Ready


If you've found this thread useful, dive deeper into User Group community content by role