How to Make a Double Lookup

[Libbya]

Double lookups are so darn useful and slightly tricky to make so I figured I'd make a screencast in a dedicated thread that shows exactly how to make one and how to select each of the tables.

[Libbya]

---

@Ed__Jobe wrote:

I found a similar way to do 2 lookups, but with one Lookup. You can add multiple parameters to a single lookup action. This also provides the ability to do asymmetrical lookups. For example, in the attached block, I have Width (X) and Height (Y) stretch parameters. With 2 lookups, selecting a value changes the height and width using the same value, i.e. a square. But with the lookup in this sample, I can implement a table with x and y of different values.

---

What you posted is just basic single-lookup functionality.  Single lookups are certainly powerful items, but that are very well documented and their discussion is off-topic in this thread.  This thread is about double lookups and you might have missed the huge added functionality that double lookups offer over single lookups.  

[Ed__Jobe]

@Libbya wrote:

What you posted is just basic single-lookup functionality.  Single lookups are certainly powerful items, but that are very well documented and their discussion is off-topic in this thread.  This thread is about double lookups and you might have missed the huge added functionality that double lookups offer over single lookups.

---

I guess in my line of work I haven't come across a need for double lookup. Can you post an example block?

[Libbya]

I have posted examples in many threads on this forum.  

[Jaycee.foli]

I would love to see a tutorial that explains the "why" behind lookups, rather than just the how. I have used a double lookup twice before, but always as  "do this" from someone else, without explanation as to why this method is the way to go. I think it would be greatly beneficial to many of us middle of the road users to increase our knowledge and skills. If you have any materials or videos that do this, please share. It would be greatly appreciated.

[j.palmeL29YX]

---

@Jaycee.foli wrote:

... the "why" behind [Double] lookups, 

---

I'd suggest, analyze some  of the block examples which use Double Lookups and then try to get the same functionality without using a Double Lookup. I think this way your question will answer itselfe. 

[Libbya]

The concept on how they function is relatively simple.  

With a single lookup, when the user selects a different row on the lookup table either through the grip or through properties, the block parameters that are added to the table are changed to match their values for that row of the lookup table.

A double lookup behaves in a similar manner, but instead of the user selecting a row of the table, when the values of the parameters listed on one table all match a row, the row is selected and the parameters that are added to the other table are changed to match their values for that row of the lookup table.

When the above is understood, the 'why' becomes relatively obvious.  It allows some parameter values to be forced when other parameter values match specific block states.  

[Jaycee.foli]

So basically, one look-up makes certain parameters active, and the second look-up tells the first which parameters to make active based on how the current active parameters are set, via the grips for instance? Is that the basic idea?

[h_s_walker]

Yep that's the basic idea

[Gil_C]

Now that is really cool. I wonder if you can do it with visibility state. I am thing not, but going to try it on Monday.

[zobb011111]

To make a double lookup in Excel, follow these steps:

1. Set Up Data: Organize your data in a table format.

2. Create Dropdowns:
- Select cells for category and topic selection.
- Use `Data Validation` to create dropdowns for these cells.

3. Find Row and Column
- Use `MATCH` to find the row for the selected category and topic.
- Use `MATCH` to find the column for the data header.

4. Combine `INDEX` and `MATCH`
- Use `INDEX` to get the value based on the found row and column.

### Example Formula
Assume category is in `E1`, topic in `F1`:

```excel
=INDEX(C:D, MATCH(E1, A:A, 0), MATCH("Data 2A", 1:1, 0))
```

This formula retrieves the value by combining `INDEX` and `MATCH` functions.

[timothy_crouse]

Here is a PDF Guide to help . . .

v2 Updated with Lookup Table Parameter Allowable Entry Format

v3 Added and Example

-Tim C.

[miah_mcbride]

This is really awesome! thank you for explaining (showing slowly) how to add a double lookup! I got this part to work perfectly.

I have really tried searching for an example block that is super simple, to wrap my head around this, but I only seem to find examples of pretty complicated blocks where others are either having trouble, or the block complexity is outside of my knowledge scope.

Is there a simple example block anywhere that I might be able to use to help me figure this out?

Typically, I use Visibility states for every option I think I will need, but I think we can all agree that these can get out of hand when you have too many visibilities. 

I've started using Lookups to help me add options, but I typically just drive these from the visibility states. This creates a grip for anything I usually want to change.

With a double lookup, should you be able to pick from two different data sets from the grip?

When I add a Double lookup, I don't seem to have the ability to choose the info I have used to drive the table.

A simple example could really help me out.

Attached is a block that I would like to keep the visibilities limited to what I have, but be able to have a lookup table that pulls from a "list" that isn't really an action.

For example:

if the Visibility Grip is set to "Sealant And Backer Rod"

Is there a way to get the Lookup Grip "Manufacturer" (and others) so that it pulls from a "list" somehow and not from an Action parameter? A list that has all our venders on it, for example?

Am I barking up the wrong tree here?

Thank you all so much!!

[timothy_crouse]

Try This . . .

Add a Block Properties Table, a dialog will popup allowing you to add parameters or create parameters

-Add (create) a String Paramater, 2nd icon from top left, select type string

