Solved: Autocompute based on a related reference - Page 2

TeneoBI · 09-12-2022 10:28 AM

Hi All,

I have two tables [JobFlow] and [ItemDetail]. The Job Flow sheet holds the basic information about a customer job with us, and the Item Detail sheet holds all the items that have been left as part of that job. For example, a customer will be given their ticket number, say 7090. This is then used in the Item Detail sheet with each of their items (say 1, 2, 3 and 4 for example). Later I will use a simple virtual column to reference each item as 7090.1, 7090.2, 7090.3, 7090.4 etc.

For now though, how to I get AppSheet to autocompute which item number is coming from this customer?

It should be straight forward: count the number of items that use 7090 as the [Ticket Number], and then add 1. But I don't know how to make AppSheet do the first calculation. Any help greatly appreciated.

As an example:

- If customer (7090) does not yet have any items linked to their Ticket Number... So new item number expression should be 0+1 = 1

- If customer (7090) has one item number linked to their Ticket Number... So new item number expression should be 1+1 = 2. etc...

Steve

@TeneoBI wrote:
I tried using this formula: count(ItemDetail[Ticket Number])+1

Try instead:

count(
  select(
    ItemDetail[Ticket Number],
    ([_ThisRow].[customer1] = [customer2])
  )
)
+ 1

Replace customer1 with the name of the column in the JobFlow table that contains the customer number, and replace customer2 with the name of the column in the ItemDetail table that contains the customer number.

View solution in original post