Query DynamicAttributes

[josecgomez]

Okay “UNCLE”!!!

Does anyone have the secret encantation required to get the values of a DynamicAttribute Set attached to a Part Record?

I have a Part with some Dynamic Attributes

In the Db there are some very nice laid out tables such
as

erp.DynAttrValue

Which I thought would have all the Dynamic Attribute Values related to my Part Record and indeed that seems to be the case, however the RealtedToSysRowID field doesn’t match my Part SysRow ID, it maches an entry in another table called DynnAttrValueSet

SELECT * FROM erp.DynAttrValueSet

While looking there there is no reference anywhere to my part record, just a confusing set of “Descriptions” and an attribute Hash…

And I can’t make heads or tails of where or how Epicor can know which attribute values belong to my Part.

[jgiese.wci (Joshua Giese)]

Do a full DB search for that RelatedToSysRowID see where else it pops up.

[knash (Ken Nash)]

You are going to laugh/cry/shout for joy.

First you set the part to the AttrClassID.

This is a DMT for Part

Epicor will make all of the default values

[josecgomez]

Well right that gets me the ClassID that is tied to the Part but how do I get the value that belongs to that particular part instance?

[hackaphreaka (Garret Travers)]

DynAttrValue.RelatedToSysRowID = Part.SysRowID

@josecgomez

however the RealtedToSysRowID field doesn’t match my Part SysRow ID, it maches an entry in another table called DynnAttrValueSet

Weird. Maybe it has to do with how you went about setting them up? Newer version?

[knash (Ken Nash)]

ahh like a report by type?

That we had to create a BAQ for each Attribute.

[knash (Ken Nash)]

select 
	[Part1].[PartNum] as [Part1_PartNum],
	[Part1].[PartDescription] as [Part1_PartDescription],
	[Part1].[ClassID] as [Part1_ClassID],
	[Part1].[InActive] as [Part1_InActive],
	[AttValuesbyPart].[DynAttrValue_AttrClassID] as [DynAttrValue_AttrClassID],
	[AttValuesbyPart].[Calculated_Color] as [Calculated_Color],
	[AttValuesbyPart].[Calculated_FormFactor] as [Calculated_FormFactor],
	[AttValuesbyPart].[Calculated_Material] as [Calculated_Material],
	[AttValuesbyPart].[Calculated_PSU] as [Calculated_PSU]
from Erp.Part as Part1
left outer join  (select 
	[Part].[PartNum] as [Part_PartNum],
	[DynAttrValue].[AttrClassID] as [DynAttrValue_AttrClassID],
	(MAX(
 IIF(DynAttrValue.AttributeID = 'Color', DynAttrClassDtlListVal.Description, ''))) as [Calculated_Color],
	(MAX(IIF(DynAttrValue.AttributeID = 'FormFactor', DynAttrClassDtlListVal.Description, ''))) as [Calculated_FormFactor],
	(MAX(IIF(DynAttrValue.AttributeID = 'Material', DynAttrClassDtlListVal.Description, ''))) as [Calculated_Material],
	(MAX(CAST(IIF(DynAttrValue.AttributeID = 'PSU', DynAttrValue.DataLogical, '') as int))) as [Calculated_PSU]
from Erp.DynAttrValue as DynAttrValue
left outer join Erp.DynAttrClassDtlListVal as DynAttrClassDtlListVal on 
	DynAttrClassDtlListVal.Code = DynAttrValue.DataCharacter
	and DynAttrClassDtlListVal.AttrClassID = DynAttrValue.AttrClassID
	and DynAttrClassDtlListVal.AttributeID = DynAttrValue.AttributeID
inner join Erp.Part as Part on 
	DynAttrValue.RelatedToSysRowID = Part.SysRowID
	and ( Part.ClassID = 'ENC'  )

group by [Part].[PartNum],
	[DynAttrValue].[AttrClassID])  as AttValuesbyPart on 
	Part1.PartNum = AttValuesbyPart.Part_PartNum
where (Part1.ClassID = 'ENC'  and Part1.InActive = 0)

[knash (Ken Nash)]

I am working on a mass loader as well right now.

It’s a bit garage style programming, but it works… lol

[knash (Ken Nash)]

Quick SQL

select 
	*
from Erp.DynAttrValue as DynAttrValue
left outer join Erp.DynAttrClassDtlListVal as DynAttrClassDtlListVal on 
	DynAttrClassDtlListVal.Code = DynAttrValue.DataCharacter
	and DynAttrClassDtlListVal.AttrClassID = DynAttrValue.AttrClassID
	and DynAttrClassDtlListVal.AttributeID = DynAttrValue.AttributeID
inner join Erp.Part as Part on 
	DynAttrValue.RelatedToSysRowID = Part.SysRowID

ADHD/ADD response coming to a close.

[josecgomez]

@knash

DynAttrValue.RelatedToSysRowID = Part.SysRowID

That isn’t true for us though, that RelatedToSysRowID doesn’t match Part.SysRowID… that matches record in DynAttrValueSet

What version are you guys in?

[knash (Ken Nash)]

Well, Darn… here I thought I was helping.

We are on 10.2.600.4.

[andrew.johnson]

Part has a field called DefaultAttributeSetID. You would join that to the DynAttrValueSet table, AttributeSetID field.

From what I have seen, RelatedToSysRowID is always related to the Dynamic Attribute Set, and then the Dynamic Attribute Set ID is related to the Business Entities. Part, JobMtl, etc.

[josecgomez]

Yeah I think they changed something Major in 202X cause we have a bunch of “extra” tables in the middle and I ca’nt seem to figure out how to connect them.

[knash (Ken Nash)]

Let me check on 22.2

[josecgomez]

@andrew.johnson

Part has a field called DefaultAttributeSetID

There it is… Jesus…

Yup that works

Thanks @knash for your additional help, it seems they complicated things in 202X

[knash (Ken Nash)]

OK what else do you need to do to set up Dyn Attributes in Kinetic?

My DefaultAttributeSetID are all zero.