-Use the string column to create a sort of a menu system

--I imagine the manufacurer would be added as a string column too, note you can paste column data into a block properties table

Best Regards

-Tim C.

[timothy_crouse]

Hey Libbya

Could you explain the signifigance of the way the lookup angles were defined in the block you finalized for me:

I believe if these were answered online it would help not only me but more than a few folks gain insight into the use of double lookups:

Polar Stretch Angle, Lookup Table 1

(,90.00], (270.00)

1. Why are mixed brackets required?

2. Why is there a , at teh beginning

(90.00,270.00]

1. Why are mixed brackets required?

Text Attribute Rotation Control

1.  The control is set off to the side with no associated origin to teh attribute, seems unique as rotations controls are typically based upon an origin.  In this case is the control use simply to define an angle which is loosley linked to th attribute rotation angle property?

2.  Can the rotation action handle multiple objects or is a separate rotation required?  Eg if their were (2) attributes would two rotation parmaeters sets be required?

Thank You

-Tim C.

[timothy_crouse]

Libbya if you have time could you answer the following questions?  I believe answering them with help myself and many folks to have an understanding of how to enter parameters into a double lookup,  I looked aroud but have not located and help information that defines the parameter entries you provided.

These questions are in reference to the Polar Stretcvh Rotation Action Defined in the Lookup 1 Action:

DEFINED LOOKUP PARAMETERS (Angle1 Rotation action of the Polar Stretch)

LINE 1:  (,90.00],(270.00,)

1.  Why is there a comma at the begining?

2.  Why is there a comma at the end?

3.  Why is the first parameter encased inside mixed brackets?

LINE 2:  (90.00,270.00]

(90.00,270.00]

1.  Why is there no comma requred at the beginning or end?

2.  Why is the parameter encased in mixed brackets?

Thanks in Advance

-Tim C.

[christian_canaryLU3LG]

See my comments in BOLD

DEFINED LOOKUP PARAMETERS (Angle1 Rotation action of the Polar Stretch)

LINE 1:  (,90.00],(270.00,)

1.  Why is there a comma at the beginning?
The comma at the beginning denotes anything less than 90.

2.  Why is there a comma at the end?

Similar to the first, except this denotes anything greater than 270

3.  Why is the first parameter encased inside mixed brackets?

Brackets and parentheses come from math set notation, meaning including and excluding.  "(" means excluding while "[" means including.

NOTE: When doing sets for anything less than 90 (ie. (,90.00] ), you can't use the bracket "[" as you'll never get to negative infinity (you're getting into math theory regarding set notation which we don't care about here 😂).  This also applies to anything greater than 270, as you'll never get to positive infinity.

EXAMPLE:
LINE 1:  (,90.00],(270.00,)
This means 0 - 90 (including the 90), and the other range means 270 (but not including) to 360.  In simple man's terms, anything less than or equal to 90 and greater than 270.

LINE 2:  (90.00,270.00]

(90.00,270.00]

This means greater than 90 and less than or equal to 270.

Hope this helps,

[julian_felipe_herrera]

Good afternoon,

Thanks for the awesome tutorial.

I'm working with a block using 3 visibility states.

There is one weird issue (this also happens sometimes with only 2 visibility states): when I change the first two values and then change the third one, the first two get frozen.

Real quick context: I have a bar with two possible hooks at each end and one extension line. For example: 90º–180º–No Ext line or 0º–90º–Ext.

Values are fixed: 0, 90, 180 for hooks and Yes/No for the extension line.

I can assign a 90º hook at the start, and the visibility state updates correctly. Then I assign a 180º hook at the end, and again it updates just fine. I can even go back and change the start hook to something else, and it still updates properly.

The issue starts when I change the extension line. If I do that and then try to change either of the hooks, the visibility state no longer updates as expected.

Interestingly, I have a series of attributes tied to each possible visibility state (18 in total). These attributes do update correctly — they appear or disappear based on the visibility state as intended.

What I'd like is a way to force the visibility state to refresh whenever I make changes, instead of having to manually trigger it by reselecting previous options.

I hope I explained the situation clearly. I'm attaching the block in case anyone is kind and able to take a look at it for me.

Best regards,

Julián.~

EDIT: All happens when I have Initial Hook = No (0º) and Extension Line shown (Yes). All the other states behave nice.

[susanK39XP]

Text Attribute Rotation Control

1.  The control is set off to the side with no associated origin to teh attribute, seems unique as rotations controls are typically based upon an origin.  In this case is the control use simply to define an angle which is loosley linked to th attribute rotation angle property?

2.  Can the rotation action handle multiple objects or is a separate rotation required?  Eg if their were (2) attributes would two rotation parmaeters sets be required?

Thank You

-Tim C.

Can anyone answer Tim's questions from above?

[susanK39XP]

I have been tweaking your dynamic block to fit my needs (very useful block!). I changed text att to one line with multiple line option on. Wondering if there is a way to keep text att justified left when it rotates >90? I'm thinking the flip action is forcing the justification left of 90 atm. Thoughts on how to make it work?

[Libbya]

It is unfortunate that so many off-topic comments have been posted in this thread.  The result is that the on-topic information is now getting lost.