Add rows to this table using values from another tables

[Uribe]

Hello experts!

I have three source tables and one destination table:

DESTINATION TABLE:

Date	Name	Future Task	Price Checked	Task Assigned	Task Discarded	Key
03/30/24	Peter	2	1	2	1	123qwe2
03/29/24	Mary	1	1		1	654asd

SOURCE TABLES:

Future Tasks:

Date	Name	Future Task	Key
03/30/24	Peter	Buy	122wer
03/30/24	Peter	Sell	poi123
03/29/24	Mary	Buy	0978we

Prices:

Date	Name	Price	Key
03/30/24	Peter	50.25	qwe987
03/29/24	Mary	30.21	klmlkm654

Tasks Assigned and Discarded:

Date	Name	Task Assigned or Discarded	Key
03/30/24	Peter	Assigned	09q8we
03/30/24	Peter	Assigned	90lkasd
03/30/24	Peter	Discarded	cbn3556
03/29/24	Mary	Discarded	qweqw123
03/29/24	Peter	Discarded	qweqw124

I need the destination table to be filled everyday automatically with counts from the source tables per person per date.

I tried with actions on the source tables, but I it will add several rows for the same person on the destination table.

I thought about setting app formulas on the destination table, but can't figure out how the app will add one row per person per date with the counts I need.

Hope you could point me in the right direction.

Thanks in advance!

[AleksiAlkio]

One way is to trigger the scheduled Bot/Webhook from the Users table (I assume you have it already). When you use Start: & End with the webhook, it creates a loop with the SELECT() and a correct amount of rows or you can filter the list if only part of your users have worked at that day.

With the AppSheet API & Webhook you need Core subscription.

[Uribe]

I have a core subscription. 
what should be the formulas you suggest?

[AleksiAlkio]

It needs to be something like..

{
"Action": "Add",
"Properties": {
"Locale": "en-US",
"Location": "60.494915, 22.090139",
"Timezone": "E. Europe Standard Time"
},
"Rows": [
<<Start:SELECT(Users[User],TRUE)>>
{
"ID": "<<UNIQUEID()>>",
"User": "<<[User]>>",
"Date": "<<TODAY()>>",
"Future tasks": "<<COUNT(SELECT(Future tasks[ID],AND([Date]=TODAY(),[User]=[_THISROW-1].[User])))>>",
"Price checked": "<<COUNT(SELECT(Prices[ID],AND([Date]=TODAY(),[User]=[_THISROW-1].[User])))>>",
"Tasks assigned": "<<COUNT(SELECT(Tasks[ID],AND([Date]=TODAY(),[User]=[_THISROW-1].[User],[Task status]="Assigned")))>>",
"Tasks discarded": "<<COUNT(SELECT(Tasks[ID],AND([Date]=TODAY(),[User]=[_THISROW-1].[User],[Task status]="Discarded")))>>",
}
<<End>>
]
}

[Uribe]

Hi AleksiAlkio, hope you're doing great

Table name is the table I want to obtain the data from? or the destination?

Target table "Table Name in the App ID to send the request to", what table should I set here?

[AleksiAlkio]

Table name = Users and Target table = Your destination table

[Uribe]

I need to make 3 bots because I have 3 different source tables?

[AleksiAlkio]

No, one Bot is enough.

[Go]

Wouldn't it be better to add a virtual column to DESTINATION TABLE to count the number of items in each table?

App formula:
COUNT(SELECT(Future Tasks[Name],AND([Date]=[_THISROW].[Date],[Name]=[_THISROW].[Name])))

[Uribe]

I need the values to be written on the destination table so I keep statistics. 

The thing I can't figure out is how the app will add one row per name/user without the need for me to add it manually.

[Go]

sorry. I misunderstood.

How about a combination of the following data structure and a bot that adds events to the Future Tasks table?

1. DESTINATION TABLE

Future Task:(virtual column)

COUNT(SELECT(Future Tasks[Name],AND([Date]=[_THISROW].[Date],[Name]=[_THISROW].[Name])))

Price Cheked:(virtual column)

COUNT(SELECT(Prices[Name],AND([Date]=[_THISROW].[Date],[Name]=[_THISROW].[Name])))

Task Assigned:(virtual column)

COUNT(SELECT(Tasks Assigned and Discarded[Name],AND([Date]=[_THISROW].[Date],[Name]=[_THISROW].[Name], [Task Assigned or Descarded]="Assigned")))

Task Discarded:(virtual column)

COUNT(SELECT(Tasks Assigned and Discarded[Name],AND([Date]=[_THISROW].[Date],[Name]=[_THISROW].[Name], [Task Assigned or Descarded]="Discarded")))

2. Bots

EVENT:

Condition:

COUNT(SELECT(DESTINATION TABLE[Date],AND([Date]=[_THISROW].[Date],[Name]=[_THISROW].[Name])))=0

PROCESS:

Add row to this table:

DESTINATION TABLE

With these values:

Data: [_THISROW].[Date]

Name: [_THISROW].[Name]

[Uribe]

The final result will be a row per each different name and date?

 Let’s say: 5 names = 5 rows? Per date

[Go]

No, it's not.

If the same record does not already exist in the additional condition SELECT function, it will be processed

---------------------

Condition:

COUNT(SELECT(DESTINATION TABLE[Date],AND([Date]=[_THISROW].[Date],[Name]=[_THISROW].[Name])))=0