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?:
- use UD column maintenance to add columns to a table (eg. QuoteHed)
- regenerate the data model
- 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 
1 Like
Won’t be me. I just learned this was possible! 
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)
Hey all - I am a little late to respond on this thread but I wanted you to know that in 10.2.600 we added a SQL View to the system - same View as provided by Tech Support but different name so as to not conflict - and we have updated the BAQ and the zData so that Product Configurator data is available via a Standard BAQ without having to work with the raw XML.
5 Likes
In 10.2.500, the raw XML fields are available. So while you wait for 10.2.600, check out this free course in April to learn how to manipulate XML (and JSON) in SQL:
https://www.pluralsight.com/courses/querying-json-xml-temporal-data-tsql
Whats the name of the tablet to add to a BAQ to get this information?
Table name for use in the BAQ is Erp.PcInputValue.
