Filtering OData Model data at client side (SAP UI5 program)

[sun197895]

I have got all the data from Gateway Server to my SAPUI5 program. Now, I dont want to show all the data which I got but want to show only one particular line item. I tried putting filter but it is not working.

Can some one please help me how to use filter in OData model. Please note that I am trying to filter at client side and not at server side

[sun197895 · Accepted solution]

Hello All,

Firstof all thanks for the help provided by all of you which made me learn baby steps in creating a OData service and access it through SAP UI5 program. I am almost done but have one last stage issue, request your help.

I have implemented getEntity() method so that I can pass a parameter in the URL and get the line item related to that URL. The below URL is working fine when I execute it from browser or from netweaver gateway server directly .... below is the URL which works fine (reads a specific record as per URL parameter)

http://<NetWeaver Gateway Domain>:8000/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/UserInfoSampleSet('123098')

The above URL gives me one expected entry in XML format.

However, if I use below code in my SAPUI5 program it still retrives all the three records. Please see below code.

var oModel = new sap.ui.model.odata.ODataModel("/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/");

oModel.read("/UserInfoSampleSet('123098')/");

var oTable = new sap.ui.table.Table({width : "100%", visibleRowcount : 5});

oTable.addColumn(new sap.ui.table.Column({label : new sap.ui.commons.Label({text : "UserID"}), template : new sap.ui.commons.TextView({text : "{Zuserid}"})}));

oTable.addColumn(new sap.ui.table.Column({label : new sap.ui.commons.Label({text : "User Address"}), template : new sap.ui.commons.TextView({text : "{Zaddr}"}), visible : true}));

oTable.addColumn(new sap.ui.table.Column({label : new sap.ui.commons.Label({text : "Country"}), template : new sap.ui.commons.TextView({text : "{Zcountry}"}), visible : true}));

oTable.setModel(oModel);

oTable.bindRows({path : "/UserInfoSampleSet"});

return oTable;

[jmoors · Accepted solution]

You are binding the table to the "/UserInfoSampleSet" path which will return all the collection of results.

oTable.bindRows({path : "/UserInfoSampleSet"});

If I understand correctly you want to only display the single item, which you should use the filter. The table needs to be bound to a collection rather than individual entity.

Regards,

Jason

[sun197895 · Accepted solution]

I have to slightly disagree here but correct me if I am wrong.

I have implemented getEntity() method which basically takes an input (like Emp ID) and then queries the database and gives back that particular record. I can say that the getEntity() method is working as I am getting the expected result using below URL which I ran in browser as well as in gateway client.

http://<NetWeaver Gateway Domain>:8000/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/UserInfoSampleSet('123098')

oTable.bindRows(),...I guess this method is not going to the server,...it binds the data which OData has already retrieved to the SAPUI5 program from gateway server.

I guess I should be using oModel.read() .... but not sure how to pass parameters to achieve expected result.

Having said that,...I also tried below way but in vain. This gave me 'No Data'. I have checked the 'filter' check-box of the property Zuserid.

oTable.bindRows({path: "/UserInfoSampleSet?$filter=Zuserid eq '123098'"});

[kedarT · Accepted solution]

Hi,

How about using something like this:

oTable.bindAggregation("items","/UserInfoSampleSet",oTableItems,null,oFilter);

where oTableItems is the instance of sap.m.ColumnListItem

and oFitler = new sap.ui.model.Filter("Zuserid", "EQ",'123098')

Also make sure the Zuserid has a the same case as in your oData model entity properties.

Hope this helps.

[jmoors · Accepted solution]

Just to clarify:

• The table control can only bind to an aggregation (EntitySet), it needs to bind to an array, not an individual item (Entity).
• Have a look in your browser network tools you will see the bindRows will make a request, the read in your example is redundant.

I don't understand you metadata, is the unique identifier Zuserid or scid? I would recommend checking the HTTP request has the correct parameters being passed to gateway and then ensure you have the necessary filtering mechanism setup in gateway.

Regards,

Jason

[sun197895 · Accepted solution]

Let me explain you Jason.

Since I had an issue with my previous example I thought of creating a new service....which is below.

'Zuserid'  field is the Key..

