CRM Plugin [VB]: Use variable in FetchXML

Tuesday, November 03, 2009 12:21 PM

Joey VDB

Hi all,

I want to retrieve data from an N:N table. Therefor I use the FetchXML method. Works fine and all but there is only one thing I need and can't get done.
In the FetchXML I have a filter with a condition. The condition is that opportunityid has to match an guid. No problem if I wanted a known GUID. But this is not what I want. In my plugin I retrieve an opportunityid. This particulair opportunityid is what I want as value for the condition.

I would like to know if it is possible to "escape" the FetchXML string and paste the GUID variable in the condition value. Below you find my code. On the fifth line is the value property that I want to replace for a GUID (Lookup value).

            Dim fetchXml As String = "" & _



            "<fetch mapping=""logical"">" & _



            "	<entity name=""new_opportunity_new_klantabonnement"">" & _



            "		<filter type=""and"">" & _



            "			<condition attribute=""opportunityid"" operator=""eq"" value=""HERE I WANT THE GUID""/>" & _



            "		</filter>" & _



            "		<link-entity name=""new_klantabonnement"" from=""new_klantabonnementid"" to=""new_klantabonnementid"">" & _



            "			<attribute name=""new_klantabonnementid""/>" & _



            "		</link-entity>" & _



            "		<link-entity name=""opportunity"" from=""opportunityid"" to=""opportunityid"">" & _



            "			<attribute name=""opportunityid""/>" & _



            "		</link-entity>" & _



            "	</entity>" & _



            "</fetch>" & _



            ""







            Dim retrieveVerkoopkansAbo As New FetchXmlToQueryExpressionRequest()



            retrieveVerkoopkansAbo.FetchXml = fetchXml







            Dim responseVerkoopkansAbo As New FetchXmlToQueryExpressionResponse()







            Try



                responseVerkoopkansAbo = CType(service.Execute(retrieveVerkoopkansAbo), FetchXmlToQueryExpressionResponse)



            Catch ex As Web.Services.Protocols.SoapException



                Throw New InvalidPluginExecutionException("Fout bij het lezen van de gekoppelde verkoopkansen (" & ex.Detail.InnerText & ")")



            End Try

I hope somebody can help me! Because it is starting to be innoying haha.
By the way my code is in VB.net. But if you have the answer for C#, you're welcome! I will translate it then.

I also tried to make an queryexpression with fetchxml. But when I use a retrievemulti on it it gives me the error: The 'RetrieveMultiple' method does not support entities of type 'new_opportunity_new_klantabonnement'. So that is the reason why I try it this way

Thanks in advance!
Joey

Answers

Wednesday, November 04, 2009 12:34 PM

Joey VDB

I figured the whole thing out. Im happy to share with you:

            Dim fetchXml As String = String.Format("" & _
            "<fetch mapping=""logical"">" & _
            "	<entity name=""new_opportunity_new_klantabonnement"">" & _
            "		<filter type=""and"">" & _
            "			<condition attribute=""opportunityid"" operator=""eq"" value=""{0}""/>" & _
            "		</filter>" & _
            "		<link-entity name=""new_klantabonnement"" from=""new_klantabonnementid"" to=""new_klantabonnementid"">" & _
            "			<attribute name=""new_klantabonnementid""/>" & _
            "		</link-entity>" & _
            "		<link-entity name=""opportunity"" from=""opportunityid"" to=""opportunityid"">" & _
            "			<attribute name=""opportunityid""/>" & _
            "		</link-entity>" & _
            "	</entity>" & _
            "</fetch>" & _
            "", vOpp.Value)


            Dim FetchResult As String = service.Fetch(fetchXml)

            Dim m_xmld As XmlDocument
            Dim m_nodelist As XmlNodeList
            Dim m_node As XmlNode

            'Maak XML document aan
            m_xmld = New XmlDocument()

            'Lees de XML uit
            m_xmld.LoadXml(FetchResult)

            'Haal de lijst met node namen op
            m_nodelist = m_xmld.SelectNodes("/resultset/result")

            Dim aboNodeCount As Integer = 0

            Try
                For Each m_node In m_nodelist
                    aboNode = m_node.ChildNodes.Item(1).InnerText
                    OfferteregelAboGUIDOphalen()
                Next

            Catch ex As Web.Services.Protocols.SoapException
                Throw New InvalidPluginExecutionException("Fout bij het lezen van de FetchXML (" & ex.Detail.InnerText & ")")
            End Try

Marked As Answer byJoey VDB Wednesday, November 04, 2009 12:34 PM

All Replies

Tuesday, November 03, 2009 1:04 PM

Andriy a33ik Butenko

Hi, Joey.

