Retrieve records depending upon a text field value

(0) Share

Report

Posted on by Turbo Forms

277

Hi Experts, we have a field on Account called new_newcode which holds a unique text in it and another entity B text field (new_transferfromcode) where it will be referenced manually. Initial architecture was messed up so they created Text fields instead of Lookups.

Here is the requirement, when new_transferfromcode is entered, it should pull address from Account based on the text entered in new_transferfromcode. Wrote this below code and it's not working, any help would be appreciated. Thank you.

function codeDetails(executionContext) {
    debugger;
    formContext = executionContext.getFormContext();

    var Fromcode = formContext.getAttribute("new_transferfromcode").getValue();

    var result;
    var req = new XMLHttpRequest();
    req.open("GET", csjs.clientURLVersion() + "/api/data/v8.2/accounts?$select=new_newcode,new_physicaladdress,new_physicalcity,new_physicalstate,new_physicalzip,name,createdby&$filter=_new_newcode_value eq " + Fromcode + " &$orderby=createdby desc", true);
    req.setRequestHeader("OData-MaxVersion", "4.0");
    req.setRequestHeader("OData-Version", "4.0");
    req.setRequestHeader("Accept", "application/json");
    req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
    req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");
    req.onreadystatechange = function () {
        if (this.readyState === 4) {
            req.onreadystatechange = null;
            if (this.status === 200) {
                var results = JSON.parse(this.response);
                var recordCount = results["@odata.count"];
                for (var i = 0; i < results.value.length; i++) {
                    var new_newcode = results.value[i]["new_newcode"];
                    var new_physicaladdress = results.value[i]["new_physicaladdress"];
                    var new_physicalcity = results.value[i]["new_physicalcity"];
                    var new_physicalstate = results.value[i]["new_physicalstate"];
                    var new_physicalstate_formatted = results.value[i]["new_physicalstate@OData.Community.Display.V1.FormattedValue"];
                    var new_physicalzip = results.value[i]["new_physicalzip"];
                    var name = results.value[i]["name"];

                    formContext.getAttribute("new_comments").setValue(new_physicaladdress);
                }
            }
            else {
                Xrm.Utility.alertDialog(this.statusText);
            }
        }
    };
    req.send();
 
}

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

Pawar Pravin 5,237 on at

Like (0)

Report

RE: Retrieve records depending upon a text field value

Hi ,

Can you please run your api with hardcoded value and check it once. For ex.

https://yourinstancename.crm5.dynamics.com+ "/api/data/v8.2/accounts?$select=new_newcode,new_physicaladdress,new_physicalcity,new_physicalstate,new_physicalzip,name,createdby&$filter=_new_newcode_value eq " + "hardcoded_value" + " &$orderby=createdby desc"

Anyway if you are using 9.0 version then you can use Xrm.web.RetriveMultipleRecords as well

Was this reply helpful? Yes No
Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report
RE: Retrieve records depending upon a text field value

Hi ,

Are you getting any error ? try to debug see you are getting the result or not .

In addition try with below code , make sure you are writing the value inside single quote -

req.open("GET", csjs.clientURLVersion() + "/api/data/v8.2/accounts?$select=new_newcode,new_physicaladdress,new_physicalcity,new_physicalstate,new_physicalzip,name,createdby&$filter=_new_newcode_value eq '" + Fromcode + "'&$orderby=createdby desc", true);

Was this reply helpful? Yes No
Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report
RE: Retrieve records depending upon a text field value

Another answer , its should work removing "OrderBy"-

Two things you need to do -

1) Add the text value inside '"+ + "'

2)Remove the Order By.

req.open("GET", csjs.clientURLVersion() + "/api/data/v8.2/accounts?$select=new_newcode,new_physicaladdress,new_physicalcity,new_physicalstate,new_physicalzip,name,createdby&$filter=_new_newcode_value eq '" + Fromcode + "'", true);

Was this reply helpful? Yes No
Turbo Forms 277 on at