My MetaData when I used URL : http://domain:port/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/$metadata

----------------

<edmx:Edmx xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"xmlns:sap="http://www.sap.com/Protocols/SAPData" Version="1.0">

<edmx:DataServices m:DataServiceVersion="2.0">

<Schema xmlns="http://schemas.microsoft.com/ado/2008/09/edm" Namespace="ZUI5_SAMPLEUSERINFO_SRV" xml:lang="en" sap:schema-version="0000">

<EntityType Name="UserInfoSample" sap:content-version="1">

<Key>

<PropertyRef Name="Zuserid"/>

</Key>

<Property Name="Zuserid" Type="Edm.String" Nullable="false" MaxLength="12" sap:label="Chgd by" sap:creatable="false" sap:updatable="false" sap:sortable="false"/>

<Property Name="Zaddr" Type="Edm.String" Nullable="false" MaxLength="50" sap:label="Address" sap:creatable="false" sap:updatable="false" sap:sortable="false"sap:filterable="false"/>

<Property Name="Zcountry" Type="Edm.String" Nullable="false" MaxLength="50" sap:label="Address" sap:creatable="false" sap:updatable="false" sap:sortable="false"sap:filterable="false"/>

</EntityType>

<EntityContainer Name="ZUI5_SAMPLEUSERINFO_SRV_Entities" m:IsDefaultEntityContainer="true">

<EntitySet Name="UserInfoSampleSet" EntityType="ZUI5_SAMPLEUSERINFO_SRV.UserInfoSample" sap:creatable="false" sap:updatable="false" sap:deletable="false" sap:pageable="false"sap:content-version="1"/>

</EntityContainer>

<atom:link xmlns:atom="http://www.w3.org/2005/Atom" rel="self" href="http://domain:port/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/$metadata"/>

<atom:link xmlns:atom="http://www.w3.org/2005/Atom" rel="latest-version" href="http://domain:port/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/$metadata"/>

</Schema>

</edmx:DataServices>

</edmx:Edmx>

-----------------

Now I used URL to test my getEntity() function which I implemented and it worked fine. URL is : http://domain:port/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/UserInfoSampleSet('123098')

-------------------

<entry xmlns="http://www.w3.org/2005/Atom" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xml:base="http://nwcedev1.amat.com:8000/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/">

<id>

http://domain:port/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/UserInfoSampleSet('123098')

</id>

<title type="text">UserInfoSampleSet('123098')</title>

<updated>2015-01-14T19:12:09Z</updated>

<category term="ZUI5_SAMPLEUSERINFO_SRV.UserInfoSample" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>

<link href="UserInfoSampleSet('123098')" rel="self" title="UserInfoSample"/>

<content type="application/xml">

<m:properties>

<d:Zuserid>123098</d:Zuserid>

<d:Zaddr>SORENIA STREET</d:Zaddr>

<d:Zcountry>SOMANIA</d:Zcountry>

</m:properties>

</content>

</entry>

-------------------

So whatever I am able to fetch using the above url (getting ne specific record) is what I what I want to achieve using my SAPUI5 program. Please advise.

[jmoors · Accepted solution]

What do you get if you try the following URL?

http://xxxx:port/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/UserInfoSampleSet$filter=Zuserid eq '123098'

If it returns the 3 results I expect that the filter code hasn't been implemented in the

_GET_ENTITYSET method? i.e. Task 2 in the document.

if you are trying to display in a table you will need to bind to the EntitySet i.e. "/UserInfoSampleSet"

Regards,

Jason

[sun197895 · Accepted solution]

So Jason,...the filter need to be implemented in _GET_ENTITYSET method ? I thought it needs to be implemented in _GET_ENTITY METHOD.

When I use below URL

http://nwcedev1.amat.com:8000/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/UserInfoSampleSet$filter=Zus... eq '123098'

I am getting below error message

<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">

<code>005056A509B11EE1B9A8FEC11C21D78E</code>

<message xml:lang="en">

Resource not found for the segment 'UserInfoSampleSet$filter=Zuserid%20eq%20'123098''.

</message>

<innererror xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">

<transactionid>54B6DF5C7E800A10E10000000A298945</transactionid>

