Query DynamicAttributes

Okay “UNCLE”!!!

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

confused disney animation GIF

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.

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

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
image

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?

DynAttrValue.RelatedToSysRowID = Part.SysRowID

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

ahh like a report by type?

That we had to create a BAQ for each Attribute.

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)

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

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

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.

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?

Well, Darn… here I thought I was helping.

We are on 10.2.600.4.

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.

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.

Let me check on 22.2

Samantha Jones Wow GIF by HBO Max

There it is… Jesus…

Yup that works :slight_smile:

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

1 Like

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

My DefaultAttributeSetID are all zero.