Looping thru excell file

[flmngldn]

I am trying to create a report(8.5) where I have database and an excel file.
The database format:

Part# partdesc Qty cost ref
12345 Trays 5 10.00 535/538/537
78901 Pencils 8 0.80 123/456
56789 Paper 20 1.50 567

excel format:
serial# %cost
535 30
538 60
537 10
123 50
456 50

What the report does is split the ref field(/ deliminator) and cehck in the excel file for the serial# and get the %cost. The extended cost will be (cost*qty)/(%cost/100).
If there is no serila number match then the %cost is 100.
I have not done subreports but I think that is the best way to go. How would I create the array and have the serial lookup loop thru the excel file?

All the help is very much appreciated.

 

[herbsza]

Try the following
I have not tested this but it should work.

In a formula do the following

formula @test
replace ({ref},'/',',')

go to
change subreport links
select the formula @test

deselect (Select data in subreport based on field)

now use the parameter in the subreport where clause

serial# in [{?Pm-@test}]