Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 21, 2021 10:31 AM
Hi All,
I hope someone could help me a better way of doing this. I have two tables on a base (Table 1 and Table 2).
In Table 1, data is shown below:
Here is what I am trying to do in Table 2:
I tried using the Pivot Table app but the lack of exporting it to CSV is not ideal for me.
Solved! Go to Solution.
Sep 21, 2021 10:42 AM
Convert your {State / Country}
field into a link to another record
-type field, connect it to Table 2. This will create one record in Table 2 for each unique {State / Country}
value. To reduce repetition, you should add a {Region}
field to Table 2 and make the {Region}
field in Table 1 a lookup
-type field.
From there you can add a count
-type field to Table 2, which will show you the number of records from Table 1 linked to each state/country.
Do a similar process for Region: convert the {Region}
field in Table 2 (not Table 1) in to a link to another record
-type field and connect it to another table (Table 3). Then add a rollup
-type field that uses the SUM(values)
aggregation to add up the count
field from Table 2. This will give you the total of all Table1 records linked to a state/country in that region.
Sep 21, 2021 10:42 AM
Convert your {State / Country}
field into a link to another record
-type field, connect it to Table 2. This will create one record in Table 2 for each unique {State / Country}
value. To reduce repetition, you should add a {Region}
field to Table 2 and make the {Region}
field in Table 1 a lookup
-type field.
From there you can add a count
-type field to Table 2, which will show you the number of records from Table 1 linked to each state/country.
Do a similar process for Region: convert the {Region}
field in Table 2 (not Table 1) in to a link to another record
-type field and connect it to another table (Table 3). Then add a rollup
-type field that uses the SUM(values)
aggregation to add up the count
field from Table 2. This will give you the total of all Table1 records linked to a state/country in that region.
Sep 21, 2021 04:19 PM
Hi Kamille,
This is very helpful! Thank you! - I cant seem to make the rollup file type work though. I must be doing something wrong.
Sep 21, 2021 04:59 PM
Are you getting an error? can you post a screenshot of what you have thusfar?
Sep 22, 2021 05:01 AM
Hi Kamille,
All I get is 0.
Sep 22, 2021 11:47 AM
Is your Count field in Table 2 working properly? The {Region Rollup}
field should be doing SUM(values)
for the Count field, so if the Count fields are showing values greater than 0 this is an issue.
Sep 22, 2021 11:52 AM
I figure it out - thank you for your help!