Where are configuration values / smart strings stored?

Have you considered writing these values into an extended OrderDtl table or a UD table from a doc rule in the configurator?

well… an idea to continue with… create a PIVOT BAQ… in the pivot, put all the INPUTS that you want to see. These will become your columns. You will get one row per configurator session.

@danbedwards - your approach with the OrderDtl table, and/or the UDTable is a secondary approach that is used. I just created one where we create a “SmartID” (sort of a smart part number) that is an assembly of all the answers. We store this SmartID in a UD table, along with the part number that we assigned. This way, if someone answers the questions the same exact way, we can find the part number used last time, reducing the number of part numbers generated. We are not using a smart part number, because to get all the options in, the part number needs to be over 400 chars long (ahhhhhh).

1 Like

@timshuwy & @danbedwards - appreciate the feedback.
Dan - we actually put a few values right on the orderdtl line via the rules so we have some quick answers but it’s not enough for real BI analysis. We also thought about doing that UD Field with the mega-string Tim mentions - would work for lookups but not BI analysis. The only way to get the BI-useable data is to break it all out into columns and we didn’t want to add all of those to the orderdtl table. Hence the brute-force SQL query to build the UD Table.

Tim - PIVOT BAQ - right! Being an (essentially) one-man show that is teaching a couple of folks how to do a lot of this so I don’t have to be a one-man show - I get my blinders on too tight once in a while and forgot about the new stuff in 10 that 9 didn’t have - I spent a long time in 9 and working around query issues with SQL. Thanks for the reminder - I’ll take a look this week.

1 Like

How did you manage to push in PcValueSet data?

By extended OrderDtl you mean the OrderDtl.Number01, etc fields? Even if there are enough fields available, how would I get all the existing configurations into that table?

Also seems a bit silly to duplicate the data, but if I can’t find any other way I will give this a try.

Yes, extending OrderDtl and adding custom fields to populate – another option is a separate UD table that is extended. Duplication has good reason here and it allows for users to have easy access to the configuration data throughout the toolset and into products like EDD and EDA.I use document rules to do this.

I was revisiting this thread and noticed you said you can push in PcValueSet data from REST. Does that mean you can create configure a product from REST? That is also something I want to do…

Hi Tim,
Can an external BAQ work for the PcValueSet table? I believe it is stored in XML format, so maybe that is why it isn’t working for me?

People have create views to access this table through External BAQs. Or they do what @danbedwards did and duplicate the data in a UD table.

This solution won’t work for SaaS users as they cannot use External BAQs (or set up Views). I spoke to @Rich at last year’s Insights and they were working on a way to access this data from within the standard tool-set, including EDD and EDA. In the last year, I’ve spoken to three prospective companies who are looking at Epicor SaaS and they expressed concerns at not being able to get to this data. I know they’re working on it but not sure where it is in the backlog.

They are waiting for me to get frustrated and re-write all our configurators outside of the system so we can access the data. Just after that they will release an update that solves the major problems with configurators

1 Like

Carol - there is an internal ‘view’ called erp.PcInputValue that works for finding a configurator value set and returning a ‘table’ of values where each value is a row. Something like

select * from erp.pcinputvalue where groupseq=38952

Or, you can use it like this, where I have a stored procedure using this view to set up a temp table and then use that temp table to get all of the values I want from the configurator value set.

		SELECT PcValueHead.Company,
			   qh.quotenum,
			   qd.quoteline,
			   qd.partnum,
			   PcValueHead.GroupSeq,
			   PcValueGrp.RelatedToTableName,
			   PcValueGrp.RelatedToSysRowID,              
			   PcValueHead.ConfigID, 
			   PcValueHead.ConfigType,
			   PcValueHead.ConfigVersion, 
              FldValues.value('local-name(.)', 'nvarchar(50)') AS InputName,
			  FldValues.value('.', 'nvarchar(50)') AS InputValue, 
		      FldValues.value('(@Type)[1]', 'nvarchar(50)') AS DataType
        INTO #ASH_E10QUOTE_CONFIG_DATA_TEMP
		FROM  Ashworth.Erp.PcValueSet Cross apply FieldValues.nodes('/*/*/*') as FieldValues(FldValues)
                 INNER JOIN Ashworth.Erp.PcValueHead    
                 ON PcValueSet.Company = PcValueHead.Company 
                    AND PcValueSet.GroupSeq = PcValueHead.GroupSeq
                    AND PcValueSet.HeadNum = PcValueHead.HeadNum
				 INNER JOIN Ashworth.Erp.PcValueGrp
                 ON PcValueSet.Company = PcValueGrp.Company 
                    AND PcValueSet.GroupSeq = PcValueGrp.GroupSeq	
				JOIN Ashworth.erp.quotedtl qd on qd.SysRowID=PcValueGrp.RelatedToSysRowID
				Join Ashworth.erp.quotehed qh on qh.company=qd.company and qh.quotenum=qd.quotenum
		where --OPTIONAL -> qh.quotenum = 44038--44336 and
			RelatedToTableName = 'QuoteDtl'
			and FldValues.value('local-name(.)', 'nvarchar(50)') not in ('GroupSeq','HeadNum','PageSeq','ValueSetSeq','InputName')
			and FldValues.value('local-name(.)', 'nvarchar(50)') in 
					('DECWIDTH','DECWIDTHM','DECTURNRADIUS','DECNUMBERLOOPS','DECSTDLOOPCT','CMBAPPLICATION'
					,'CHRMESH','CHRMESHID','CMBEXPEDITE','CMBEDGETREAT','CMBBARLINKMAT','CMBRODLINKMAT','CHRMESHMAT','CMBMESHMAT2','CMBSTRIPMATERIAL'
					,'CMBRODLINKMAT','CMBRODMAT','decmaterialcost','declaborcost','decburden','dectotalcost','RADSPFEATYN','RADFLATTENED'
					,'CHKFATIQUE','DEVIATION','CHKREVERSEPICKET','ChkLaneDivider','chkLDIVDetach','chkLDIVnonDetach','ChkGuardEdge','ChkGeEdge'
					,'ChkBore','ChkSqrBore','DecBoreSize','CmbDiameter','DecDiameter','CmbDrive','ChkKeyWay','CmbMaterial','chrmaterial','ChkSetScrew'
					,'RadUnilBal','DecCageDia','chkhanger','DecTurnRatio','chklifts','DecCageDia','chkmonoplaner','ChrBelt','decsuramt','dectaramt'
					,'CHKCOSTSHEET','DecSetScrew','CmbTeeth','CHKSQRBORE')
			and Qd.company in ('yourcompany')

