Clarity

  • Private Community
Back to discussions
Expand all | Collapse all

XOG out filtering with "starts with" criteria?

  • 1.  XOG out filtering with "starts with" criteria?

    Posted Oct 11, 2012 03:03 PM
    I would like to export project data similar to the Project list's excel export, but using XOG since it will be an automated solution. The thing is, the business rule is to filter these is that a project name must begin with a certain keyword. I looked through the XOG documentation and the only filtering options are EQUALS, OR, BEFORE, AFTER, and BETWEEN. I am not sure any of these would satisfy the requirement, unless there are more I am not aware of. Can anyone suggest a way to do this?


  • 2.  RE: XOG out filtering with "starts with" criteria?

    Posted Oct 11, 2012 03:18 PM
    Just create a custom query (where you can control the filters explicitly); you can then call that over the query-api web-service just you would do with a XOG call.

    (or forget about XML calls and just run the query directly and dump then extracted data out however you need to (eg through GEL and writing a flat file))


  • 3.  RE: XOG out filtering with "starts with" criteria?

    Posted Oct 11, 2012 05:56 PM
    If you want to do it with XOG object api you can use BETWEEN filter
    if project name is a filter attribute
    BETWEEN (ABC,ABCz) will give all that start with ABC


    Martti K.


  • 4.  RE: XOG out filtering with "starts with" criteria?

    Posted Oct 15, 2012 06:10 AM
    To perform a 'starts with' or other wildcard pattern match, use EQUALS but include one or more asterisk characters where you want it to behave:

    E.g. Extracts projects nick, nick1, nick2, nickabcd, etc.

    <Query>
    <Filter name="projectID" criteria="EQUALS">nick*</Filter>
    </Query>

    E.g. Extracts projects nick, nick1, nick2, nickabcd, nika1, etc.:
    <Query>
    <Filter name="projectID" criteria="EQUALS">ni*k*</Filter>
    </Query>

    The XOG object reader API that builds the query filter will see the asterisk and change it automatically from an '=' operation to a 'LIKE' operation instead.


  • 5.  RE: XOG out filtering with "starts with" criteria?

    Posted Oct 15, 2012 10:22 AM
    Monday's tip: XOG supports wildcards (Documented?)
    Thanks Nick.

    Martti K.


  • 6.  RE: XOG out filtering with "starts with" criteria?

    Posted Oct 15, 2012 11:53 AM
    It's not documented, and there could be a reason for that - whilst it's a deliberate behaviour/decision in the general Object filtering code, there are some cases where this doesn't work as some objects have (had to?) implemented their own specific filter mechanism instead. So it isn't available in all cases.

    For an example, it doesn't work with the security groups filters (e.g. groups_read.xml).

    There'll be some follow-up actions for me to do on/from this.


  • 7.  RE: XOG out filtering with "starts with" criteria?

    Posted Oct 16, 2012 02:26 AM
    For groups BETWEEN works if you can define a range like

    <Filter name="code" criteria="BETWEEN">cs,csl</Filter>

    will bring all the groups where the code starts with csk. (the PMO groups)

    Martti K.


  • 8.  RE: XOG out filtering with "starts with" criteria?

    Posted Nov 13, 2012 05:19 AM
    Hi,

    I'm trying something similar - need to XOG out all groups with name beginning "FEP".

    I can't use the BETWEEN filter on id/code as unfortunately the IDs have not been consistently applied

    It sounds like the OR criteria will give me the LIKE behaviour that I'm after - but my XOG below returns no records (no error, just no records)
    <?xml version="1.0" encoding="UTF-8"?>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
  <Header version="7.5" action="read" objectType="group" externalSource="NIKU"/>
  <Query>
    <Filter name="name" criteria="OR">fep%</Filter>
  </Query>
</NikuDataBus>
    If this is not possible, is it possible to manually create a single XOG read by listing all the IDs in one script?

    I've tried something like this but again getting errors (I guess there's a constraint on how many filters you can do a single attribute?) 
    <?xml version="1.0" encoding="UTF-8"?>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
  <Header version="7.5" action="read" objectType="group" externalSource="NIKU"/>

  <Query>
    <Filter name="code" criteria="EQUALS">v_ccb_ba</Filter>
    <Filter name="code" criteria="EQUALS">v_c&s_ccb</Filter>
  </Query>