Like (0)

Report

RE: Retrieve records depending upon a text field value

Hi Gautham, Thanks for your time. I have tried filter=new_newcode eq '" + Fromcode + "'", true) and got an error saying not found

Within the CRM Rest Builder, when I created the query and manually passed a value for "Fromcode" it provided correct values when I use URL generated.

FYI.. From code is text field and I'm trying to run onCHange

api/data/v8.2/$metadata#accounts(new_physicaladdress,new_physicalcity,new_physicalstate,new_physicalzip)","value":[{"@odata.etag":"W/\"1004760438\"","new_physicaladdress":"213 tst","new_physicalcity":"tester","new_physicalstate":800000006,"new_physicalzip":"12344","accountid":"95acbe85-2da6-e811-a96a-000d3a109495"}]}

Was this reply helpful? Yes No
Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report

RE: Retrieve records depending upon a text field value

No still you are doing wrong should be like this -

' " + Fromcode +" ' ", true)

For your uunderstanding I put space inside quotation.

Was this reply helpful? Yes No
Turbo Forms 277 on at

Like (0)

Report
RE: Retrieve records depending upon a text field value

Hi Gautam, I did exactly like you said. May be I'm missing something. here is the complete function, I'm calling onChange.

function CodeDetails(executionContext) { debugger; formContext = executionContext.getFormContext(); var FromCode = formContext.getAttribute("new_fromcode").getValue(); var ToCode = formContext.getAttribute("new_tocode").getValue(); var result; var req = new XMLHttpRequest(); req.open("GET", csjs.clientURLVersion() + "/api/data/v8.2/accounts?$select=new_newcode,new_physicaladdress,new_physicalcity,new_physicalstate,new_physicalzip,name&$filter=new_newcode eq ' " + FromCode + " ' ", true); req.setRequestHeader("OData-MaxVersion", "4.0"); req.setRequestHeader("OData-Version", "4.0"); req.setRequestHeader("Accept", "application/json"); req.setRequestHeader("Content-Type", "application/json; charset=utf-8"); req.setRequestHeader("Prefer", "odata.include-annotations=\"*\""); req.onreadystatechange = function () { if (this.readyState === 4) { req.onreadystatechange = null; if (this.status === 200) { var results = JSON.parse(this.response); var recordCount = results["@odata.count"]; for (var i = 0; i < results.value.length; i++) { var new_newcode = results.value[i]["new_newcode"]; var new_physicaladdress = results.value[i]["new_physicaladdress"]; var new_physicalcity = results.value[i]["new_physicalcity"]; var new_physicalstate = results.value[i]["new_physicalstate"]; var new_physicalstate_formatted = results.value[i]["new_physicalstate@OData.Community.Display.V1.FormattedValue"]; var new_physicalzip = results.value[i]["new_physicalzip"]; var name = results.value[i]["name"]; formContext.getAttribute("new_detaildescription").setValue(new_physicaladdress); } } else { csjs.openAlertDialog(this.statusText); } } }; req.send(); }

Error I'm getting onChange is

Was this reply helpful? Yes No
Turbo Forms 277 on at

Like (0)

Report

RE: Retrieve records depending upon a text field value

When debugged, " + FromCode + " is being shown as" + value + ", From code value is shown correct but it's jumping out of the loop after, if (this.readyState === 4) { req.onreadystatechange = null

Was this reply helpful? Yes No
Suggested answer

gdas 50,091 Moderator on at

Like (0)

Report

RE: Retrieve records depending upon a text field value

Please follow below step

Step 1- declare

var globalContext = Xrm.Utility.getGlobalContext();

Step 2- csjs.clientURLVersion() need to replace with globalContext.getClientUrl();

Step 3- I can see still space is there in the quotation which i have given because you to understand. Remove the space first and then I would suggest try to debug and get the query with complete url with value in a notepad then browse with chrome browser see what error you are getting.

You should get url like below -

