Task Reports - Adding Data Columns - What rules can I use?

I have a task report and in order to build a range of filters for the data, I need to add new columns for the report.  The task names are formatted to combine data about the task.

TaskName for CustomerName - CaseNumber - SellingBranch  (eg FF Validate Case File for Jones - FF1234 - Glasgow)

 

I can use rules which split bits out of the task name to include in columns.  For example, I can have a Selling Branch Column which is built from a rule which splits the task name by separator '-' and returns the final element from the array of split values.

But I need another column which takes the SellingBranch and looks up in a MySQL table to get the corresponding region - so any of Glasgow, Perth, Dundee return the region 'Scotland' and any of Brighton, Canterbury or Hastings return the region 'South'

I have an expression rule which takes in the SellingBranch name and returns a Region Name, which it gets by first using a query rule to look up the table SELLING_BRANCH to get regionId and then using a second query rule to look up table REGION to get the regionName.

When I try to use this rule in the column definition of my Task Report, it gives a blank column.  I can't find any documentation to say whether this ought to work or is a restriction (Appian 17.1)

  Discussion posts and replies are publicly visible

Parents
  • It should not be restricted with 17.1. You likely need to use the fn!apply() or some similar looping function to apply the query to each ID in your column. This may have performance implications but it sounds like you may have taken that into account before proceeding.

    The documentation page for fn!apply() is: docs.appian.com/.../fnc_looping_apply.html
  • I have just tried fn!apply() and it makes no difference.

    Other columns, not accessing data from tables work as one would expect, eg =rule!HFA_extractBranchNameFromTask(tp!display).

    The code for the what I am trying to do is:

    Column 'Region' is defined as: rule!HFA_findRegionFromTaskName(tp!display)

     

    where rule!HFA_findRegionForTaskName is defined as:

    =rule!HFA_getSellingBranchRegion(

    rule!HFA_findSellingBranchByName(    

    rule!HFA_extractBranchNameFromTask(ri!taskName)

    ).FK_regionId

    ).regionName

     

    where HFA_getSellingBranchReqion and HFA_findSellingBranch are query rules to extract data from MySQL tables

  • 0
    A Score Level 3
    in reply to paule
    To try this out I would create a testing/POC rule or interface to attempt to run the code you've written above to see if all the queries return the data you're expecting. Apologies if you've already done so.

    To me it just seems like maybe one of the queries isn't returning the data or isn't indexing as a list of data, which is why I recommended attempting using fn!apply() for one (or both?) of the queries. If you attempt these query rules in a test rule/interface you might be able to figure out what type(s) are being returned and that might help to figure out if a list is returned.
Reply
  • 0
    A Score Level 3
    in reply to paule
    To try this out I would create a testing/POC rule or interface to attempt to run the code you've written above to see if all the queries return the data you're expecting. Apologies if you've already done so.

    To me it just seems like maybe one of the queries isn't returning the data or isn't indexing as a list of data, which is why I recommended attempting using fn!apply() for one (or both?) of the queries. If you attempt these query rules in a test rule/interface you might be able to figure out what type(s) are being returned and that might help to figure out if a list is returned.
Children
No Data