Followed by a series of update/selects to build a table where all my config values are parsed out.

		--GET THE WIDTHS FOR BOTH IMPERICAL AND METRIC
			UPDATE T SET T.WIDTH_I  = P.INPUTVALUE  
			--SELECT distinct P.INPUTVALUE  
				FROM ASHWORTHUTILITY.DBO.ASH_E10QUOTE_CONFIG_MEASURES AS T INNER JOIN #ASH_E10QUOTE_CONFIG_DATA_TEMP AS P ON 
					T.COMPANY = P.COMPANY AND	T.QUOTENUM = P.QUOTENUM AND	T.QUOTELINE = P.QUOTELINE
					AND	T.PARTNUM = P.PARTNUM AND P.INPUTNAME IN ('DECWIDTH')

			UPDATE T SET T.WIDTH_M  = P.INPUTVALUE  
			--SELECT distinct P.INPUTVALUE
				FROM ASHWORTHUTILITY.DBO.ASH_E10QUOTE_CONFIG_MEASURES AS T INNER JOIN #ASH_E10QUOTE_CONFIG_DATA_TEMP AS P ON 
					T.COMPANY = P.COMPANY AND	T.QUOTENUM = P.QUOTENUM AND	T.QUOTELINE = P.QUOTELINE
					AND	T.PARTNUM = P.PARTNUM AND P.INPUTNAME IN ('DECWIDTHM')

Hope that gets you started with your query.
Mike

1 Like

Thanks @Mark_Wonsil
Duplicating the data into a UD table from a configurator doc rule or UDmethod would be great.

Right now I have a UDmethod that creates a string from all the configurator input values and a doc rule that pushes the string to QuoteDtl.QuoteComment. This works for displaying the configurator data on a report, but I would really like to store the data in a table for future reference.

I’m not familiar with UD tables in E10. Is this the correct method to use?:

  1. use UD column maintenance to add columns to a table (eg. QuoteHed)
  2. regenerate the data model
  3. use a configurator doc rule (or UDmethod?) to push the values (eg. QuoteHed.TireSize_c = Inputs.TireSize.Value; )

If you are on 10.2.500 you can hit this data with a standard BAQ or any other method. No external view or anything special. Example code below if you wanted to get it from a server side method.

foreach (var PCValueSetRecord in (from row in Db.PcValueSet where row.GroupSeq == GroupSeq
select row))
{
  string XMLValue = PCValueSetRecord.FieldValues;
  XmlReader xr = XmlReader.Create(new StringReader(XMLValue));
  var xMembers = from members in XElement.Load(xr).Elements() select members;
  var children = xMembers.Elements();
  var AllElements = children.Where(e => e.Value != string.Empty).Select(e => new 
  {
    Name = e.Name.ToString(), // Control Name
    Value = e.Value.ToString(), // Input Value
    Type = (string)e.Attribute("Type") // Type (i.e. System.Int32)
  }).ToList();
4 Likes

Just upgraded to 10.2.500 a few weeks ago and I didn’t get the memo! Thanks @Rich!!! And thank you @danbedwards for setting me straight!

Mark W.

1 Like

Your process is correct - BUT I would extend the QuoteDtl table instead, so that the values line up with the line item in case there is more than one configured item on the quote.

@danbedwards We have recently upgraded to 10.2.500. I created a BAQ using PcValueSet linked to QuoteDtl and it displays the configurator values in PcValueSet_FieldValues as an xml string. Is there any way to convert that string into columns of separate configurator input values?

Yes there is. I can post an example later today - if someone doesn’t beat me to it :slight_smile:

1 Like

Won’t be me. I just learned this was possible! :rofl:

2 Likes

Very interested in this as well… Only way I was able to achieve it is through a direct SQL query using OUTER APPLY and SQL Server’s XML facilities, one field at a time, something like this:

SELECT …, QtyVal.val.value(‘.’, ‘decimal(22,8)’) as ‘QtyBtes’
FROM QuoteDtl qd
LEFT JOIN Erp.PcValueSet vs ON vs.Company = qd.Company
AND vs.GroupSeq = qd.GroupSeq
AND vs.ConfigID = ‘CFG-BTE’
OUTER APPLY vs.FieldValues.nodes(‘*/*/VARNUMBTEQTE’) AS QtyVal(val)