Nov 07, 2023 12:14 PM
I have two data tables;
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:
Any ideas on how to solve for either of these?
Nov 07, 2023 03:44 PM
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.
Nov 09, 2023 04:26 AM
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}.
Nov 10, 2023 03:36 AM
Yes, it was! Thanks, It need to convert it to a string with the CONCATENATE function.