Unique Values for a CRM

Tareq_Sojol · May 18, 2022, 7:46am

Hi, I am trying to develop a customized CRM tool. For input data I have all the order activity data in a table. I want to have a list of unique client names and calculate some data such as earned revenue for the client, TAT median number, is the orders are ongoing or not based on input data.

I know that I can summarize the table by client name that will give me unique list, but I am not sure if I can do the calculation on the summary table. I have made a demo file public, Can you guys help?
Doc link: http://139.180.223.0:8484/o/docs/pwQ2WcKCfGKp/Followup-Sample

Is there anyway I can add the client name as a reference collum? I just put the unique client names from a simple unique formula in google sheet and then pasted it to a new table and then made the input table client field as a reference field so I can calculate. Is there any other solution?

natalie-grist · May 18, 2022, 2:12pm

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!

Tareq_Sojol · May 19, 2022, 9:20am

Thank you very much Natalie. I did what you told. It is working now. However, my question is, if I update the order data, will the client list updated? Now the client list is detached, I don’t think it will update. is it true? What can I do if I want the client list to be update automatically.

Second point; Can you please help me with the following formulas?

I have a checkbox collum named “OC?” in the order data table. i want the check box to be marked if the OC date collum is not blank. Unmarked if the OC date is blank.
I want to calculate the total earned revenue for the clients in the client list table. It means, I want to sum the revenue for the clients where the Order Progress is “Order Complete” Only.
In the client list, there is a column names “Order ongoing”. I want to return two values here in this column. Order Ongoing and Order done. If any of the order progress of this client is not Order complete, then I want the column to show Order ongoing, otherwise, order done.
TAT Median, I want to calculate The Median of TAT for all the orders which are completed.
lastly, How can I return max order date in the Lates Order Date column?

My apologies for the trouble. Many many Thanks.

Tareq_Sojol · May 19, 2022, 11:36am

okay, I was successful in calculating Median TAT, Earned Revenue, and latest Order date. Please help me with OC date blank formula and Order ongoing formula.

natalie-grist · May 19, 2022, 8:58pm

Hi Tareq!

Here is the formula for the OC? Column:

IF(($OC_date== None),0,1)

Just change " " to None in the formula you had in the example document.

For the Order Ongoing column:

all_statuses = Order_Data.lookupRecords(Client_Name=$id).Order_Progress
if set(all_statuses) == {'Order Complete'}:
  return "Order Complete"
else:
  return "Order Ongoing"

Tareq_Sojol · May 20, 2022, 11:47am

Thanks Natalie!

Another question; If you see the doc, I am trying to calculate Gross Profit for each order. But it shows error where the revenue or cost is blank. Is there any formula where it would make the value 0 if it is blank?

The way I understand is, now I have to update the order data manually and add the new clients in the client list table. right? Is there any way I can make this update automatically?

natalie-grist · May 20, 2022, 2:22pm

Here’s the formula:

if $Cost and $Revenue:
  return IF($Order_Progress == "Order Complete",($Revenue-$Cost),"N/A")

With the if statement, the formula will run only if values exist in the Cost and Revenue columns. If a value is None for either column, nothing will be returned.

I noticed in ‘Total Gross Profit’ on the Client List table, you’re getting a TypeError because of the Nonetype values. You can add this formula to get rid of those errors:

all_gross = Order_Data.lookupRecords(Client_Name=$id, Order_Progress="Order Complete").Gross_Profit
result = []
for num in all_gross:
  if num == None:
    continue
  result.append(num)
sum(result)

You could use an integrator service like Zapier or Pabbly Connect to automate things, depending on where the data is coming from. Let’s say you’re sending data via integration to the Order Data table and the client isn’t in the Client List yet, the data would still be imported but the name in the Client Name column would be read because they don’t exist in the Client List table so the reference is not being made.

One way to make it easier to update manually would be to create a dashboard where you can add everything in a single view. In the screenshot below, I set up a view based on the Client selected in the Client List. Andrew is selected so I see only Order Data for Andrew in the Order Data table. Then, I see his client details in the Client List card.

Tareq_Sojol · May 23, 2022, 5:54pm

Thanks Natalie!

Another question if you don’t mind. If you see the “Team Breakdown” table, I am trying to build a dashboard where If I click on a team the client type summary table will updated and then if I click on the client type summary table the client will be updated.

I have done the select by, but for some reason the team wise select by is not working. any idea why is that and whats the solution here?

natalie-grist · May 23, 2022, 7:43pm

Hi Tareq,

For the second summary table, you’ll want to group by Client Type and Team. Still select by CLIENT_LIST [by team]. The new summary table will have a Team column, you can go ahead and hide this.

Delete the Client List table and re-add it to the page. Select by CLIENT_LIST [by Client Type, Team].

Now, it should filter the way you want!

Tareq_Sojol · May 26, 2022, 1:56pm

Thank you very much Natalie. You guys have been super helpful!

I have another question. If you check the client list table, there is a collum name Team. I want to return the team name who handled the latest order of the client.

After studying the example templates, I came up with the following formula:
ordate = Order_Data.lookupRecords(Client_Name=$id).Order_Date
return Order_Data.lookupOne(Order_Date=max(ordate)).Team if ordate else None

it worked for the most part. but in my main file, some rows are giving the wrong results. I don’t know why. Can you please check the formula and let me know if there is anything wrong with it?
Doc link: http://139.180.223.0:8484/o/docs/pwQ2WcKCfGKp/Followup-Sample

natalie-grist · May 26, 2022, 5:29pm

It looks like you forgot the Client_Name = $id condition in the second statement. The lookupOne formula should be:

Order_Data.lookupOne(Client_Name=$id, Order_Date=max(ordate)).Team