Including a sum column from another table based on name

Ask for Help Formulas
1

Hello, I have a table of websites that includes a client name, project name and a hosting fee. In another table I have domains with a client name. Both of these client columns are referenced to a client table.

In a different dashboard-style table I have a couple of widgets that update based on client selection (domains, projects and total domain fees). I’d like to add a new column to one of the widgets that goes and finds the client selected in the first widget in the websites list and returns the total hosting fees for the websites under that client name. I do not know how to reference my other table to go and grab that info.

Any help would be great. I cannot share the data sadly.

2

Hello Evan,
I’m not sure I get the exact structure of your tables, but looks like to can use the lookupRecords function to find all the hosting fees for a specific client. The function returns a list of values that you can then sum:

  • Websites is the name of the table with the hosting fees
  • Client_Name is the name of the column with the client in the Websites table
  • $Client is the name of the column on the dashboard table where you need to show the sum
  • Hosting_Fee is the name of the column with the hosting fees in the Websites table

The function is in a column of the dashboard table and returns a list of fees from the Website table where the client name matches the value of the client in the $Client column of the dashboard table.
The python sum function sums all the elements of the list.

sum(Websites.lookupRecords(Client_Name=$Client).Hosting_Fee)

The dashboard widget should be linked to the Clients table in order to select the client for which the sum is to be shown.

Hope this helps; you may want to share your structure (without the data) for a more pertinent suggestion.

See:

1 Like
3

Thank you @Fabio_Moratti! I think I was relying on the autofill too much and thinking if it didn’t show it then it must not b available to me. I could only see raw data instead of my table which is filtered raw data. My final formula ended up being:

SUM(Interactive_Projects_2022_2.lookupRecords(client=$Client).hosting_fee)