How to merge multiple lookup fields?

Question

My scenario in a nutshell:
Theres a list which contains some food for example. (2010 Server)

Foods
ID Name 1 Apple 2 Banana 3 Carrot 4 Potato

Theres another list which contain food categories
Categories
ID Name MultiLookup_for_foods 1 Fruits 1;2 2 Vegeatables 3;4

Then theres the main list. It contains a multilookup field for Foods

I don't know how to achieve to create an InfoPath form, or a Designer workflow for the following scenario: I can select Categories, then the list should contain all the Foods in the selected Categories.

Any idea how to do it? I don't have Visual Studio so I'm stuck with InfoPath and SharePoint Designer

Answer 1

Why would you go to the categories list to see foods?

Your list architecture is backwards. Foods should contain a lookup to Categories, not the other way around. Suppose you add a new food, "orange". In your scenario you have a create a list item in Foods, then go into the categories list to add the new food to the category. This is messy and error-prone.

With properly structured lists you have no need for a form, or a workflow, or any code. Everything you need to do is supported right on the list.

Answer 2

Place a lookup column in the list of foods to class and leave it as required.

When you create foods you select the desired category and you can see which food belongs to category. You can only see if there is any food in the category citing them in the workflow. But limits the data. You can do the research on that REST is more advisable and possible to see if there is any food category.

I use SharePoint Designer to do this. I hope I have helped you.