Brian, Another approach would be to populate a temp SQL table from workflow containing the ISBNs from the user. A SQL View could be constructed to match the temp table entries with the assets. A Remedy View could be based on the SQL View. Finally, a table field would be used to display the results to the user... for an unlimited number of matches.
A general overview: 1. User opens screen. 2. A GUID is generated and stored in a temp field. 3. User copies the spreadsheet column containing the ISBNs and pastes into a 0 length display only field. 4. User clicks a Search button. 5a. Active link begins chomping through each ISBN, building a string in another temp field up to the maximum length that your DBMS can handle. In SQL Server 2000 each varchar parameter can be up to 8000 characters in length. 5b. For each chunk of ISBNs (each ISBN delimited with some character such as a comma) call your SQL stored procedure that populates the temp SQL table with the GUID and the list of ISBNs. 5c. Stored procedure inserts records into the temp SQL table, one record per ISBN. The temp SQL table would have two fields - one for the GUID and one for a single ISBN. 5d. The active link loops back up to the top (# 5a above) and gets the next chunk of ISBNs, and so on until there are no more to process. 6. Once the temp SQL table is populated Refresh the table field on the user's screen. Comments: The table field qualification would be GUID = GUID. This allows multiple users to run the same process without interfering with each other. Each time the user clicks the Search button issue a Direct SQL command to DELETE FROM MySQLTempTable WHERE GUID = GUID. Then, issue this command once more when the form closes. This will keep the temp SQL table empty except while in use. The SQL View would be a Join between the temp SQL table and your asset form. Just be sure to expose the GUID column so that you can use it in your table field on the users' screen. To walk through a field, search for a character that delimits an ISBN using STRSTR(). If copied from a spreadsheet column it will likely be a Return or Return+LineFeed. For each ISBN add it to another temp field separated with a comma (or another character - commas should work because ISBNs do not contain commas). After you have added an ISBN to the other temp field remove the ISBN from the list of all ISBNs using SUBSTR() and the position of the delimiter + 1. Note: Until you get this process working hold off using the GoTo command to loop back up to the top because most likely the first few tries the workflow will go into an infinite loop. Not a big deal, you just have to shut down the User Tool and restart. Let me know if you need more details. Stephen -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Sokol, Brian Sent: Monday, July 24, 2006 4:30 PM To: [email protected] Subject: Re: How to perform a single search of multiple items and return all the matching records??? Cary/Rick, The way it works now is a user sends a request for x# of ISBN records listed in a spreadsheet. Staff here copy and paste the ISBN column from the spreadsheet into a single field. They then search for all the hits from the single field against our database. The spreadsheet could have anywhere from a few ISBN numbers up to around 2,000. Not sure how to walk the string? -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Rick Cook Sent: Monday, July 24, 2006 4:25 PM To: [email protected] Subject: Re: How to perform a single search of multiple items and return all the matching records??? Yeah, EXTERNAL would work, too. Regardless of the solution used, the most difficult part is going to be delimiting and validating the data string. The users are going to have to do some of that themselves, as you can't practically screen every potential problem type out with workflow. Rick -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Carey Matthew Black Sent: Monday, July 24, 2006 1:03 PM To: [email protected] Subject: Re: How to perform a single search of multiple items and return all the matching records??? Brian, Well... I see an option here.... How about you walk the string? If you can tell your users that every ISBN number must be on their own line (hard return separated) then you could use a guide to loop over a character field and "fix up" all the sub strings and at the same time produce a nice string for use in an EXTERNAL operator for a Table field. The only caution I have for you is that the length of an EXTERNAL operator is limited.(4k if memory serves) So if the list of ISBN's is long enough, and the syntax for each value is long enough the total string may not be possible to do in this way. However if an ISBN is 20 characters long and your field ID is 10 digits with no spaces between the quotes (like '1234567890'="value_20_01234567890") with an OR in there if there is another value yet to add to the list... would bring the length per ISBN to be 38. So with a 4k limit they would need to do a search on 106 separate ISBN numbers to break the length limit. If I did all my math and ASB syntax right. :) So how long of a list are we talking about? -- Carey Matthew Black Remedy Skilled Professional (RSP) ARS = Action Request System(Remedy) Solution = People + Process + Tools Fast, Accurate, Cheap.... Pick two. Never ascribe to malice, that which can be explained by incompetence. On 7/24/06, Sokol, Brian <[EMAIL PROTECTED]> wrote: > I have been asked to replace a Filemaker Pro database with a Remedy > application. We are a book publisher and this application is used to > request digital assets by their ISBN number. Each asset has their own > unique ISBN number. In Filemaker they can cut and paste a long list of > ISBN numbers and search using that list. The results will show every > match made against a table of about 60K records. Additionally the ISBN > list may be formatted with dashes or unformatted with just the number. > > Is something like this possible? > > Brian Sokol > Manager, Desktop Services > Scholastic Inc. > 212-343-7698 > [EMAIL PROTECTED] > <http://www.scholastic.com> > > ______________________________________________________________________ > _________ UNSUBSCRIBE or access ARSlist Archives at > http://www.wwrug.org > ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org ________________________________________________________________________ _______ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org
