Autocompute based on a related reference

[TeneoBI]

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]

Serial Numbers, If You Must - Google Cloud Community

[TeneoBI]

Hi Steve,

Thanks for this. I used it last time to work out the new key numbers for the first table (JobFlow[Ticket Number]). 

This problem, however, is more complicated, as I just need to count how many ticket numbers in the Item Detail table, match the currently displayed customer (ticket number). And I can't quite get my head around how to do that. 

[Steve]

Please post a screenshot of the columns of the ItemDetail table from Data >> Columns in the app editor.

---

@TeneoBI wrote:

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.

---

None of this makes sense. "Items that have been left'? How are ticket numbers "given"? How is a ticket number "used in the Item Detail sheet with each of their items"? Lots and lots of missing details.

[TeneoBI]

Apologies. Please allow me to explain.

Here are two customers (7090 and 7091). Their details are stored in the table JobFlow. 

Customer 7090 comes to us and gives us an item to repair. He currently already has two items with us. So, when we add his new item to the related table ItemDetail, the Item Number should be 3 because there are already rows listed in that table for customer 7090 item 1 and 7090 item 2.

Customer 7091 comes to us with his very first item for us to repair. Since he has no previous items, there are no records in the ItemDetail table, and so, his [Item Number] in this table will be 1.

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

BUT, it doesn't work, because it just counts all the customers, and not just those records that relate to the specific customer I am working with.

[TeneoBI]

I used 7090 and 7091 to simplify the numbers, as our actual customer number is based on the date time. But you get the idea.

[Steve · Accepted solution]

---

@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.