Searching through list of files to match with list of SKUs

[snipe]

Hey guys,



I have been on this program for a week now and I have just hit a bad road block.

I have a table, that is pulled from ODBC, that lists all of my company's active packages. A package is 1 SKU that when typed into our point-of-sale system "explodes" into the individual SKUs that make up the package. For example, the PKB213QSB (B213 queen sleigh bed) explodes into B213-51 (headboard), B213-71 (footboard), and B213-94 (rails). These packages are created by my company.

I have another table, that is pretty much static. The manufacturer's pictures of their products get pulled from their FTP once every 3 months to our server. I have a small .exe program that creates a list of the files in that folder of the server, as well as attaches a URL to them. The pictures are labeled oddly.

Packages: Picture list:

This pictures give you an example of what the tables look like. The packages table lists the package sku, then the individual skus in that package. The next two columns are what I was working on: it separates the individual item (the last 2 numbers of our SKU) from the series. The directory table fields list a series, then a "-", then each of the items in that picture all separated by "-".

What I want is to link a picture to a package in a form. This will make a sort of catalog that I can quickly flip through. Once I get these linked, I can bring over a whole bunch of other data that I need (stock, pricing, back orders, etc).

Some of the pictures will contain most of the items in the package (not always all of them) and some will have even more items. Example: There isn't just a picture of the PKB213QSB that I mentioned, but there is a pic that includes the bed, nightstand, dresser and mirror.

Any ideas on how I can make a search that determines the best way to do this?

[June7]

It appears the URL has the Series and Item number embedded, and think I see examples with multiple Items in the URL. Seems to complicate but the hyphenation might be quite helpful. To retrieve the URL with the ActivePackage record in a query or on a form or report, try a calc like:
DLookup("URL","Dirlist","Left([filename],5)='" & [Series] & "' AND [filename] LIKE '*-" & [Item] & "*'")

Review these articles about hyperlinks:
http://allenbrowne.com/func-GoHyperlink.html
http://www.allenbrowne.com/casu-09.html

[snipe]

That was definitely a fantastic start! Thank you so much!

I can't really decipher what your criteria is doing in there though. Think you could put it in plain English for me?

[June7]

Translation of the DLookup expression:
Find first record in Dirlist where the first 5 characters of [filename] are equal to the value from [Series] AND [filename] contains the value from [Item]. I concatenate a hyphen in front of [Item] value so the characters won't match to characters in the Series part.

[snipe]

Ah ok, that makes sense. Thank you again. I tweaked it a bit and is doing what it should! I appreciate all the help!