Skip to main content
5/5

wherecontains(): match a payment file to open invoices

Hard

Each morning the bank sends a payment file: one line per payment, holding the invoice number that was paid.

Match it against the open invoices, which are records with a number and an amount. A line the bank could not read arrives as null.

Return a record with three fields

  • stillOpen: the invoices that were not paid, as records, in their original order
  • unknown: payment numbers that match no open invoice, in file order, each time they appear
  • unreadable: how many lines were null

Watch out

  • An invoice paid twice is simply paid.
  • wherecontains() returns positions in the list being searched, so the argument order decides which list those positions belong to.
  • Unlike a comparison, it can also find nulls.

Tip: the invoice numbers are needed more than once. a!localVariables() lets you name them; repeating the expression works too.

Inputs

  • ri!invoices: List of Map
  • ri!paidNumbers: List of Text

ri! Rule Inputs

ri!invoicesList of Map
{
  a!map(number: "INV-1", amount: 100),
  a!map(number: "INV-2", amount: 250),
  a!map(number: "INV-3", amount: 75)
}
ri!paidNumbersList of Text
{
  "INV-2",
  "INV-9",
  null,
  "INV-2"
}

Example 1: A typical morning

Input: ri!invoices
{
  a!map(number: "INV-1", amount: 100),
  a!map(number: "INV-2", amount: 250),
  a!map(number: "INV-3", amount: 75)
}
Input: ri!paidNumbers
{
  "INV-2",
  "INV-9",
  null,
  "INV-2"
}
Output:
a!map(stillOpen: {a!map(number: "INV-1", amount: 100), a!map(number: "INV-3", amount: 75)}, unknown: {"INV-9"}, unreadable: 1)

Example 2: Everything paid, in another order

Input: ri!invoices
{
  a!map(number: "INV-1", amount: 100),
  a!map(number: "INV-2", amount: 250),
  a!map(number: "INV-3", amount: 75)
}
Input: ri!paidNumbers
{
  "INV-3",
  "INV-1",
  "INV-2"
}
Output:
a!map(stillOpen: {}, unknown: {}, unreadable: 0)

Example 3: Only unreadable lines

Input: ri!invoices
{
  a!map(number: "INV-1", amount: 100),
  a!map(number: "INV-2", amount: 250),
  a!map(number: "INV-3", amount: 75)
}
Input: ri!paidNumbers
{
  null,
  null
}
Output:
a!map(stillOpen: {a!map(number: "INV-1", amount: 100), a!map(number: "INV-2", amount: 250), a!map(number: "INV-3", amount: 75)}, unknown: {}, unreadable: 2)

Example 4: An unknown number twice

Input: ri!invoices
{
  a!map(number: "INV-1", amount: 100),
  a!map(number: "INV-2", amount: 250),
  a!map(number: "INV-3", amount: 75)
}
Input: ri!paidNumbers
{
  "INV-7",
  "INV-1",
  "INV-7"
}
Output:
a!map(stillOpen: {a!map(number: "INV-2", amount: 250), a!map(number: "INV-3", amount: 75)}, unknown: {"INV-7", "INV-7"}, unreadable: 0)

Example 5: An empty file

Input: ri!invoices
{
  a!map(number: "INV-1", amount: 100),
  a!map(number: "INV-2", amount: 250),
  a!map(number: "INV-3", amount: 75)
}
Input: ri!paidNumbers
{}
Output:
a!map(stillOpen: {a!map(number: "INV-1", amount: 100), a!map(number: "INV-2", amount: 250), a!map(number: "INV-3", amount: 75)}, unknown: {}, unreadable: 0)

Appian Expression Tips

  • • Use ri!variableName for rule inputs
  • • Use a!localVariables(local!x: …, body) for locals
  • • Use a!forEach(items: …, expression: fv!item) to iterate
  • • Use where() / reject(fn!isnull, list) to filter