locked
VERY SPECIFIC ODATA REST QUERY - problems formatting 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

    In my CRM 2011 application I have an entity named new_s_licenseinformation, for ease of understanding we shall call it License.

    I also have another entity named new_s_licenseeinfo, for ease of understanding we shall call it Permit Holder.

    License (new_s_licenseinformation) has a GUID which is located in the field new_s_licenseinformationid.

    We shall call that field License GUID Field.

    Permit Holder (new_s_licenseeinfo) has a GUID which is located in the field new_s_licenseeinfoid.

    We shall call that field Permit Holder GUID Field.

                   

    On License (new_s_licenseinformation), there is lookup field, which is named new_licensefrmlicensee

    The new_licensefrmlicensee lookup field links to the Permit Holder (new_s_licenseeinfo) entity, specifically to the Permit Holder GUID fieldwhich is new_s_licenseeinfoid.

    If I view the relationship area of License (new_s_licenseinformation), the relationship between License (new_s_licenseinformation), and Permit Holder (new_s_licenseeinfo) via the lookup field on License named new_licensefrmlicensee is listed as a Many to 1 Relationship with Referential behavior. The name of the relationship is listed as new_new_s_licenseeinfo_new_s_licenseinformation_licensefrmlicensee.

    Also on License (new_s_licenseinformation), there is lookup field named new_licensefrm_sgctype

    This new_licensefrm_sgctype lookup field contains many different values, but we are only concerned with the value SPC and REG.

    The business rule is simple. A permit holder cannot be placed on a license whose value is SPC, unless another license already exists that has a value of REG and the given permit holder has already been placed on that license.

    Specifically, assume permit holder Bob Smith walks in. He already exists in the system as a Permit Holder record and the value of his Permit Holder GUID field (new_s_licenseeinfoid) is 05894ce0-0b71-e511-8ac9-000c29d70c71

    The SPC license we want to assign Bob to has a GUID of 9699650D-6D73-E511-872C-000C29D70C71

    Bob may have already been placed on 10 licenses, but we only care if he has already been placed on a license whose value is REG.

    Therefore, if someone using the application tries to place Bob Smith on an SPC License (that means the value of new_licensefrm_sectype = SPC) , something must first query all licenses to see Bob Smith’s on any of the ones that have new_licensefrm_sectype = REG. If no licenses exist the value of REG that have Bob Smith on it, then the new_licensefrm_sgctype lookup field cannot be set to SPC, and simply blanks out.  (I can do the blank out no problem)

    My problem is finding if any licenses with REG as the new_licensefrm_sectype value exist with Bob Smith as the permit holder on the license.

    I have a SQL query that gets me exactly what I need, but this is useless to me because I can’t use jscript onChange to pass it the GUID of the current license record, and let it run to find out of the Permit Holder exists on any REG Licenses that may be  out there. I need to do the following SQL query as an ODATA rest so I can do it from Jscript onChange

    use BINGODEV_MSCRM;
    declare @LicenseGUID varchar(50)
    set @LicenseGUID = '9699650D-6D73-E511-872C-000C29D70C71'
    
    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)

    Therefore, I need to create a JavaScript ODATA Rest Query that executes on the new_licensefrm_sgctype lookup field’s OncChange event so that if the value of new_licensefrm_sectype is SPC, the following things will happen:

    • The value of the new_licensefrmlicenseefield, on the current form, which is the same value as the Entity 2 GUID Field will be obtained  -- This I have already done – I can get this value easy on change as needed
    • Then, take the value obtained from the previous step, and select via ODATA REST, all Entity 1 GUIDs where the value of new_licensefrmlicensee is the same as the value that is set on the current record I am on and the value of  new_licensefrm_sectype = REG

    (a) If a GUID is returned, that means the Permit Holder exsits on another license has REG as its value for new_licensefrm_sgctype. Therefore my jscript will allow the user to select the SPC value for new_licensefrm_sgctypeon the current license record.

    (b) If nothing is returned, that means the permit holder doesn’t exist on another license that has REG as the value of new_licensefrm_sgctype. And since no data comes back, the user is prevented from setting the value to SPC on the current license record.

    I am trying to build my ODATA query in the browser first so I confirm it gives me what I need and so far I have the following:

    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

    This is halfway done because it gives me all the License records and basically does the same as  a SQL inner join to the Permit Holder record that links from each License record via the lookup.

    The problem is that I can’t figure out how to add $filter so that I am selecting only those License Records - License (new_s_licenseinformation)  whose  new_licensefrm_sgctype field = REG  and whose new_licensefrmlicensee field = the currently selected licensee that’s on the current license record we are working with

    How can I add a $filter to get any  License Records (new_s_licenseinformation) that contain the Bob Smith Permit Holder GUID of 05894ce0-0b71-e511-8ac9-000c29d70c71 in the new_s_licenseeinfoid field on the Permit Holder Record which links to the License Record via the new_licensefrmlicensee lookup field

    I tried the following, but it breaks my query..

    • $filter=new_s_licenseeinfoid/Id eq (Guid'05894ce0-0b71-e511-8ac9-000c29d70c71')
    • $filter=new_licensefrmlicensee eq (Guid'05894ce0-0b71-e511-8ac9-000c29d70c71')
    • $filter=new_licensefrmlicensee/Id eq (Guid'05894ce0-0b71-e511-8ac9-000c29d70c71')

    Hoping someone can help!





    • Edited by ACECORP Friday, October 16, 2015 2:01 PM
    Friday, October 16, 2015 3:30 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!


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