</NikuDataBus>
    Any help much appreciated!

    Regards

    Alex


  • 9.  RE: XOG out filtering with "starts with" criteria?
    Best Answer

    Posted Nov 13, 2012 05:32 AM
    Are you sure you can't use BETWEEN?

    Wouldn't this suffice?
    <?xml version="1.0" encoding="UTF-8"?>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
  <Header version="7.5" action="read" objectType="group" externalSource="NIKU"/>

  <Query>
    <Filter name="code" criteria="BETWEEN">FEP,FEQ</Filter>
  </Query>

</NikuDataBus>
    To test, I created groups "FEP", "FEP2", and "FEP ABC" and all 3 were extracted using this condition. Unless you also have one called 'FEQ' that should be fine (as it will also be included if so), otherwise you'll have to use something like 'FEPzzz' for the range ending marker.


  • 10.  RE: XOG out filtering with "starts with" criteria?

    Posted Apr 30, 2014 05:25 AM

    You are a legend Nick :)
     



  • 11.  RE: XOG out filtering with "starts with" criteria?

    Posted Nov 13, 2012 05:34 AM
    If name can be used as a filter then is should be with EQUALS not OR

    that is
    <Filter name="name" criteria=" EQUALS">fep%</Filter>

    as in Nick's example

    I don't think you can use the same item for filtering more than once so the other option using OR should be

    <Filter name="code" criteria="OR">v_ccb_ba,v_c&s_ccb</Filter>

    Martti K.


  • 12.  RE: XOG out filtering with "starts with" criteria?

    Posted Nov 13, 2012 05:35 AM
    Shouldn't it (GUESSing) be something like 
    <Filter name="name" criteria="OR">v_ccb_ba, v_c&s_ccb</Filter>
    (don't think anyone has said that the wildcard works with OR, just with EQUALS?)

    EDIT : wow a record 3 simultaneous posts there! :grin:


  • 13.  RE: XOG out filtering with "starts with" criteria?

    Posted Nov 13, 2012 05:39 AM
    Yes three simultaneous is a first.:lol:

    BETWEEN SHOULD be OK if the names are in a between range.

    But in the first place can group name be a filter?


    Martti K.


  • 14.  RE: XOG out filtering with "starts with" criteria?

    Posted Nov 13, 2012 05:44 AM
    Ah, I see, the codes are inconsistent to the group name prefixes.. unfortunate.

    Without customization, only the group code and isactive fields are eligible for filtering.

    So the only supported way would be to use a comma-separated OR list of codes (no wildcards).


  • 15.  RE: XOG out filtering with "starts with" criteria?

    Posted Nov 13, 2012 06:38 AM
    Wow - thanks for the triple-sync help!

    I've managed to extract the groups in two scripts, the bulk of them using one script filtering BETWEEN on the IDs and another filtering OR on the remaining IDs that weren't caught by the first query.

    One final question - there is one group that throws an error when included in the OR filter. This is because the ID is ' v_c&s_ccb'

    XOG doesn't seem to like ampersand as part of an ID (understandably so, no idea why this was used in the ID in the first place but can't be helped now...)

    Any way to XOG this one remaining group out?

    Thanks again, much appreciated

    Alex


  • 16.  RE: XOG out filtering with "starts with" criteria?

    Posted Nov 13, 2012 06:45 AM
    For that one you'll need to escape the ampersand character, like this:
    <?xml version="1.0" encoding="UTF-8"?>
<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
  <Header version="7.5" action="read" objectType="group" externalSource="NIKU"/>

  <Query>
    <Filter name="code" criteria="EQUALS">v_c&amp;s_ccb</Filter>
  </Query>

</NikuDataBus>


  • 17.  RE: XOG out filtering with "starts with" criteria?

    Posted Nov 13, 2012 06:58 AM
    Perfect - thanks again all!

    Cheers,

    Alex


  • 18.  RE: XOG out filtering with "starts with" criteria?

     
    Posted Oct 23, 2012 07:29 PM
    Hi akort,

    Did the responses here help answer your questions? If so please mark the appropriate posts as Accepted Solution.

    Thanks!
    Chris