Lookup with 2 tables

Like (0) Share

Report

Posted on 13 Jan 2022 14:59:34 by rho1967

141

I have 2 tables.
Recipes and Combi (see example)

Combi looks like this:
CombiID, Combi Name,Color,RecipeID1,RecipeID2,RecipeID3,RecipeID4 etc

Recipe looks like this:
RecipeID,Recipe,IngredientID1,IngredientID2,IngredientID3,IngredientD4 etc

I would like to see the recipes listed in IngredientID1, Ingredient2, Ingredient3 etc when I am on Combi-1.

I will now create a label containing
LookUp('kitchen.combi', CombiID = ThisItem.CombiID,ReceptID1)
I call that label lbl_ID1 and make it invisible

Then in the label what I have to show I do
LookUp('kitchen.recipes', RecipeID = Value(lbl_ID1.Text),Recipe) in the text field

This works but can't do this in 1 command without first creating an lbl_ID1.

Pict_1_recepten.jpg

Pict_1_show.jpg

Pict_1_Combi.jpg

All responses (8)

Answers (1)

RandyHayes 76,287 Super User 2024 Season 1 on 13 Jan 2022 at 20:29:21

Like (1)

Report

Re: Lookup with 2 tables

@rho1967

Well, I would not advise doing a lookup on all of the columns as that will hamper performance.

I cannot tell from your design if this is in a gallery or just on a screen.

If this is in a Gallery, then I would advise adding the Lookup to the ingredients to the Items as a column.

Something like this:

AddColumns(yourCurrentItemsFormula, "Ingredients", LookUp('kitchen.Ingredients',IngredientId=IngredientID1))

Then, your formula in the gallery for the display of the ingredients would be more along these lines.

If(ThisItem.Ingredients.Egg, "Egg")
rho1967 141 on 13 Jan 2022 at 20:23:01

Like (0)

Report

Re: Lookup with 2 tables

Hi Randy,
Below works. Must do this per IngredientID.
But how do I get the name Egg as text when the value is true?
Now the value will be true if egg is entered. But would you like to see a name or cross?

LookUp('kitchen.recipes', ReceptID = LookUp('kitchen.combi', CombiID = ThisItem.CombiID, ReceptID8),
LookUp('kitchen.Ingredients',IngredientId=IngredientID1,Egg))
Verified answer

RandyHayes 76,287 Super User 2024 Season 1 on 13 Jan 2022 at 18:16:35

Like (0)

Report

Re: Lookup with 2 tables

@rho1967

Yes, so as I see it, you have your ingredients as columns in your table. This is a challenge as you will need to reference each column to get its value. Normally you would have this in a list with separate records.

Ex:

Recipe Ingredient

1 Egg

1 Fish

1 Gluten

2 Gluten

2 Nuts

3 Egg

3 Gluten

This is far more friendly to then display as you would just Filter the table by the Recipe number. That would return a list of the ingredients.

As it is now, you will need to Lookup the entire record to then reference the ingredients columns.

This is best done in the Gallery Items so that you are doing only ONE Lookup to the ingredient list. Or if you are not working with a Gallery, then at least somewhere you are doing this lookup once.

Not entirely dure of your app design, so the above are suggestions.
rho1967 141 on 13 Jan 2022 at 18:02:21

Like (0)

Report

Re: Lookup with 2 tables

And the last picture

Pict_1_show.jpg
rho1967 141 on 13 Jan 2022 at 18:01:58

Like (0)

Report

Re: Lookup with 2 tables

Hi Randy,
I made the pictures a bit bigger.
Hope they are good now.

What I want to achieve as the Pict_1_show.jpg image.

When I look at kitchen.combi that there is a recipe for this. After that, the recipe must show all the ingredients that are in that recipe. When I see the ingredients I have to show all allergies (gluten, egg, fish etc). Is this possible?

Pict_1_recepten.jpg

Pict_1_Ingredient...

Pict_1_Combi.jpg
RandyHayes 76,287 Super User 2024 Season 1 on 13 Jan 2022 at 17:43:21

Like (0)

Report

Re: Lookup with 2 tables

@rho1967

Yes, you can lookup on as many tables as you want.

I can't tell too much on the picture, but is your ingredient list a table of ingredients for a particular recipe? In other words, are there multiple records that make up the ingredients, or are the ingredients all in one record?

I ask, because, if one record, then you will use LookUp. If multiple records, then you will want to use Filter.
rho1967 141 on 13 Jan 2022 at 16:58:54

Like (0)

Report

Re: Lookup with 2 tables

Hi Randy,
Super! Thank you very much.
Can this also be done with 3 tables?
I also have an ingredients table.
RecipeID, Recipe, IngredientID, IngredientId2
Can I link these so that I also see the ingredients?

Let me know if you want bigger pictures.

You are awesome.
Thank you

Pict_1_Ingredient...
RandyHayes 76,287 Super User 2024 Season 1 on 13 Jan 2022 at 15:13:32

Like (1)

Report
Re: Lookup with 2 tables

@rho1967

Sure, you can combine that in one formula. There is usually never a need for an invisible control in order to get values. I see people do this often, but it just is not necessary.

The following formula will combine the two into one:

LookUp('kitchen.recipes', RecipeID = LookUp('kitchen.combi', CombiID = ThisItem.CombiID, ReceptID1), Recipe )

Not sure if you have tried that or if you are having issues with it. Your pictures attached are too small to be seen.

I hope this is helpful for you.