My current setup:
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]
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.
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]
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]
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!
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:
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.
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:
Does it work for you ? If not please share with us some screenshots for better understanding.