I'm sure you've heard of Representations, but do you know what you can use them for, and that you can change the set of fields that can be used for filtering and sorting?
Well, first of all, Representations can be used in:
1) Excel Workbooks
Representations can be queried using Sage X3's Excel Add-in
2) REST Web Service Calls
Representations can be executed as if they were REST Web Services, and they return the results of the query as JSON text.
How can you change the list of fields which can be used for filtering and sorting query result-sets?
Well, Representations are only views based on their associated Class and the Class has a list of Fields with different Properties including "Searchable" which denotes whether or not a Field can be used in Filtering and Sorting. Now, that's all fine and well but the searchable Field(s) must also be available in the Representation - this is done by ensuring that the "Query" attribute/option is ticked in the Available Properties section of the Representation. Once that's done and the Class and Representation have been Validated, you should be able to Filter and Sort by the appropriate fields in Excel and REST Web Service Calls.
The best way to demonstrate this is by working through an example.
"Can we have the FCY field enabled for Filtering and Sorting the results for BPPARTNER Entity within Excel?"
Step 1 Enable "Searchable" in the Class
Fortunately, FCY is already enabled
Step 2 : Check to see if "Query" is enabled in the Representation
Unfortunately, FCY isn't enabled for Query as standard.
I decided to clone the standard BPARTNER Representation and called it XPARTNER and then enabled FCY Query
Step 3: Validate appropriate Class and Representation
In this instance, I only need to validate XPARTNER.
Once those three steps are completed, I'm able to see ZBPARTNER listed in Excel
and Site is now available for both Filtering and Sorting purposes:
Please note that there is currently an issue with Sorting - you may experience an error when trying to specify a Sort Field - in the worked example, I received the following error:
The good news is, as of Syracuse 12.16, you can still use Excel's sort-by-column functionality to provide the same results.
This issue has been raised with Development and should be fixed in a later release.
For REST Web Service calls, you can add FCY as a "where" and "orderBy" clause to REST calls such as
http://x3erpv12sqlvm:8124/api1/x3/erp/X3ERPV12_SEED/BPARTNER?representation=XBPARTNER.$query&count=10&orderBy=BPRNUM&where=left(FCY,2) eq 'GB'
Note that some fields cannot be used for Filtering/Sorting as they are Collections - for example, Addresses.