Skip to main content

Multiple Values in a single field used in formula or broken out into columns

  • November 7, 2023
  • 3 replies
  • 36 views
E
Forum|alt.badge.img+1

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?

 

2 Attachments

3 replies

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • November 7, 2023

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.

Alexey_Gusev
Forum|alt.badge.img+25

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}.  

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • November 10, 2023

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.

Terms & ConditionsAccessibility statement