Custom Aggregation Column Name

Hi Gabriel,

Thanks for your answer and the Loom video - I didn't realise table mirroring was so feature rich.

Yes I definitely have a query filter - I was just pasting a shortened version of my formula :)

My concern with the ability to rename columns afterwards and it being smart, is that for someone else working on the workbook it's not obvious how the table column naming is/was done/mapped. 

Providing column names within the formula would make it clear either using SQL style aliases, or as a seperate argument.

Alternatively I could turn off table headers within SI.QUERY, and then specify them manually, but not the cleanest approach.

I think I originally went with TOTABLE because the output table shrunk if there were no results and so it would shift cells on the right leftwards. My more complicated formula which after testing won't be needed. (Note the IFERROR to handle the shrinking table issue I bumped into. And I found I had to specify every output column too to make it more stable)

=TOTABLE(
    IFERROR(
        SI.QUERY(Connection_Name, "GLDETAIL", TX_FILTER, TX_COLUMNS, TRUE, HSTACK({"Sort"}, {"BOOKID,ITEMID,EMPLOYEEID,LOCATIONID,DEPARTMENTID"})),
        VSTACK({"ITEMID", "EMPLOYEEID", "LOCATIONID", "DEPARTMENTID", "BOOKID", "BASECURR", "SUM(AMOUNT)"})
    ),
    ,
    TRUE,
    $Q$9, $R$9, $S$9, $T$9, $U$9, $V$9, $W$9
)

=SI.QUERY(Connection_Name, "GLDETAIL", TX_FILTER, TX_Columns, TRUE, HSTACK({"Sort"},{"BOOKID,ITEMID,EMPLOYEEID,LOCATIONID,DEPARTMENTID"}), $Q$9, $R$9, $S$9, $T$9, $U$9, $V$9, $W$9)

I forgot to attach the file I built in the video - here you go!

Hi Ben,

The TOTABLE() function is meant to be used with functions that do not support table-mirroring out of the box. With the SI.QUERY function, you can simply use the OutputColumn argument to specify the location of the first column of the table. You are then completely free to rename the column header, and Velixo will be smart enough to remember where the data goes when refreshing. 

Here's a short video demo: https://www.loom.com/share/4737c79e994b4c53b40797c20f560e26

Question: do you have a filter setup on your query? Aggregation happens locally at the moment so the full GLDETAIL will be loaded without any filtering.

P.S. I moved your post to the Sage Intacct area.