Skip to main content

I have two data tables;

  • Table 1 has records with "recommended zip codes" these zip codes are in a single delimited field
  • Table 2 has a data table with zip codes and corresponding populations

Ultimately I just want to find the total population for all zip codes listed in the single field (even within an interface)

I can see this being solved in two different ways:

  1. a formula to recognize each separate value stored in the same field
  2. some way to separate all the separate zip code values into distinct columns within the same record

Any ideas on how to solve for either of these?

 

In this case, by formatting the lists of zip codes into a lists that can be pasted to the "Link to other table" field, and update to Link with Automation and then summed with Rollup Sum(values).

The steps in brief are as follows,

1. Add a Link to other table field
2. Add a formula field and enter either formula

For space-delimited list

SUBSTITUTE({List}, " ", ", ")



For line break delimited list

SUBSTITUTE({List}, "\n", ", ")

3. Setup Automation to update Link to other table when List is updated.
4. Add Rollup field and Sum(values) expression referring to Link field

Ideally, however, it would like to be able to pre-calculate this in the sync source table.

In this case, by formatting the lists of zip codes into a lists that can be pasted to the "Link to other table" field, and update to Link with Automation and then summed with Rollup Sum(values).

The steps in brief are as follows,

1. Add a Link to other table field
2. Add a formula field and enter either formula

For space-delimited list

SUBSTITUTE({List}, " ", ", ")



For line break delimited list

SUBSTITUTE({List}, "\n", ", ")

3. Setup Automation to update Link to other table when List is updated.
4. Add Rollup field and Sum(values) expression referring to Link field

Ideally, however, it would like to be able to pre-calculate this in the sync source table.


Hi,
would like to add, SUBSTITUTE might cause ERROR when applied to the lookup field value, so you need to use CONCATENATE({List}) instead of {List}.  

Hi,
would like to add, SUBSTITUTE might cause ERROR when applied to the lookup field value, so you need to use CONCATENATE({List}) instead of {List}.  


Yes, it was! Thanks, It need to convert it to a string with the CONCATENATE function.

Reply


Terms & Conditions