Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Simple Count from Table 1

Solved
Jump to Solution
2264 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Eddy_Maaya
6 - Interface Innovator
6 - Interface Innovator

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:

image

Here is what I am trying to do in Table 2:

  1. Count all similar State Country entries from table 1
  2. Count all EMEA, APAC and AMERICAS from table 1

I tried using the Pivot Table app but the lack of exporting it to CSV is not ideal for me.

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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.

See Solution in Thread

6 Replies 6
Kamille_Parks
16 - Uranus
16 - Uranus

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.

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.

Are you getting an error? can you post a screenshot of what you have thusfar?

Hi Kamille,

image

All I get is 0.

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.

I figure it out - thank you for your help!