Add list of Query elements to a single Array element

Question

I am looping through a query to create a list of part numbers to populate a list of part numbers:

<cfset binlist = "" >
<cfset a = 1 />
    <cfloop query="getParts">
    <cfif a >
        <cfset binlist = getParts.binnum>
        <cfset a = 2 >
    <cfelse>
        <cfset binlist = binlist & "," & getParts.binnum >
    </cfif>
    </cfloop>

I want to add the binlist element to an element of an array in order to populate a spreadsheet:

<cfset aColumns = [ partnum,  shortchar08, partdescription, binlist, inventory.currinv , staged.stagedqty, alloc.allocqty, available, shelfCount, shipdtl.shipqty, getNumberofStores.numStores, tordered, APS, paddedLeadTime, LWM, storesRemain] />

<!---add the column data to the spreadsheet--->
<cfset SpreadsheetAddRow(SpreadsheetObj, ArrayToList(aColumns)) />

1 part can be in multiple bins. It works in my CF output page, but not in the spreadsheet that I am trying to generate to show the multiple bins for a part

The spreadsheet generated only contains one bin per part and not multiple bins for the parts that do have multiple bins.

I'm not following what exactly you're trying to do? Also, if you can display the code in html, you can cheat and send the html to excel using cfcontent. Additionally you can simply if your initial logic a lot using ValueList() or ListAppend() if you want to do other logic inside the loop still. — Busches
– Busches, Commented Mar 1, 2012 at 17:28
Thanks for the response, Bushes, however, the binnum is not getting limited to the specific part. — aparker81
– aparker81, Commented Mar 1, 2012 at 17:58

Dan A. · Accepted Answer · 2012-03-01 18:08:10Z

I think (if I understand your logic) what you are attempting to do is simply one line of code in Coldfusion:

<cfset binList = ValueList(getParts.binnum) />

Upon looking at your code further, it looks like you are embedding a list into an Array, and then converting the array to a list. If you have a comma-separated list, then stick that in the middle of another comma-separated list, they're going to just be interpreted as individual elements rather than a set.

See what happens if you change your delimiter to something other than a comma so that it doesn't get confused with the larger list:

<cfset binList = ValueList(getParts.binnum, ';') />

Update:

OK, I see you have a part grouping problem. Let me update my solution:

<!--- loop over unique parts -->
<cfoutput query="getParts" group="partnum">
    <cfset binlist = "" > 
    <!--- loop over bin numbers for each part --->
    <cfoutput>
        <cfset binlist = ListAppend( binlist, getParts.binnum, ';' ) />
    </cfoutput>
    <!--- do row level stuff here --->
    <cfset aColumns = [ partnum,  shortchar08, partdescription, binlist, inventory.currinv , staged.stagedqty, alloc.allocqty, available, shelfCount, shipdtl.shipqty, getNumberofStores.numStores, tordered, APS, paddedLeadTime, LWM, storesRemain] />

    <!---add the column data to the spreadsheet--->
    <cfset SpreadsheetAddRow(SpreadsheetObj, ArrayToList(aColumns)) />

</cfoutput>

Basically, you don't mix CFOUTPUT and CFLOOP. If you're using CFOUTPUT to group, the inner CFOUTPUT is needed (minus the group parameter) to get your inner grouping.

Mark A Kruger · Accepted Answer · 2012-03-01 17:31:17Z

2

You are reinventing the wheel. valuelist(getparts.binnum) will give you your list without all that looping and checking. Or at the very least you could use "listAppend()" to avoid all that comma checking :)

answered Mar 1, 2012 at 17:31

Mark A Kruger

7,19322 silver badges21 bronze badges

2 Comments

aparker81 Over a year ago

Thanks, Mark. I have tried this, yet it seems to be pulling every bin and not limiting the bins to the specific partnums. I'm trying:

<cfoutput query="getParts" group="partnum">   <cfset binlist = "" > <cfloop query="getParts"> <cfset binlist = ListAppend( binlist, getParts.binnum ) /> </cfloop>

Mark A Kruger Over a year ago

Your code above doesn't limit them either... the loop code simply appends part num's to a manually created list. IF you want a quick and easy way to filter the part nums then try using query of a query: AS in <cfquery name="qryPartNum" dbtype="query">select partnum from getParts where partnum <> '' OR ... add conditions here </query>

bpanulla · Accepted Answer · 2012-03-01 18:25:00Z

If your query contains only the rows for a single part, the ValueList() function is probably the way to go here. You may need to use a different delimiter for your bin list to keep them separate from the other items in the array that you pass to SpreadSheetAddRow:

<!--- Second argument uses space as delimiter --->
<cfset binlist = ValueList(getParts.binnum, " ") >

<!---add the column data to the spreadsheet--->
<!--- Explicitly use comma as delimiter --->
<cfset SpreadsheetAddRow(SpreadsheetObj, ArrayToList(aColumns, ",")) />

It also sounds like you're having trouble partitioning your query into sets by part. So maybe the ValueList won't do it for you without using Query of Query (QoQ). If you're already using a grouped cfoutput, just collect up the part/binlist mapping in a Struct:

<cfset part_bins = structNew()>
<cfoutput query="getParts" group="partnum">
  <cfoutput> <!--- partnum group --->
    <cfif structKeyExists(part_bins, getParts.partnum)>
      <cfset part_bins[getParts.partnum] = listAppend(part_bins[getParts.partnum], getParts.binnum, " ")>
    <cfelse>
      <cfset part_bins[getParts.partnum] = getParts.binnum>
    </cfif>
  <cfoutput>
</cfloop>

Then pull the bin list from the struct when you make your array:

<cfset aColumns = [ partnum,  shortchar08, partdescription,
  part_bins[partnum], inventory.currinv , staged.stagedqty,
  alloc.allocqty, available, shelfCount, shipdtl.shipqty,
  getNumberofStores.numStores, tordered, APS, paddedLeadTime,
  LWM, storesRemain] />

You might need to handle cases where a part has no bins - that will throw a struct key exception.

bpanulla · Accepted Answer · 2012-03-01 18:22:06Z

0

You don't say what database platform you're using, but you might be able to accomplish this same trick using an aggregate function in SQL. For example, in MySQL:

SELECT partnum, GROUP_CONCAT(DISTINCT binnum SEPARATOR ' ')
FROM Parts
GROUP BY partnum

A similar function exists in PostgreSQL:

array_to_string(array_agg(binnum),' ')

answered Mar 1, 2012 at 18:22

bpanulla

2,9981 gold badge21 silver badges23 bronze badges

Collectives™ on Stack Overflow

Add list of Query elements to a single Array element

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related