Need to be able to calculate the total of column data in order to be used in reporting metrics

I have this following query which that kind of gives me the result I want (pictured below) but how do I mold the expression so that I can use the result to sum the balances?

a!queryRecordType(
  recordType: 'recordType!{6e4edeaf-030d-4fc2-917a-ab20f1027418}DC Loans',
  fields: {
    'recordType!{6e4edeaf-030d-4fc2-917a-ab20f1027418}DC Loans.fields.{fc4aaffb-28af-4b3f-bd19-a59e7625372b}principalBalance',
  },
  pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 100,
    sort: 
      a!sortInfo(
        field: 'recordType!{6e4edeaf-030d-4fc2-917a-ab20f1027418}DC Loans.fields.{fc4aaffb-28af-4b3f-bd19-a59e7625372b}principalBalance',
        ascending: true
      )
      )
      ).data

docs.appian.com/.../Query_Recipes.html

docs.appian.com/.../Query_Recipes.html

I know, I am sorry, my brain is fried and I am extremely frustrated that I can't figure this out. I appreciate everyone's patience.

Pressure is not your friend when learning something new.

Just use the query editor to create that query.

docs.appian.com/.../using-the-query-editor.html

I have been trying to come up with the correct query for hours now and cannot get past getting the list of balances. None of the recipes on that line you provided seem to get me where I need to be.

I have tried so many different combinations of queries my head is spinning. If anyone has an idea of what the query and sum function should be to pull all of the data from the Principle Balance filed in a Loans table so that it can then be summed, please post it.

cls5469 said:

this following query which that kind of gives me the result I want

1) store the result of that query in a local variable

2) call sum() on that result (or the relevant field)

First you need to know how to access the field in RecordType data post querying.  Kinda-stupidly, this requires referencing the entire recordtype and then the relevant fieldname.  For the recordtype here in my example I have a field name called "dataLength". (Querying just a page of 10 only for simplicity's sake).

So let's display the full value first:

Traditional queries would let us just add ".dataLength" after that to get the property alone as a list.  Let's try:

(Womp, womp...)

So how do we access it?  Following the error message, we add the record type property reference in square brackets:

Voila!

Then for the cherry on top, since now we just have an array of integers, we can pass that quite cleanly into sum().

Though I often prefer to abstract the individual steps into their own local variables, which adds a bit more flexibility...

And as with most things, when crafting expressions it's best to start with something small and working, and then iterate it step-by-step.

a!localVariables(
  local!pagingInfo: a!pagingInfo(startIndex: 1, batchSize: 20),
  local!balanceSum: a!refreshVariable(
    value: a!queryRecordType(
      recordType: 'recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan',
      fields: a!aggregationFields(
        groupings: a!grouping(
          field: 'recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan.fields.{776544e7-09aa-4079-b038-f3e274675ab5}id',
          alias: "id"
        ),
        measures: a!measure(
          field: 'recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan.fields.{02381417-037d-4868-bb86-0d812cc564f8}principalBalance',
          alias: "principalBalanceSum",
          function: "SUM"
        )
      ),
      pagingInfo: a!pagingInfo(
        startIndex: local!pagingInfo.startIndex,
        batchSize: local!pagingInfo.batchSize
      )
    ).data,
    refreshOnVarChange: local!pagingInfo
  ),
  {
    a!gridField(
      data:  a!recordData(
        recordType: 'recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan'
      ),
      columns: {
        a!gridColumn(
          label: "Loan",
          value: fv!row['recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan.fields.{1a71e16c-85b9-4882-8ab0-4af845f27271}loanName']
        ),
        a!gridColumn(
          label: "Balance",
          value: fv!row['recordType!{c98782ad-3fca-46e0-bba4-c6a8e2c71a2c}T Loan.fields.{02381417-037d-4868-bb86-0d812cc564f8}principalBalance']
        )
      },
      pageSize: 20,
      pagingSaveInto: { a!save(local!pagingInfo, fv!pagingInfo) },
      showSearchBox: false,
      showRefreshButton: false
    ),
    a!richTextDisplayField(
      value: {
        a!richTextItem(text: "Total Balance: ", style: "STRONG"),
        a!richTextItem(
          text: {
            a!currency(
              value: sum(local!balanceSum.principalBalanceSum),
              isoCode: "USD"
            )
          }
        )
      },
      align: "RIGHT"
    )
  }
)

Not sure if this is what you are trying to do. This example works with paging but doesn't support searching/sorting etc but it gives you an idea.

If you wanted filters and such, you'll have to build them out yourself, you can't use the ones provided by recordData if you want sums and totals.

I dunno, it sounded to me a bit more like a matter of wanting to be able to take a sum of a column but not being able to figure out how to access the data property in the returned RecordType query, like I walked him through here.  But the detail provided was a little unclear, so who knows.