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

Question

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.

khabran · Accepted Answer

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

Mike Schmitt · Answer

khabran said: 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.

Mike Schmitt · Answer

You'll run the same queries on the same DSE, but you'll pass a completely separate set of columns in each call. AFAIK there's no such limit to the number of columns in a table / DSE (though having over 100 columns sounds like a maintenance nightmare, to be honest). 
 For your final question, I'm not quite sure what you mean. When populating your staging table, you'd query all your information from the original source, sort it and do any post-processing you need there - then all at once you'd write the entire processed list back to the staging table, already in the order you want it to be exported to excel.

Mike Schmitt · Answer

Again you basically create a dummy table containing only the columns you need (they could just all be text, for example), populate it on demand and then clear it back out once your document is generated.

Mike Schmitt · Answer

khabran said: what would be a sound solution so that no collisions exist. 
 sorry i missed this particular followup question until now. there should be zero need to worry about collisions. you just pass the document id of the generated document (which you save into a PV in the first node) into the subsequent node(s) as i mentioned earlier.

Mike Schmitt · Answer

Did you follow my earlier suggestion of running the same excel sheet twice through the generator, setting your starting cell to a position ~50 columns to the right of the previous run? (If the number of columns being inserted will be variable, you would just need to store that number by some means and then use logic to set the new starting column to the matching value). 
 AFAIK appian only limits the number of columns you can insert *at once* to 50, but not necessarily * in total *. If you can show otherwise I'd like to hear the details of the steps you took to verify.

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

Top Replies