In the previous video we created this Table. In this case
it's a Linked Table to an existing SQL Server Database.
But, how do we actually make use of this
in the Elements? So, if I go out here,
is there a way, is there some kind of a reference that I can go out and get
data from that Table? As you can see, there's a
Table Look-up Option here. It's a type of a Data Reference.
I can associate that with an Attribute.
And, what I'm doing is, basically, I'm going to be passing
an Argument and looking to see if that Argument matches
the Material ID, and then I'll instruct it to return
one of these Columns. In this case, I'll try to retrieve the
Heat Capacity. So, if that's my Product,
and if I can, if I can look this Product up
and say, okay, if my Product matches that Material ID,
then I'll instruct it to return that Heat
Capacity. And, I'll do this over here, and I'll just create this
as a New Element called Heat Capacity. So,
how would I go about doing that? Well, first of all,
I have to make sure that this is, and this is just a
minor point, but I have to make sure that this is
extensible, this particular Element. Right now this
Element, I cannot add new Elements to it,
and if you recall, it's because of what we did in a previous
Exercise. When we,
when we did our Exercise a little while ago
in creating these Templates, if you remember one of the
things I did was I Associated this new Template with
that Element, but this Template does not allow
Extensions. So, I'm going to -- the first thing I'm going to do is
go ahead and select Allow Extensions. I'll check
that in, and now, if I go
back in here and look at this Mixing Tank, the Mixing Tank that's
based on that Template, now
I should be able to create new Attributes. As you can
see, now, as opposed to before, now, I can actually create new
Attributes. So, this is just kind of a
minor point but, since this is based on
an Element Template, I have to make sure that Element Template
will support adding New Attributes first.
And, of course, that's got nothing to do with this
Table Look-Up. It's just something I needed to do in order
to start creating New Attributes. So, the first Attribute
I'm going to do is I'm going to create a New Attribute that,
that documents in this particular Element
what the Product Name is. I'll choose
the New Attribute, and I'll call it Product. And this is simply
going to be a String. Now, this Product Name,
this is what I'm going to try to match
to the Material, to the Material ID. So,
we'll make this a String, and in this
case let's say the Current Value for this is
wx12000. Now, that's not just
an arbitrary number, or that's not just an arbitrary
Product Name there. I did that because,
in My Library, if we take
a look at this Table that's in SQL Server, you can see
that there is a , a, a row
in this Table in which the Material ID is wx12000.
So that's actually where I'm going to be making
the Link between the two. Now,
I want to retrieve the Heat Capacity. I want to
retrieve the Heat Capacity when my Product
is equal to wx12000. So let's go back to
the Elements. I'm going to add a brand new Element
here, and this time this Element's going to be a
Data Reference to a Table Look-up, so let's call
this, we're going to
call this the Heat Capacity
[typing]
and
that Heat Capacity is going to be a Data Reference
to a Table Look-up.
Now like other Data
References this has the Settings where we get to set,
you know, how we retrieve this. And the first item in that
is which of the Tables in AF we're going to be
retrieving this from. Well, this is going to be that Table
Still in SQL. And, by the way, let me show you Before and After
here. The Default is it, that it
found the very first Table, and if found the three Columns
that are in that Table. If I switch to a
different Table, we will go out
and find whatever Columns are in that Table. Now what this
is, this Result Column is what it is we're trying to retrieve.
As I said earlier, we wanted the Heat Capacity,
so I'm going to bring the Heat Capacity in. You know,
this is kind of confusing 'cause I've got them
named both the same things. They certainly don't need to be named the
same thing. So, just to make sure
there's no ambiguity here, let me just call this
my Heat Capacity. That's going to be the Heat Capacity,
in this context, within this Element right
here. So, again, I'll go in here. I'm going to
specify Table Still in SQL.
I'll go out and look for the Results Column which is
Heat Capacity in this case. And, I don't want to do
any Calculations at this point. I'm simply going to bring in the raw data.
So let's ignore Summary and Order, etc.,
and here's my, here's where I'm
actually going to make that Connection. I said that I
wanted to check to see when the Product ID
is equal to that Material ID. Well,
what we're doing is we're showing you the
Columns here from that Table, Material
ID, and we're going to try to map those
to the Attributes that already exist
in the Element I'm currently working with.
So, one of the Attributes, the one I just added,
is this Attribute called Product.
We're specifying here when that Material ID is equal to Product, that's
the, you know, when that is a match,
then it's that result we're going to be bringing
back, that Heat Capacity we're going to bring back.
I'll go ahead and add this. There's our
Query. Go ahead and say OK.
And, as you can see, the big finish to this is we
actually are now retrieving that Heat Capacity.
If I go back to My Library, you can see that
was the Heat Capacity with that Product.
And just to make it obvious, let's say I had
a different Product in that particular Element,
hc15000 for example. My Product
were different. Let's change that to
hc15000. We would
retrieve a different set of results.
I'm not going to see that until I refresh my screen.
[typing]
And we resequenced
this alphabetically. There's hc15000,
and my Heat Capacity is now coming back as
.87 which is
the Heat Capacity for that Product. Okay, let's
point out a couple of things over here. First of all, if we go into
these to look at this Attribute here,
this is my Heat Capacity. And notice
the, the Settings is something I can use to Configure
this. But, I can also edit this by hand. If you'll look
over here, this is actually the SQL Statement that we're executing.
So, for example, instead of Heat Capacity, I
could have said let's, let's select the, the
Viscosity. And let's take a look at what happens over
here. See, now we're bringing back
Viscosity instead, or Density, or
any of these others.
There we go. There's Density now.
And secondly, within Settings
if you recall, there were these Summary Options
and this is just basically going to be generating some SQL Code for us.
But let me, let me show you how we can make use of that.
I would like to retrieve, not just the,
the Viscosity of a specific
Row, but let's do an Average Viscosity.
Let's do one of these Aggregates or Summaries. So, what I
can do, let me create a New Attribute here. I'm going to call
this Average Viscosity.
[typing]
This is going to be a Table Look-up. I guess I should have done this earlier.
Let's just make this a Single Precision Real
so that we can use it properly in Calculations and
whatnot. And, for the Data Reference, we'll do a Table Look-up.
As we did before, when we choose
the Table Look-up Settings, you get a choice of
the different Tables that exist in AF. So
far I only have three. Here's the one that's reaching out
and grabbing data from SQL Server. Now when I switch to
that we see a List of all the Columns. Now, this time, I'm going to
retrieve Viscosity, but instead of just the
Viscosity of whatever I select,
I'm going to choose to show the Average Viscosity,
and then I'm going to set up a Select Statement that's
going to actually retrieve many different Viscosities --
in fact, all of the Viscosities.
I'll do that like this. I'll say the Material ID is
LIKE, and then from here I can just
put in a, let me put in a,
an *. I'll go ahead and add that.
And, when I retrieve this,
, this materialdLIKE"*", essentially
is going to match all the Materials in that Table
and this, because it's a Summary, an Average,
is going to give me the Average Viscosity for all of those.
So when I Acknowledge, as you can see, this Average
of Viscosity what it's doing, it's going out to
this Viscosity Column. It's finding everything
that matches Material ID is Equal
to *, which is the wildcard character. So, it
finds all six and it simply adds up these
Viscosities and divides by six.
Or, for example, instead of saying let's match to All,
I can just say well, we're only interested in the Viscosity
of those, those Rows here
in which the Density's greater than 3,000. So, I go back
here, and I can either do it from
within here, just type it in by hand, or I'll just go
and do it in here in Settings. I'm going to change this over
and let's say when the Density
is greater than 3,000.
And
when you Add these, these are going to be added to this
Statement. I can go ahead and modify this. I'm only going to restrict
it to that one Statement. And watch what happens
to our SQL Query over here.
And, one last
Option I want to mention is that if you
do have a Column that requires some
type of Interpolation, like Strapping Table Calculations,
or something like that, in which you have in your
SQL Database, or in your Relational Database, you've got
one Column here that represents
Values you're trying to look up, and then
the Column over here that represents the Values you're trying to
return, and you simply want to do an Interpolation
so that, say, if this comes in at,
is passed in at 2.2, it comes back
as the Interpolated Value between
20 and 30, it would represent that Interpolation
between 2.2 and 3, then there is an
Interpolate Operator that you can use.
So that's all supported. These are all, you know, SQL constructs.
In this Example, that's exactly the results you would get
if you put in a 2.2 and you configured
it properly, you would get returned a 22.