<timestamp>20150115072944.2021590</timestamp>

<Error_Resolution>

<SAP_Transaction>

Run transaction /IWFND/ERROR_LOG on SAP NW Gateway hub system and search for entries with the timestamp above for more details

</SAP_Transaction>

<SAP_Note>

See SAP Note 1797736 for error analysis (https://service.sap.com/sap/support/notes/1797736)

</SAP_Note>

</Error_Resolution>

</innererror>

</error>

I will also go through the link provided by you. Thanks again Jason..

[ChandrashekharMahajan · Accepted solution]

Hi,

you can have look on blog where I explained how to implement get_entityset with filter etc.

Regards,

Chandra

[sun197895 · Accepted solution]

Hi Chandrasekhar

I infact have created my OData service using your blog only.Thanks for that first.

However, in your blog you have implemented two methods ..._GET_ENTITYSET &amp; _GET_ENTITY.

In _GET_ENTITYSET method : your code is retrieving the whole set of data from database and there is no condition applied on the query.

I did same way so I am getting all the data which is there in the table. So fine till here.

In _GET_ENTITY method : You have written a query based on a condition. I have implemented same way. When

I use URL : http://domain:port/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/UserInfoSampleSet('123098') in Netweaver Gateway client it gives me one record in XML format.

It is all fine until here. But my actual requirement : To implement code in SAPUI5 program where I can pass a parameter to Gateway server and depending upon the parameter the record is retrieved.

Say, I want to a record to be retrieved depending upon the UserID. In my SAPUI5 program I will be sending userId value to gateway server and depending upon the UserId value that particular record should be given back to my SAPUI5 program. So this is what I am struggling to achieve.

Now to achieve this, will I have to write code in _GET_ENTITYSET method or _GET_ENTITY method?

Please advise.

[ChandrashekharMahajan · Accepted solution]

If UserID is your key property in entity User (entity collection may be UserCollection) then you need to implement to get_entity to get that particular record. Check the code in Read operation of the blog.

*Get the key property values
READ TABLE it_key_tab WITH KEY name = 'UserID' INTO ls_key_tab.

  lv_userid = ls_key_tab-value.

it_key_tab is your importing parameter of the method.

in your UI5 application, you will be able to read the record as below for e.g.,

oModel.read('/Products(1)', null, null, true, function(oData, oResponse){
  alert("Read successful: " + JSON.stringify(oData));
  },function(){
alert("Read failed");});

Check OData Write Support - SAPUI5 Developer Guide - SAP Library

Regards,

Chandra

[sun197895 · Accepted solution]

Hi Chandra,

Thanks for helping me with the oModel.read() method.

So now when I used the oModel.read() method code posted by you, I am getting an alert with OData content which has only 1 item which is what is expected....but my table shows all three items. So I see some problem with the binding here. Below is my SAPUI5 code ... please advice where changes are required.

var oModel = new sap.ui.model.odata.ODataModel("/sap/opu/odata/sap/ZUI5_SAMPLEUSERINFO_SRV/");

oModel.read("/UserInfoSampleSet('123098')", null, null, true, function(oData, oResponse){

   alert("Read successful: " + JSON.stringify(oData));

   },function(){

  alert("Read failed");});

<created table and created columns here>

oTable.setModel(oModel);

oTable.bindRows({path : "/UserInfoSampleSet"});

Though I am reading only one line item in oModel.read() method, I some how not able to know how to bind only that single line item retrieved

by oModel.read() to oTable.

I think my last line of code is binding my oTable to all rows. :  oTable.bindRows({path : "/UserInfoSampleSet"});

If I cannot bind oTable to a single entry then please let me know which control I need to use to show this one line item in the browser.

[ChandrashekharMahajan · Accepted solution]

Hi,

here you are binding aggregation to table. it means bindRows expect entity collection which is /UserInfoSampleSet. hence the call will go to get_entityset method of DPC_EXT class.

you can put the filter logic in get_entityset method and then get the value of UserID and accordingly get the filtered data into collection

OData Query	Method Parameter	Alternative way to get the value	Coding required to implement Query Operation
UserCollection?$filter=UserID eq '123' and LastName eq 'Mahajan'	IT_FILTER_SELECT_OPTIONS and IV_FILTER_STRING	DATA: my_filter_options TYPE/iwbep/t_mgw_select_option, my_filter_string TYPE string. my_filter_options =io_tech_request_context->get_filter( )->get_filter_select_options( ). my_filter_string  = io_tech_request_context->get_filter( )->get_filter_string( ).	Yes

As mentioned in the table, you will get the value in method importing parameter or by using code provided in alternative way. Check the code in

put external breakpoint in method get_entityset and accordingly put the logic. once you will get the UserID say in variable lv_userid, pass it to select query as below

METHOD usercollection_get_entityset.

  DATA: lt_userinfo TYPE TABLE OF zuserinfo,
        ls_userinfo LIKE LINE OF lt_userinfo,
        ls_entity   LIKE LINE OF et_entityset.

*Get data from ZUSERINFO table
  SELECT * FROM zuserinfo INTO TABLE lt_userinfo where userid = lv_userid.

*Fill ET_ENTITYSET
  LOOP AT lt_userinfo INTO  ls_userinfo .
    ls_entity-userid    = ls_userinfo-userid.
    ls_entity-firstname = ls_userinfo-firstname.
    ls_entity-lastname  = ls_userinfo-lastname.
    ls_entity-email     = ls_userinfo-email.
    ls_entity-phone     = ls_userinfo-phone.
    ls_entity-country  = ls_userinfo-country.
    APPEND ls_entity TO et_entityset.
  ENDLOOP.

ENDMETHOD.

On UI5 side, you can put the code as mentioned here JS Bin - Collaborative JavaScript Debugging

Regards,

Chandra

[sun197895 · Accepted solution]

I think I am just a step away in achieving it.

As you said, below is the what I implemented in get_entitySet() method.

---------------------------------

DATA: lt_filters          TYPE  /iwbep/t_mgw_select_option,

      ls_filter           TYPE  /iwbep/s_mgw_select_option,

      ls_so               TYPE  /iwbep/s_cod_select_option,

      my_filter_string    TYPE  string,

      lt_userinfo         TYPE table of zuser_info,

      ls_userinfo         like line of lt_userinfo,

      ls_entity           LIKE LINE OF    et_entityset,

      str                 TYPE string.

lt_filters = io_tech_request_context->get_filter( )->get_filter_select_options( ).

my_filter_string  = io_tech_request_context->get_filter( )->get_filter_string( ).

str = my_filter_string+9(12).

REPLACE ALL OCCURRENCES OF '=' IN str WITH ''.

SELECT single * FROM zuser_info INTO ls_userinfo WHERE Zuserid = str.

    if sy-subrc = 0.

          ls_entity-zuserid    = ls_userinfo-Zuserid.

          ls_entity-Zaddr = ls_userinfo-Zaddr.

          ls_entity-Zcountry  = ls_userinfo-Zcountry.

          APPEND ls_entity TO et_entityset.

    else.

            SELECT * FROM zuser_info INTO table et_entityset.

    endif.

------------

However I am not getting expected result. sy-subrc value is not 0 meaning the query is not retrieving any rows so it is going to else part. What I found in debugging is

'my_filter_string' value is coming as : ( ZUSERID = '123098' ).

I did not know how to get the value from this string...so I substring so that I can get only the digit part. Below is the code.

str = my_filter_string+9(12).

REPLACE ALL OCCURRENCES OF '=' IN str WITH ''.

Now if I see in the debug the value of str is : '123098'.

But the query "SELECT single * FROM zuser_info INTO ls_userinfo WHERE Zuserid = str." is not retrieving any rows.

Whereas, if I hard code the condition in the query as below then it works..

SELECT single * FROM zuser_info INTO ls_userinfo WHERE Zuserid = '123098'.

May be I am doing a very silly mistake but since I am from java/portal developer so finding it tough with ABAP programming. Thanks in advance.

[sun197895 · Accepted solution]

As I guessed it was a simple mistake. I did not replace apotrophe...which I replaced now and it works fine.

Thanks a lot Chandra and thanks a lot Jason moors and all others on the post who helped me.

Closing the thread.