Group query results in microflow

Our story in short: We need a microflow that creates invoices in the database. An invoice consists of one or more invoice lines, each with a reference to a certain product. There is an entity with the transactions that need to be invoiced. Each transaction has a reference to a product and a customer. The amount of an invoice line is the total of transaction all amounts with the same product and customer. What do you suggest for the easiest way to group the results for such a query, that also performs with large numbers of transactions?
2 answers

Based on Mike's answer you can loop through the transaction list which is sorted by customer :

  • Retrieve all transactions that need to be invoiced, sort on customer
  • Create a empty list called CustomerTransactionList
  • Loop through this list and add each transaction to the CustomerTransactionList
  • if the customer changes!
  • Create/Update the customer
  • Create an invoice
  • Loop through the CustomerTransactionList and create invoicelines
  • empty the CustomerTransactionList and continue the loop through the CustomerTransactionList

Group by and distinct are 2 capabilities I have missed frequently in Mendix. Here is a way you can accomplish what I think you need to do:

  • Retrieve all transactions that need to be invoiced, sort on customer
  • Loop through this list and create a list of distinct customers
  • Loop through the list of customers
  • Create an invoice for each customer
  • use a List Operation action to Filter the transaction list to locate all transactions for a customer
  • Create a nested loop to loop through all transactions for the current customer
  • Inside the nested loop, create an invoice line item on each loop

You'll need to test out performance of this with your volumes. Aside from the initial retrieve, all other retrieve/list operations happen in memory vs. requiring database activity.

Hope that is helpful.