Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Concatenate all elements of two arrays with all elements of another array

566 4
Showing results for 
Search instead for 
Did you mean: 

Hello there,

My current setup:

  1. In one table [ALL PRODUCTS], I have two pairs of Lookup fields, providing all the options.
    [Products] = [Bananas, Apples, Grapes, Strawberries]
    [Markets] = [US, FR, DE]

  2. In another table [DELIVERED-PRODUCTS] I have multiple pairs of Lookup fields [Products] and [Markets], describing the products per market, a subset of the values in the previous table.
    [Products1] = [Bananas, Apples, Grapes]
    [Market1] = [US]
    [Products2] = [Bananas, Grapes, Strawberries]
    [Market2] = [FR]
    [Products3] = [Grapes]
    [Market3] = [DE]

Ultimately, I would like to know what combinations are possible but not delivered per market, see point 5.

  1. I assume this would be somehow done by concatenating all elements of one array pair with the other, as in:
    [DELIVERED_Products_US] = [Bananas_US, Apples_US, Grapes_US]
    [DELIVERED_Products_FR] = [Bananas_FR, Grapes_FR, Strawberries_FR]
    [DELIVERED_Products_DE] = [Grapes_DE]

  2. Same for the [ALL PRODUCTS]
    [ALL_Products_Markets] = [Bananas_US, Apples_US, Grapes_US, Bananas_FR, Apples_FR, Grapes_FR, Strawberries_FR, Grapes_DE, Strawberries_DE]

  3. Ultimately, I would like to know what combinations of products and markets are in ALL PRODUCTS and not in DELIVERED PRODUCTS.
    [ALL-DELIVERED_Products_Markets] = [Apples_FR, Strawberries_DE]

Thanks a lot!

4 Replies 4

Hi @christoph,

It’s not that clear to me the structure you have because you mention that most of the fields are Lookup fields.
Would you like to share a screenshot of both tables and then tell us what exactly do you want to do? Then it will be very easy to help you :slightly_smiling_face:


Thanks for your help!

Maybe we could try with this intermediate step:

How can I concatenate every item of one item with every element of another, to have all possible pairs?

Sticking to the example above, this is what I’ve got:

And this is what I am looking for

If I understand what you are trying to do, it cannot be done with a formula field or with rollup fields.

Of course, there is the possibility that I do not understand your situation. Screen captures showing your tables and the data would help.

If you are looking to make this calculation using a script, that is possible.

It is also possible that a different base design might make it easier to calculate what you want.

Hi @christoph,

What we all recommend you to do is to change the base design. With no been sure what exactly do you try to do I think that an effective way to store your data is the following:

  1. On the table with products, every record (this means every new line) corresponds to one product
    In this table you can put a single select of multiple select filed with values for the attribute like {product_type} = (Apples, Bananas, Grapes)
  2. In the table with orders you will put follow the same logic of one ordered product per line.

Does it work for you ? If not please share with us some screenshots for better understanding.