Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

Beginner Question - How to make a collection based on SQL query results

Created: 20 Jun 2014 • Updated: 23 Jun 2014 | 4 comments
This issue has been solved. See solution.

So I have an initial SQL query that creates a list of jobs.  I then have another query that finds a list of outstanding purchase orders for each job.  What I can't figure out is how to add each iteration (Each list of purchase orders for a specific job) to a collection so that at the end I can print out the entire list of jobs and corresponding purchase orders.  Right now I am doing a For Each purchase order, map values, then add to collection, but it is not working.  Please see my attachment for a screen shot of the process.

I want the results to look like this on the webform grid:

Job Number 1

Outstanding PO 1

Outstanding PO 2

Outstanding PO 3

Etc. 

Job Number 2

Outstanding PO 1

Outstanding PO 2

Outstanding PO 3

Etc.

Can someone point me to what I'm missing?

Thanks.

Operating Systems:
Discussion Filed Under:

Comments 4 CommentsJump to latest comment

reecardo's picture

This should be able to be done via a mapping component... assuming there are a collection of POs per job, you can use a PO ProcessTypeMapping to create a mini-flow to manually associate the correct POs with each job.

The ProcessTypeMapping for the POs would look like Start-> (use a comp to look up POs by the job number -> End (where reults are the result of the query).

You could try doing the entire job collection mapping with a Multi Value Mapping component (this resembles a Single Value Mapping component, but is used to process entire collections vs single values)

romado's picture

Thanks reecardo, the Job number and the list of purchase orders come from two different databases, so I need two separate queries.  If you look at the image I attached, I am using a mapping component, but it still doesn't seem to work, though I don't completely understand the difference between single value mapping and multiple value mapping...

reecardo's picture

I don't have the tool in front of me, but I think you can put the PO "logic" into a ProcessTypeMapping of a Job mapping component.

I'm assuming you have a "Job" object, and the Job objects has a "POs" collection. In the mapping for the Job object, right click on the "POs" collection and select ProcessTypeAction. You can take that Raid PO DB/Mapping/Add to Collection logic and put it in here.

africo's picture

looks like the only issue Romado had was connecting with Firebird.  in order to pass parameters properly, we had to uncheck "Db Parameter", include apostrophe quotes in the actual query script, and set Quotes Type to "None".  We also cleared the cache and replaced the component after making changes.  All this combined did the trick; everything else was already configured properly.

-andrew

SOLUTION