getting unique values from detail table

[ingalsaac76]

Hello, I need some help to figure out this, I hope you can help me 😊

I have 4 tables:
VENDORS: id: uniqueid(), name: string

PAID_PERIODS: id: uniqueid(), description: string 

SALES
id: uniqueid()
date: date
vendor: ref from vendors
authorized: Y/N
period: ref from periods
total: price

VENDOR_BONUSES
period: ref from periods
vendor: ref from vendors
createdAt: date
Total: price
Paid: Y/N

every sale is assigned to a vendor, every vendor would have 0 or many sales, when the sale is authorized I have to assing the period when the sale will be paid to the vendor, I want to create a single row for each vendor in VENDOR_BONUSES with the total of sales authorized, so I can print a receipt.

I guess I have to run some actions:
1. add a new row to another table using values from this row (sales to vendor_bonuses)
2. execute an action on a set of rows (sales to vendor_bonuses) with referenced Table (I think here is my problem)

but I need this row be unique for each vendor, I hope it's clear, thank you in advance!

[TeeSee1]

You can try this expression as [Total]'s app formula.

SUM(
 SELECT(
  SALES[total],
  AND(
   [period] = [_THISROW].[period],
   [vendor] = [_THISROW].[vendor],
   [authorized]
  )
 )
)

 Having [Total] as a regular column requires that you edit the row to force it to re-calculate.

A vc may work better depending on your operational procedure. 

EDITED: I may have misunderstood you. Maybe you just want to know how to create rows in VENDOR_BONUSES?

A similar Q&A here. https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Prepopulate-table-depending-on-2-tables/m-p/430...

You might find other Q&As if you search for it.

[ingalsaac76]

Hello @TeeSee1  thank you for your reply, your example above was almost what I nedeed, but you get your list of uniques employees directly from the employees table, using your app my question is, how would you get a list of unique employees using the time_sheets table? and insert these employees  into the table below

vendor_bonuses
 id_employee: unique
total_hours: vc hours worked
bonus_amount: vc (total_hours) * (employee_rate)

using SQL i'm trying to make a
"select employee, count(hours) from time_sheets group by employee"

and use this recordset to insert them into another table,
I hope you can help me, and thanks again for your time!

[TeeSee1 · Accepted solution]

You set the condition of the action to add a new row to another table (=vendor_bonuses) using data from this row (=sales)

ISBLANK(
 FILTER(
  "vendor_bonuses",
  AND(
   [period] = [_THISROW].[period],
   [vendor] + [_THISROW].[vendor]
  )
 )
)

 to ensure unique records having a combination of [vendor] & [period] .

[ingalsaac76]

Thank you for your help, I have another problem but I will create another topic because the beahavoir condition let the action create duplicates