locked
CRM 2011 Plugin Queston RRS feed

  • Question

  • HIGH LEVEL

    I need to achieve the following in a custom CRM 2011 App I developed: When I select a Permit Holder to go on a License Record. The ODATA query must check to see if any other license records exist in the system, that contain that permit holder. (I actually need a little more than that, but if I can get success with searching on the permit holder, I can do the rest)  And if any records exist, I will do then have the same Jscript do something with them.

    My problem is that I have not been successful at constructing the ODATA query.  

    I used Rhett Clinton’s ODATA query constructor, but when I run it, I get an HTTP 400 error. I am thinking I am not selecting the options to properly construct the query.

    If I write it manually, I can get (too many) results with this - https://bingodev.cobdev.local:444/XRMServices/2011/OrganizationData.svc/new_s_licenseinformationSet?$select=new_licensefrmlicensee,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee&$expand=new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee

    But when I try to add in the final $filter portion, I get HTTP 400

    The relationship between license and permit holder (licensee) is that a license has only one permit holder, but the same permit holder can be on many licenses.

    Now I do have a SQL query that gets me EXACTLY what I need, and am wondering if instead of using ODATA rest, it would be easier to do this with a Plugin, since I have a pure SQL query that gets me exactly what I need.

    I would need to have Jscript “launch or call” the plug in, and then the plugin would do what I need the ODATA rest query to do, and then hand the result back to the JSCRIPT. Basically, if any records exist, I would have the JSCRIPT perform task set # 1 and if no records exist, the JSCRIPT would perform task set # 2.

    Everything Jscript works except for the part that, when I select a Permit Holder to go on a License Record, the ODATA query must check to see if any other license records exist in the system, that contain that permit holder. And if any records exist, I will do then have the same Jscript do something with them.

    The preferred solution is to get a darn ODATA REST query working since everything else is in place.

    Any suggestions or insight would be appreciated.

    DETAILS

    Is it possible to use a CRM 2011 Plugin to do the following:

    Call the plugin from Jscript OnChange on a lookup field.

    Pass the called plugin a GUID for a specific record.

    The Plugin would then execute the following SQL query, using the GUID we passed it earlier in place of the SQL parameter @ LicenseGUID

    IF EXISTS(select LI.new_s_licenseinformationid [License #] from Filterednew_s_licenseinformation LI
    					inner join Filterednew_s_licenseeinfo EI on EI.new_s_licenseeinfoid = LI.new_licensefrmlicensee
    					where LI.new_s_licenseinformationid = @LicenseGUID
    					and ((LI.new_licensefrm_bingotypename = 'R') or (LI.new_licensefrm_sgctypename = 'REG'))
    					AND CONVERT(DATE,LI.new_licenseexpirationdate) > CONVERT(DATE,GETDATE())
    			)
    BEGIN(select LI.new_s_licenseinformationid from Filterednew_s_licenseinformation LI
    					inner join Filterednew_s_licenseeinfo EI on EI.new_s_licenseeinfoid = LI.new_licensefrmlicensee
    					where LI.new_s_licenseinformationid = @LicenseGUID
    					and ((LI.new_licensefrm_bingotypename = 'R') or (LI.new_licensefrm_sgctypename = 'REG'))
    					AND CONVERT(DATE,LI.new_licenseexpirationdate) > CONVERT(DATE,GETDATE())
    			)
    END
    ELSE (SELECT DISTINCT 'NO Valid Regular License On File' [License #]
    					FROM Filterednew_s_licenseinformation)

    The Plugin would then set the value of the result, which is the varchar text data inside of [License #] field, in the myfield textbox on the form, just like jscript would do if it set a value  xrm.page.getattribute(myfield).setvalue([License #])

    Is this possible?

    I originally tried to do it via ODATAREST but cannot get the query working right details shown here -- https://social.microsoft.com/Forums/en-US/d401b2d5-8b97-494f-95d3-40087ed93945/very-specific-odata-rest-query-problems-formatting?forum=crmdevelopment#d401b2d5-8b97-494f-95d3-40087ed93945




    • Edited by ACECORP Friday, October 16, 2015 2:01 PM
    Friday, October 16, 2015 3:41 AM

Answers

  • W00T I solved it!

    This is the ODATA Query with the Permit Holder who DOES NOT EXIST on any other licenses of type R or REG:

    https://internalcrm-dev.cobdev.local:444/BINGODEV/xrmservices/2011/OrganizationData.svc/new_s_licenseinformationSet?$select=new_licenseinfoid,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseeidnum,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseename&$expand=new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee&$filter=new_licensefrmlicensee/Id eq guid'75C021C2-AC51-E511-97F3-000C29D70C71' and (new_licensefrm_bingotype/Id eq guid'5C87C42E-AF6E-E511-ADAA-000C29D70C71' or new_licensefrm_sgctype/Id eq guid'83EBBB8B-056E-E511-8DF5-000C29D70C71')


    This is the ODATA Query with the Permit Holder who DOES EXIST on other licenses of type R or REG:

    https://internalcrm-dev.cobdev.local:444/BINGODEV/xrmservices/2011/OrganizationData.svc/new_s_licenseinformationSet?$select=new_licenseinfoid,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseeidnum,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseename&$expand=new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee&$filter=new_licensefrmlicensee/Id eq guid'05894CE0-0B71-E511-8AC9-000C29D70C71' and (new_licensefrm_bingotype/Id eq guid'5C87C42E-AF6E-E511-ADAA-000C29D70C71' or new_licensefrm_sgctype/Id eq guid'83EBBB8B-056E-E511-8DF5-000C29D70C71')

    Thank you all for your input and contributions as you pushed my mind closer to figuring it out!



    • Edited by ACECORP Friday, October 16, 2015 4:25 PM
    • Marked as answer by ACECORP Friday, October 16, 2015 4:25 PM
    Friday, October 16, 2015 4:24 PM

All replies

  • Hi,

    I read both of your posts (not carefully) but you are mixing your requirement using both client side (the onchange event of the field) and server side technology.

    As I wrote I didn't read the posts carefully, but I think will be very useful (in first instance for you and after in order to ask a question on a forum) to describe your exact requirement at the highest level possible and after down the level, without trying to put the implementation in the middle of this process.

    For example:

    Requirement (Highest level): When the Field X change, we need to check/execute the process Y

    Less High Level: When the Field X change and has a value, we need to check if the entity B has a record connected to entity C

    and go on.

    This because you know exactly how your data is structured and why was made with that combination of fields and relationships, me, when I see that SQL Query I don't see the same thing.

    The important is that you describe your requirement, after the right technology can be selected (javascript, odata, plugin, wf, sql query, etc etc)

    hope it helps


    My blog: www.crmanswers.net - CRM Theme Generator

    Friday, October 16, 2015 5:08 AM
  • Thanks for the suggestion, I edited both posts to include an easy to understand high level which is.

    HIGH LEVEL

    I need to achieve the following in a custom CRM 2011 App I developed: When I select a Permit Holder to go on a License Record. The ODATA query must check to see if any other license records exist in the system, that contain that permit holder. And if any records exist, I will do then have the same Jscript do something with them.

    My problem is that I have not been successful at constructing the ODATA query.  

    I used Rhett Clinton’s ODATA query constructor, but when I run it, I get an HTTP 400 error. I am thinking I am not selecting the options to properly construct the query.

    If I write it manually, I can get (too many) results with this - https://bingodev.cobdev.local:444/XRMServices/2011/OrganizationData.svc/new_s_licenseinformationSet?$select=new_licensefrmlicensee,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee&$expand=new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee

    But when I try to add in the final $filter portion, I get HTTP 400

    The relationship between license and permit holder (licensee) is that a license has only one permit holder, but the same permit holder can be on many licenses.

    Now I do have a SQL query that gets me EXACTLY what I need, and am wondering if instead of using ODATA rest, it would be easier to do this with a Plugin, since I have a pure SQL query that gets me exactly what I need.

    I would need to have Jscript “launch or call” the plug in, and then the plugin would do what I need the ODATA rest query to do, and then hand the result back to the JSCRIPT. Basically, if any records exist, I would have the JSCRIPT perform task set # 1 and if no records exist, the JSCRIPT would perform task set # 2.

    Everything Jscript works except for the part that, when I select a Permit Holder to go on a License Record, the ODATA query must check to see if any other license records exist in the system, that contain that permit holder. And if any records exist, I will do then have the same Jscript do something with them.

    The preferred solution is to get a darn ODATA REST query working since everything else is in place.

    Any suggestions or insight would be appreciated.


    • Edited by ACECORP Friday, October 16, 2015 1:58 PM
    Friday, October 16, 2015 1:35 PM
  • Can you post the $filter clause, so we can see if there's a syntax problem with it ? The other thing to check with the REST endpoint is the case you use for the attribute and entity names. The REST Endpoint uses the SchemaName, which can be a mix of upper and lowercase, and not the LogicalName (which is always lowercase). If you get the case wrong, it often returns a 400 error

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Friday, October 16, 2015 3:18 PM
    Moderator
  • I'm getting closer. The following ODATA query contains a Permit Holder that I know does indeed exist on another license of type REG -- so I get a result.

    https//internalcrm-dev.cobdev.local:444/BINGODEV/xrmservices/2011/OrganizationData.svc/new_s_licenseinformationSet?$select=new_licenseinfoid,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseeidnum,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseename&$expand=new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee&$filter=new_licensefrmlicensee/Id eq guid'05894CE0-0B71-E511-8AC9-000C29D70C71' and new_licensefrm_bingotype/Id eq guid'5C87C42E-AF6E-E511-ADAA-000C29D70C71' or new_licensefrm_sgctype/Id eq guid'83EBBB8B-056E-E511-8DF5-000C29D70C71'

    However, I encounter an undepected result when I run the same query but replace the Permit Holder with another Permit Holder who I know DOES NOT have the proper pre-requisite license and a record is still returned showing me the another Permit Holder exists on the license of type REG.

    https://internalcrm-dev.cobdev.local:444/BINGODEV/xrmservices/2011/OrganizationData.svc/new_s_licenseinformationSet?$select=new_licenseinfoid,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseeidnum,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseename&$expand=new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee&$filter=new_licensefrmlicensee/Id eq guid'75C021C2-AC51-E511-97F3-000C29D70C71' and new_licensefrm_bingotype/Id eq guid'5C87C42E-AF6E-E511-ADAA-000C29D70C71' or new_licensefrm_sgctype/Id eq guid'83EBBB8B-056E-E511-8DF5-000C29D70C71'

    The underlined Permit Holder GUID will always be different as we will always be searching for different Permit Holders.

    I believe I somehow need "do the ODATA equivalent" of the following SQL:

     where LI.new_s_licenseinformationid = @LicenseGUID and ((LI.new_licensefrm_bingotypename = 'R') or  (LI.new_licensefrm_sgctypename = 'REG'))

    Changing the ODATA rest query so it behaves like what's shown below

    and ((LI.new_licensefrm_bingotypename = 'R') or  (LI.new_licensefrm_sgctypename = 'REG'))

    would solve my problem because it would correctly return NO records when the Permit Holder DOES NOT exist on any other licenses of type REG or type R but would return records where the Permit Holder DOES exist on other licenses of type REG or R.

    Any additional help so I can get over that last "issue" would be greatly appreciated. I am so close.








    • Edited by ACECORP Friday, October 16, 2015 4:12 PM
    Friday, October 16, 2015 4:07 PM
  • W00T I solved it!

    This is the ODATA Query with the Permit Holder who DOES NOT EXIST on any other licenses of type R or REG:

    https://internalcrm-dev.cobdev.local:444/BINGODEV/xrmservices/2011/OrganizationData.svc/new_s_licenseinformationSet?$select=new_licenseinfoid,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseeidnum,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseename&$expand=new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee&$filter=new_licensefrmlicensee/Id eq guid'75C021C2-AC51-E511-97F3-000C29D70C71' and (new_licensefrm_bingotype/Id eq guid'5C87C42E-AF6E-E511-ADAA-000C29D70C71' or new_licensefrm_sgctype/Id eq guid'83EBBB8B-056E-E511-8DF5-000C29D70C71')


    This is the ODATA Query with the Permit Holder who DOES EXIST on other licenses of type R or REG:

    https://internalcrm-dev.cobdev.local:444/BINGODEV/xrmservices/2011/OrganizationData.svc/new_s_licenseinformationSet?$select=new_licenseinfoid,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseeidnum,new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee/new_licenseename&$expand=new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee&$filter=new_licensefrmlicensee/Id eq guid'05894CE0-0B71-E511-8AC9-000C29D70C71' and (new_licensefrm_bingotype/Id eq guid'5C87C42E-AF6E-E511-ADAA-000C29D70C71' or new_licensefrm_sgctype/Id eq guid'83EBBB8B-056E-E511-8DF5-000C29D70C71')

    Thank you all for your input and contributions as you pushed my mind closer to figuring it out!



    • Edited by ACECORP Friday, October 16, 2015 4:25 PM
    • Marked as answer by ACECORP Friday, October 16, 2015 4:25 PM
    Friday, October 16, 2015 4:24 PM