I have a Query of a Table in CPQ. I declared the variable using the first column name (PartNo). In order to get the entire column, I did not specify distinct, first or top. I get the result I want but it looks like this:
{PartNo: ‘123456’} but I want 123456
Since I am grabbing the whole column, it is assigned to an array. I am using a For each loop to iterate and am calling each row with a variable and the Get Item # block from the array. Like I said, if I review the values in Debug, its there. i just need the ‘clean’ version of it like above. Am I attacking this the wrong way or am I using the wrong method?
How does eveyone else get data from a table to populate existing fields in CPQ>
When it comes to pulling data from tables into fields I probably overuse text - select and link to an option filter. However, I have never tried to pull the data as an Array. I know when I use my method it automatically does a distinct on the column I choose, but if you do successive fields with other values they sort of act like filtering selections so you can drill down (i.e. Make, Model, Trim for a Car).
I don’t know if that helps at all. Maybe if you give more information about your table and what you want to do with the data in the column I could give more advice?
Thanks for the reply. What I do is I pull in the data as an array and then use a For loop to iterate through the array. It works fine, but it comes as the Column Name: Value. So I ended up having to do a Replace on the Serialized to JSON string to arrive at my values. I’ve got it working but I thought there must be a better way to extract the actual value from the array. I can only put one image in a post as a ‘new user’. Function below in reply.
OK I am weak on this stuff and I don’t know your full use case, so take this all with a grain of salt.
We use a global rule to retrieve data from tables, but we only return one result. Also this rule was setup by Epicor CPQ staff, we are re-using their snap code.
Here is an example:
We don’t have to remove any text from this result, we have a WHERE statement. Not sure if that might help your situation? Could it be returning the header row?
One way we have started returning multiple part numbers when a selection is made on a text-select field is using a table cell with comma separated list of values, of course that leaves out details like qty or price. We have thought about doing things like a two dimensional array with like one set of values separated by commas and another set separated by semi colons. A cell might look like 900-00405;12,100-00010;7,350-09456;1, then we make one array using the comma as separator then inside those values then create a sub array using the semi colon. Might be crazy but then we could do BoM in a table. Not sure if that helps. Also I do a lot of hidden fields on forms and {ab}use Option filters on tables to have multiple values from a single Text - Select field and reference that field data in Value Rules.
Thanks for the info. I do use single result queries a lot for other fields but in this case I want to return the whole table (25-40 rows per table) and iterate through them. The only issue is I get:
The tables have two columns, with PartNo and Qty as headers and up to 45 rows each.
I’m workable with the method I came up with but seemed like it should spit it out without text manipulation (like the single return query you outlined). Just figured someone had come across this at some point!
I think the “PartNo:” is the field/column name. If you had a Type that you could serialize into that has a matching Property named “PartNo:” then maybe you can iterate through the array as an array of type and reference the “PartNo:” value in each array entry?!?!
