Skip to main content
khabranp0001
khabranp0001
March 9, 2022
Solved

Can an Expression rule be used with a query entity to process the data before output?

  • March 9, 2022
  • 23 replies
  • 0 views

I am using a process model to export data from the database to a spreadsheet. I am not using the export in the record type, just to be clear. When selecting the fields using the a!querySelection -> a!queryColumn, this immediately pulls the data and exports to a new spreadsheet, with no way of adding code to process it. If I have a CDT that has, for example, twenty fields, and one of those fields contains an employee ID. How can I, or is it even possible, to use an expression rule that takes an ID parameter, and outputs the actual employee name? There doesn't seem to be a path forward on this. I've looked through the process model, all the different query types, etc., but no way to pull the data, then process that data, before outputting to the spreadsheet.

Just to make sure this is clear, I already have the expression rule to process the data, I just have nowhere to do it and if there is a way to do it, I have not found it.

Any help will be greatly appreciated. 

Thanks.

Best answer by khabranp0001

THERE IS NO ANSWER TO MY QUESTION, AS APPIAN DOES NOT ALLOW MORE THAN 50 COLUMNS TO A WORKSHEET IN A WORKBOOK. VERY DISAPPOINTING.

23 replies

mikes0011
mikes0011
Brainy
March 9, 2022
export data from the database to a spreadsheet

Which node are you using to try this?  Any answer we provide here would depend on this.

Currently the most common / supported method would be the "Export DSE to Excel" node, and in that case the answer to your post's subject is "no" - the only way to do what you're asking is to get the data the way you want it (and sorted the way you want it) in a DB table or view and export it directly.

Another option is the "Export CDT to Excel" node in the Excel Tools plug-in, which despite being deprecated technically, still provides capabilities that have not been sufficiently replaced by anything else (ootb or plug-in based).  With this node, you'd be able to iterate over a list-type CDT and do any sort of processing you want in an expression rule, saving the output back into a CDT, then sending that to the excel template.

stewart.burchell
stewart.burchell
March 10, 2022

Another option would be to work backwards form the desired output content, create a table that maps to that output content, and have your process load that table, converting Ids into required values (e.g. employeeId to employeeName) and then dump the data from your new table straight to Excel. You could consider this new table to be a "staging" table, loaded on demand, extracted to Excel and then flushed clear as the last step in the process.

    khabranp0001
    khabranp0001Author
    March 11, 2022

    Stewart, would you be able to give me a little more guidance regarding your solution?

    J
    jayashreer0001
    March 10, 2022

    you could build a View (join between the two tables) , use Query on the View and then export the result to a Excel

    khabranp0001
    khabranp0001Author
    March 11, 2022

    This wouldn't work because I don't want to show a hundred columns in the table. There are only some columns that should be viewable in the table, the rest, depending on the user, will only see all of them in the spreadsheet.

    J
    jayashreer0001
    March 11, 2022

    When you query the view , you can use a a!querySelection(columns) to return only the specific columns in the view/table.

    khabranp0001
    khabranp0001AuthorAnswer
    March 14, 2022

    THERE IS NO ANSWER TO MY QUESTION, AS APPIAN DOES NOT ALLOW MORE THAN 50 COLUMNS TO A WORKSHEET IN A WORKBOOK. VERY DISAPPOINTING.

      stefanhelzle0001
      stefanhelzle0001
      March 14, 2022

      If a CSV file would work for you, you can create that in a process and do whatever you want.

      Terms & ConditionsAccessibility statement