Standard Text Objects Search in S/4HANA using CDS Views
Text Search using CDS views is currently limited to 255 characters per text field. Sometimes the requirement is to search on the string more than 255 characters per field such as PO text search or Service text search. This post gives some techniques to overcome this limitation.
For example, the typical Government’s public works department uses the text as a major identifier for the infra structure work that is being carried out at several places. Hence this demands the search of the Services by text which has the maximum length more than 1000 characters. Assume that the Service master data is being used to capture the work done. The texts in the Services master data (transaction AC03) is stored as a Clustered format in STXH/STXL tables, the search of the same via CDS is not possible since the binary objects conversion to text is not possible in CDS Views/Open SQL.
Text Object Table which stores the information in clustered format:
Below screenshot shows that text object storage table which stores the information in clustered format.
Typical Service master transaction storing multiple lines of long-text:
Below screenshot shows how the work is being captured as a service. The Long text is one of the key identifiers for a search criteria.
How to Overcome this limitation:
Typical approach is to use READ_TEXT function module on a record by record basis. However, it will cost huge performance bottleneck on the ABAP application server. The Better and more efficient approach is explained below.
Create as many as fields as to the maximum requirement of the texts in a new table which shall be used to retrieve the texts. If the requirement is about 1000 characters, then create at least 4 fields with 250 characters to accommodate these texts.
When these texts are saved in the standard cluster object, it should be split and stored in these custom text fields. We shall also use a program that runs as a batch job for fetching the long text, splitting and updating it to this new table.
These fields shall be used together to achieve the search functionality and to arrive at the result – for example work ID, place, amount of work done etc. These long text fields can be added in CDS and enable search annotations.
Once the search is accomplished on all these fields together, UI will display the concatenated text results after search.
Below is a sample CDS view code snippet:
The last four elements are the split of the long text into 4 fields which can hold up to 1000 characters length.
To conclude, the solution described above is the workaround to enable search of the long texts (which uses STXH/STXL framework) more than 255 characters in CDS views. There is a overhead to store the duplicate data in additional tables and also a processing overhead to fetch the changed text and push it to the new tables. Hence this shall be adapted if and only if there is a requirement to search based on the long texts which uses text objects.