Skip to main content

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!

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

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


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

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.

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


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.

Thanks

Terms & ConditionsAccessibility statement