https:\\xxxxxx/api/data/v8.2/accounts?$select=new_newcode,new_physicaladdress,new_physicalcity,new_physicalstate,new_physicalzip,name&$filter=new_newcode eq 'CODENO'

Replace xxxxx with your server url name.

Here the code should be look like, Try with this -

function CodeDetails(executionContext) {

debugger;

formContext = executionContext.getFormContext();

var globalContext = Xrm.Utility.getGlobalContext();

var FromCode = formContext.getAttribute("new_fromcode").getValue();

var ToCode = formContext.getAttribute("new_tocode").getValue();

var result;

var req = new XMLHttpRequest();

req.open("GET", globalContext.getClientUrl()+ "/api/data/v8.2/accounts?$select=new_newcode,new_physicaladdress,new_physicalcity,new_physicalstate,new_physicalzip,name&$filter=new_newcode eq '" + FromCode + "'", true);

req.setRequestHeader("OData-MaxVersion", "4.0");

req.setRequestHeader("OData-Version", "4.0");

req.setRequestHeader("Accept", "application/json");

req.setRequestHeader("Content-Type", "application/json; charset=utf-8");

req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");

req.onreadystatechange = function () {

if (this.readyState === 4) {

req.onreadystatechange = null;

if (this.status === 200) {

var results = JSON.parse(this.response);

var recordCount = results["@odata.count"];

for (var i = 0; i < results.value.length; i++) {

var new_newcode = results.value[i]["new_newcode"];

var new_physicaladdress = results.value[i]["new_physicaladdress"];

var new_physicalcity = results.value[i]["new_physicalcity"];

var new_physicalstate = results.value[i]["new_physicalstate"];

var new_physicalstate_formatted = results.value[i]["new_physicalstate@OData.Community.Display.V1.FormattedValue"];

var new_physicalzip = results.value[i]["new_physicalzip"];

var name = results.value[i]["name"];

formContext.getAttribute("new_detaildescription").setValue(new_physicaladdress);

}

}

else {

csjs.openAlertDialog(this.statusText);

}

}

};

req.send();

}

Was this reply helpful? Yes No
Suggested answer

RaviKashyap 55,410 Moderator on at

Like (0)

Report

RE: Retrieve records depending upon a text field value

Hi Turbo Forms,

It is expected that while debugging the debugger step out of this statement if (this.readyState === 4). This is because the readystate changes from 1,2,3 & 4. The logic which we have currently is to execute when the readystate reaches to 4. so instead putting a debugger on start, put a debugger on if (this.status === 200).

Also, based on your error message which "Not Found", this means the requested url which you have build is not correct. You need to review the URL. It is possible that the field name you have used is not correct. Try it with a simple code first:

====================

function CodeDetails(executionContext) {

formContext = executionContext.getFormContext();

var FromCode = formContext.getAttribute("new_fromcode").getValue();

var ToCode = formContext.getAttribute("new_tocode").getValue();

var req = new XMLHttpRequest();

req.open("GET", Xrm.Page.context.getClientUrl() + "/api/data/v8.2/accounts?$select=name&$filter=new_fromcode eq '"++"'", true);

req.setRequestHeader("OData-MaxVersion", "4.0");

req.setRequestHeader("OData-Version", "4.0");

req.setRequestHeader("Accept", "application/json");

req.setRequestHeader("Content-Type", "application/json; charset=utf-8");

req.setRequestHeader("Prefer", "odata.include-annotations=\"*\"");

req.onreadystatechange = function () {

if (this.readyState === 4) {

req.onreadystatechange = null;

if (this.status === 200) {

var results = JSON.parse(this.response);

for (var i = 0; i < results.value.length; i++) {

var name = results.value[i]["name"];

Xrm.Utility.alertDialog(name);

}

} else {

Xrm.Utility.alertDialog(this.statusText);

}

}

};

req.send();

}

====================

Hope this helps.

Was this reply helpful? Yes No