Is there a way, similar to the “GROUPBY” code within SQL or a Pivot Table in Excel, to summarize a table within silverfin?
POTENTIAL USE CASE:
- when uploading data from excel through a fori loop, rather than summarize account information outside the system, one could group lines together based on a primary key field (unique identifier) through code and use this code for a variety of different potential situations such as uploading customer invoice data.
My idea would be using a case tag and saying when field 1 == X, $1, when field1==Y, $2 …
Only I do not want to add specifics to the code which would only work if I knew the potential primary keys, and wouldn’t work as well with something like customer IDs if I had 100s of customers. The GROUPBY SQL query is much more along the lines of what I had in mind. Please advise!
That GROUPBY function you’re looking for, is actually a part of our “Automatic reconciliation” in an account template, where the import of details comes from a ledger:
You’ll see no option there to import a field
value: we automatically group on one or more source fields, and take the value of each transaction line into the object that is named as
value (if it would be named anything else, that function would be lost as you would need to link the value yourself ).
In short, the grouping only works in an import from a ledger (so only possible in an account template, where ledgers are present) and if all details are grouped/imported into the drop
details where the object that groups the value, has to be named
For a reconciliation template this is not made possible; we presume most users would want to import how the data is in XLS… Could you give me more use cases (if possible), where the automatic reconciliation would benefit from this function to group?
PS it’s possible in Liquid code, just like you said.