can't think of any way of doing it directly in monarch as it's external lookups are built to make unique matches, rather than pull back all possibilities - think of an excel vlookup versus an access query
It would be possible to build something in vb to pull in both sets of data and output a completed list with all matching combinations, but in order to do even this, each file would have to include a "key" field
In general terms your vbscript would need to:
Open both files in turn, reading contents into arrays
Loop through "Report" array picking up Color and Name
Loop though "External File" array picking up Color and Number
Check for each "report" and "external" entry that Color from both arrays match, if so populate a third array with NameColorNumber "combination"
Write out third array to a seperate text file - use this in a monarch process as your "Key file" and hang the original report/process off this list which contains all combinations even if they are present or not.
Bill, I took [I]exactly /I[/B]that approach in developing the Excel VBA code below last week.
I just didn't post it then as I was still trying (unsuccessfully, in the end) to find a way with Monarch.
Dim aNames() As Variant
Dim aColours() As Variant
Dim iNames As Integer
Dim iColours As Integer
Dim iOutputRow As Integer
Dim sName As String
Dim sColour As String
Dim vNumber As Variant
iOutputRow = 0
aNames() = Range("tblNames")
aColours() = Range("tblColors")
For iNames = 1 To UBound(aNames)
sName = aNames(iNames, 1)
sColour = aNames(iNames, 2)
For iColours = 1 To UBound(aColours)
If aColours(iColours, 1) = sColour Then
vNumber = aColours(iColours, 2)
iOutputRow = iOutputRow + 1
Range("H" & CStr(2 + iOutputRow)).Value = aNames(iNames, 1)
Range("I" & CStr(2 + iOutputRow)).Value = sColour
Range("J" & CStr(2 + iOutputRow)).Value = vNumber
Using Monarch alone (without programming) an approach along these lines could work provided the number of numbered codes per color is not too large.
Firstly, using Monarch, create a new version of the External Lookup file converted and formatted like this. (CSV appearnce is just for interpretation.)
"Blue","123 456 896"
Export this for use as the lookup using the 'number' field as a single field array.
Link this to the Monarch table.
Bob Blue "123 456 896"
Peter Blue "123 456 896"
Denny Blue "123 456 896"
Jane Blue "123 456 896"
" " used to denote that this will be a text field.
Export this as a new report file (ie a Text file) with fixed format to give a columnar appearnce.
Open the new file in Monarch and apply MCR to the template with, in the example, 3 columns for the 'numbers' field and that should provide your desired output.
Depending in what you are working with as starting points there may be some shortcuts available to you - or there may be some as yet unidentified aspects of the data that make this approach inappropriate. However if it does work and is approproate you could also put the entire process into a batch file to be executed as a single activity once you are comortable that it all works as required.
If the lookup file is a (relatively) fixed result (ie. it does not need to be generated dynamically every time the report is analysed) that would make things even easier since the first step would not need to be run every time. However if the lookup does change, and the changes may not be communicated in a 'timely' fashion, then running that step through the batch every time the process is run would probabaly be a safer option.
HTH with some ideas.
I'm late to this party. Your solution via Monarch is always to start with the many results as your main project and do the lookup against the first table. Use a filter to remove any not matches, this will give you everything you need without any VB.
I do this type of monarch projects almost all day, I deal with actions against accounts where there can be many actions but only 1 account. I will start with the action do the lookup against the account.