Try this:

        Dim someGuid As Guid = Guid.NewGuid()
	Dim fetchXml As String = "" & _
            "<fetch mapping=""logical"">" & _
            "	<entity name=""new_opportunity_new_klantabonnement"">" & _
            "		<filter type=""and"">" & _
            "			<condition attribute=""opportunityid"" operator=""eq"" value=""" & someGuid.ToString() & """/>" & _
            "		</filter>" & _
            "		<link-entity name=""new_klantabonnement"" from=""new_klantabonnementid"" to=""new_klantabonnementid"">" & _
            "			<attribute name=""new_klantabonnementid""/>" & _
            "		</link-entity>" & _
            "		<link-entity name=""opportunity"" from=""opportunityid"" to=""opportunityid"">" & _
            "			<attribute name=""opportunityid""/>" & _
            "		</link-entity>" & _
            "	</entity>" & _
            "</fetch>" & _
            ""

            Dim retrieveVerkoopkansAbo As New FetchXmlToQueryExpressionRequest()
            retrieveVerkoopkansAbo.FetchXml = fetchXml
            Dim responseVerkoopkansAbo As New FetchXmlToQueryExpressionResponse()

            Try
                responseVerkoopkansAbo = CType(service.Execute(retrieveVerkoopkansAbo), FetchXmlToQueryExpressionResponse)
            Catch ex As Web.Services.Protocols.SoapException
                Throw New InvalidPluginExecutionException("Fout bij het lezen van de gekoppelde verkoopkansen (" & ex.Detail.InnerText & ")")
            End Try

Just put to someguid variable your identifier.

Truth is opened the prepared mind My blog - http://a33ik.blogspot.com

Tuesday, November 03, 2009 1:10 PM

Joey VDB

No already tried that one.
Gives me an error that the '&' is not defined for types String and System.GUID

Im trying the following right know:

           Dim fetchXml As String = String.Format("" & _
            "<fetch mapping=""logical"">" & _
            "	<entity name=""new_opportunity_new_klantabonnement"">" & _
            "		<filter type=""and"">" & _
            "			<condition attribute=""opportunityid"" operator=""eq"" value=""{0}""/>" & _
            "		</filter>" & _
            "		<link-entity name=""new_klantabonnement"" from=""new_klantabonnementid"" to=""new_klantabonnementid"">" & _
            "			<attribute name=""new_klantabonnementid""/>" & _
            "		</link-entity>" & _
            "		<link-entity name=""opportunity"" from=""opportunityid"" to=""opportunityid"">" & _
            "			<attribute name=""opportunityid""/>" & _
            "		</link-entity>" & _
            "	</entity>" & _
            "</fetch>" & _
            "", vOpp.Value)

            Try
                Dim FetchResult As String = service.Fetch(fetchXml)
            Catch ex As Web.Services.Protocols.SoapException
                Throw New InvalidPluginExecutionException("Fout bij het lezen van de FetchXML (" & ex.Detail.InnerText & ")")
            End Try

But it breaks ad Fetchresult. Not discovered why though.

Wednesday, November 04, 2009 12:34 PM

Joey VDB

I figured the whole thing out. Im happy to share with you:

            Dim fetchXml As String = String.Format("" & _
            "<fetch mapping=""logical"">" & _
            "	<entity name=""new_opportunity_new_klantabonnement"">" & _
            "		<filter type=""and"">" & _
            "			<condition attribute=""opportunityid"" operator=""eq"" value=""{0}""/>" & _
            "		</filter>" & _
            "		<link-entity name=""new_klantabonnement"" from=""new_klantabonnementid"" to=""new_klantabonnementid"">" & _
            "			<attribute name=""new_klantabonnementid""/>" & _
            "		</link-entity>" & _
            "		<link-entity name=""opportunity"" from=""opportunityid"" to=""opportunityid"">" & _
            "			<attribute name=""opportunityid""/>" & _
            "		</link-entity>" & _
            "	</entity>" & _
            "</fetch>" & _
            "", vOpp.Value)


            Dim FetchResult As String = service.Fetch(fetchXml)

            Dim m_xmld As XmlDocument
            Dim m_nodelist As XmlNodeList
            Dim m_node As XmlNode

            'Maak XML document aan
            m_xmld = New XmlDocument()

            'Lees de XML uit
            m_xmld.LoadXml(FetchResult)

            'Haal de lijst met node namen op
            m_nodelist = m_xmld.SelectNodes("/resultset/result")

            Dim aboNodeCount As Integer = 0

            Try
                For Each m_node In m_nodelist
                    aboNode = m_node.ChildNodes.Item(1).InnerText
                    OfferteregelAboGUIDOphalen()
                Next

            Catch ex As Web.Services.Protocols.SoapException
                Throw New InvalidPluginExecutionException("Fout bij het lezen van de FetchXML (" & ex.Detail.InnerText & ")")
            End Try

Marked As Answer byJoey VDB Wednesday, November 04, 2009 12:34 PM