Invoice Type table, multiple records for same "destination", get total

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
I have a table that creates batch files for bank transfers. This works well, except for when a destination account is used more than once for that batch file. What I'm looking to do is if a record is part of that batch file (two tables, one for line items, then given relationship to the batch file table) I want to total the amounts for each destination account, and then have the batch file only have one record per destination account.
Photo of Ryan Stanford

Ryan Stanford

  • 512 Points 500 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Is there already a relationship between the Batch Table and the Line Item Table? If so you can create summary fields on your Batch Table that would sum the Line Items. You would then use the Batch Table for your transfer file.

Summary fields can be generated by opening the table-to-table relationship from the table settings and clicking on the "Add Summary Field" on the left side. Then you can choose which fields you want to sum.
Photo of Ryan Stanford

Ryan Stanford

  • 512 Points 500 badge 2x thumb
There is, the complication I have is that I almost need a dual relationship... I have the individual line items in one table, and set a relationship to the batch table related to the batch information...

Issue is that if there are multiple line items pertaining to the same person/account, then they are multiple people on the batch.

I have been able to make a summary report with what I need, the total of payment for each person/account per Batch record, as I don't want the sum of ALL for one batch record, or ALL for one person/account.

However, Looks like summary report fields only exist in that report based on my research.