CODEWITHSUNDEEP
coldfusion
aparker81
aparker81

Reputation: 263

Add list of Query elements to a single Array element

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.

Upvotes: 1

Views: 1681

Answers (4)

bpanulla
bpanulla

Reputation: 2998

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.

Upvotes: 1

bpanulla
bpanulla

Reputation: 2998

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),' ')

Upvotes: 0

Dan A.
Dan A.

Reputation: 2924

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.

Upvotes: 2

Mark A Kruger
Mark A Kruger

Reputation: 7193

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 :)

Upvotes: 2

Related Questions