Hi Tareq!
The best way to create a list of unique values is to use a summary table - but you can detach the table so it ends up like a ‘normal’ table.
Create the summary table, grouped by ‘Client Name’. Under the Table Configuration Menu, select ‘Data’ then ‘Detach’.
Be sure the Client Name Reference Column is showing the actual Name of the client - mine showed Row ID after detaching the table. Once it shows the Name, change the column type to ‘Text’.
Update your Client Name column in the Order Data table to reference your new table.
To pull in data from the Order Data table for each client, you’ll have to use a reverse lookup.
For example, to find Total Revenue, you’ll use the following formula:
SUM(Order_Data.lookupRecords(Client_Name=$id).Revenue)
We want to lookup records in the Order Data table where the record in the Client Name column points the record ID in this table. Because Client Name is a reference column that points to a record in the new table, we use a reverse lookup when pulling data the opposite direction. We then want to pull the Revenue for that record.
Last, we sum all values found in the lookup records formula.
You can use a similar reverse lookup to pull any other values into this new table from your Order Data.
Let me know if you have any other questions!