Author Topic: Populate Shape Data based on user entered Data (VLookup equiv?)  (Read 350 times)

0 Members and 1 Guest are viewing this topic.

jimibray

  • Jr. Member
  • **
  • Posts: 21
Hi,

I've been lurking for a few days now trying to find some answers to things but this one eludes me!

I have a "smart" shape for a wire and when the user places it they're prompted to populate the wire size (from a list) and the temperature rating (also a list), with these two items known I can assign a theoretical max current carrying capacity. I'd like that to happen automatically so as the wire size or temp rating changes this field dynamically updates. But I'm not even sure where to store my table of data and/or how to reference it?

I'm using Visio Standard 2016.

Any help on this kind of dynamic field would be greatly appreciated.

Thanks,
James

Surrogate

  • Hero Member
  • *****
  • Posts: 1047
    • ShapeSheet™ Knowledge Base
Re: Populate Shape Data based on user entered Data (VLookup equiv?)
« Reply #1 on: August 27, 2020, 06:18:21 AM »
Try use LOOKUP function or LOOKUP & INDEX function

Yacine

  • Hero Member
  • *****
  • Posts: 2712
Re: Populate Shape Data based on user entered Data (VLookup equiv?)
« Reply #2 on: August 27, 2020, 06:18:32 AM »
Looking simply in a list is easy.

Say you want element i from list B, i being defined from a list A:
prop.A.format = "a;b;c;d;e"
user.B = "11;22;33;44;55"
result = index(lookup(prop.A, prop.A.format),user.B)
If you choose b in prop.A the result will be 22.

If the result depends on 2 variables (a table instead of a list), then you can use the same principle with nested lists:

                   Wire size
                   1   2   3   4
Temp    50  25  36  47  58
             60  26  35  36  55
             70  28  39  48  59

You will now slice the table either in rows or columns and concatenate them with two different separators:
user.B = "25,26,28;36,35,39;47,36,48;58,55,59" // note the commas and the semi-colons

So instead of getting the result right away, you try first to find out the right column and only then you choose the right element:

Say prop.wire_size = 2, you need the list "36,35,39".
If prop.temp = 60, then the result is 35.

--> user.col = index(lookup(prop.wire_size, prop.wire_size.format), user.B) // specification of the separator not needed here as it is the standard (";")
result = index(lookup(prop.temp, prop.temp,format), user.col, ",") // specifiy the separator (",")

You could of course write everything in one line, but that would not improve the legibility.

With this principle you can even write formulas with more than 2 input variables, just be nesting list deeper.

The nested lists should not be written manually, use excel to prepare them.
« Last Edit: August 27, 2020, 06:21:33 AM by Yacine »
Yacine

jimibray

  • Jr. Member
  • **
  • Posts: 21
Re: Populate Shape Data based on user entered Data (VLookup equiv?)
« Reply #3 on: August 27, 2020, 10:42:37 AM »
Hi Yacine,

Thanks for the detailed response. Just one point to clarify:

The user.B that you've referenced stores the table as nested lists - where's the correct place to store this data in the shape?
The user.col looks like it stores the result of the lookup, is that correct? Could I change this a prop.Current?

Thanks again,
James


Yacine

  • Hero Member
  • *****
  • Posts: 2712
Re: Populate Shape Data based on user entered Data (VLookup equiv?)
« Reply #4 on: August 27, 2020, 12:50:44 PM »
You set a user field in the shapesheet.
And yes you can use a prop field instead of user.
« Last Edit: August 28, 2020, 01:37:20 AM by Yacine »
Yacine

jimibray

  • Jr. Member
  • **
  • Posts: 21
Re: Populate Shape Data based on user entered Data (VLookup equiv?)
« Reply #5 on: August 28, 2020, 04:40:10 AM »
Hi Yacine,

I found the user fields, thanks for your help this is working as expected!

for ref I'm using to user fields, one to store the list of data and one to store the first half of the look-up, I'm then using a property to store the result so I think that's exactly the implementation you provided.

Cheers,
James