Re: How to perform a single search of multiple items and return all the matching records???
David this worked out great so thanks. One new wrinkle that was introduced the other day. Not only do they want to see every ISBN that matches, they want to see every ISBN that does not match. Any ideas? -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of David Sanders Sent: Monday, July 24, 2006 4:39 PM To: arslist@ARSLIST.ORG Subject: Re: How to perform a single search of multiple items and return all the matching records??? Hi Brian I think you should be able to do this with a simple table field qualification. Say you have a form with a field containing the string of ISBN values in a field called ISBN List, and a table field pointing to your book records which have an ISBN data field. Normally for a table field you would have a qualification like ( 'DataTableField' LIKE ( $CurrentFormField$ + % )). In this case you need to turn the qualification round, like $ISBN List$ LIKE (( % + 'ISBN') + % ) To allow the search to ignore the dashes, you would need a 'shadow' field on your book asset record (set by a filter) containg a stripped version of the ISBN. Then you could change the table field qualification to ($ISBN List$ LIKE (( % + 'ISBN') + % )) OR ($ISBN List$ LIKE (( % + 'ISBNStripped') + % )) It won't be the most efficient query, but it should work HTH David Sanders Remedy Solution Architect Enterprise Service Suite @ Work == ARS List Award Winner 2005 Best 3rd party Remedy Application tel +44 1494 468980 mobile +44 7710 377761 email [EMAIL PROTECTED] web http://www.westoverconsulting.co.uk 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
Re: How to perform a single search of multiple items and return all the matching records???
For anyone interested who had the attachment blocked, here is a link to the sample def. http://www.westoverconsulting.co.uk/downloads/sampleISBNSearch.zip Regards David Sanders Remedy Solution Architect Enterprise Service Suite @ Work == ARS List Award Winner 2005 Best 3rd party Remedy Application tel +44 1494 468980 mobile +44 7710 377761 email [EMAIL PROTECTED] web http://www.westoverconsulting.co.uk -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of David Sanders Sent: Friday, September 08, 2006 6:26 PM To: arslist@ARSLIST.ORG Subject: Re: How to perform a single search of multiple items and return all the matching records??? Hi Brian Here's a small sample def to show how this might be done. Basically I added a selection field with values of Matches/Non-Matches and changed the table field qualification accordingly. HTH David Sanders Remedy Solution Architect Enterprise Service Suite @ Work == ARS List Award Winner 2005 Best 3rd party Remedy Application tel +44 1494 468980 mobile +44 7710 377761 email [EMAIL PROTECTED] web http://www.westoverconsulting.co.uk -Original Message- From: Sokol, Brian [mailto:[EMAIL PROTECTED] Sent: Friday, September 08, 2006 4:37 PM To: arslist@ARSLIST.ORG Cc: David Sanders Subject: RE: How to perform a single search of multiple items and return all the matching records??? David this worked out great so thanks. One new wrinkle that was introduced the other day. Not only do they want to see every ISBN that matches, they want to see every ISBN that does not match. Any ideas? -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of David Sanders Sent: Monday, July 24, 2006 4:39 PM To: arslist@ARSLIST.ORG Subject: Re: How to perform a single search of multiple items and return all the matching records??? Hi Brian I think you should be able to do this with a simple table field qualification. Say you have a form with a field containing the string of ISBN values in a field called ISBN List, and a table field pointing to your book records which have an ISBN data field. Normally for a table field you would have a qualification like ( 'DataTableField' LIKE ( $CurrentFormField$ + % )). In this case you need to turn the qualification round, like $ISBN List$ LIKE (( % + 'ISBN') + % ) To allow the search to ignore the dashes, you would need a 'shadow' field on your book asset record (set by a filter) containg a stripped version of the ISBN. Then you could change the table field qualification to ($ISBN List$ LIKE (( % + 'ISBN') + % )) OR ($ISBN List$ LIKE (( % + 'ISBNStripped') + % )) It won't be the most efficient query, but it should work HTH David Sanders Remedy Solution Architect Enterprise Service Suite @ Work == ARS List Award Winner 2005 Best 3rd party Remedy Application tel +44 1494 468980 mobile +44 7710 377761 email [EMAIL PROTECTED] web http://www.westoverconsulting.co.uk 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
Re: How to perform a single search of multiple items and return all the matching records???
Hi David, I tried this and it would not return anything if I put the ISBN List field first in the qualification. If I reverse the order it will return an answer but only if there is a single entry in the ISBN List field. If I enter more than 1 value it does not return any records. -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of David Sanders Sent: Monday, July 24, 2006 4:39 PM To: arslist@ARSLIST.ORG Subject: Re: How to perform a single search of multiple items and return all the matching records??? Hi Brian I think you should be able to do this with a simple table field qualification. Say you have a form with a field containing the string of ISBN values in a field called ISBN List, and a table field pointing to your book records which have an ISBN data field. Normally for a table field you would have a qualification like ( 'DataTableField' LIKE ( $CurrentFormField$ + % )). In this case you need to turn the qualification round, like $ISBN List$ LIKE (( % + 'ISBN') + % ) To allow the search to ignore the dashes, you would need a 'shadow' field on your book asset record (set by a filter) containg a stripped version of the ISBN. Then you could change the table field qualification to ($ISBN List$ LIKE (( % + 'ISBN') + % )) OR ($ISBN List$ LIKE (( % + 'ISBNStripped') + % )) It won't be the most efficient query, but it should work HTH David Sanders Remedy Solution Architect Enterprise Service Suite @ Work == ARS List Award Winner 2005 Best 3rd party Remedy Application tel +44 1494 468980 mobile +44 7710 377761 email [EMAIL PROTECTED] web http://www.westoverconsulting.co.uk 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
Re: How to perform a single search of multiple items and return all the matching records???
Hi Brian, Yep, if you have several ISBN's in one field and try to run search for 'ISBNinRemoteForm' LIKE (( % + $ISBNtoSearch$) + %) it won't work. I would do it differently: 1) Create a transaction form (Book Search), in which one record is created per Query 2) Behind that form, build a work flow that: * assigns a unique ID to each search * walk through the ISBNtoSearch field and generate one record per ISBN-number in a second form (i.e. Book Search Request storing the unique search ID there as key) 3) Generate a join form between the Book Search Request form and the one where all known ISBN's are stored 4) Create a table and using the join form created in step 3 to present only records which exist in both forms for that specific search 5) Create i.e. an escalation which cleans up and removed searches search requests older then 24h George On 7/25/06, Sokol, Brian [EMAIL PROTECTED] wrote: Hi David, I tried this and it would not return anything if I put the ISBN List field first in the qualification. If I reverse the order it will return an answer but only if there is a single entry in the ISBN List field. If I enter more than 1 value it does not return any records. -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of David Sanders Sent: Monday, July 24, 2006 4:39 PM To: arslist@ARSLIST.ORG Subject: Re: How to perform a single search of multiple items and return all the matching records??? Hi Brian I think you should be able to do this with a simple table field qualification. Say you have a form with a field containing the string of ISBN values in a field called ISBN List, and a table field pointing to your book records which have an ISBN data field. Normally for a table field you would have a qualification like ( 'DataTableField' LIKE ( $CurrentFormField$ + % )). In this case you need to turn the qualification round, like $ISBN List$ LIKE (( % + 'ISBN') + % ) To allow the search to ignore the dashes, you would need a 'shadow' field on your book asset record (set by a filter) containg a stripped version of the ISBN. Then you could change the table field qualification to ($ISBN List$ LIKE (( % + 'ISBN') + % )) OR ($ISBN List$ LIKE (( % + 'ISBNStripped') + % )) It won't be the most efficient query, but it should work HTH David Sanders Remedy Solution Architect Enterprise Service Suite @ Work == ARS List Award Winner 2005 Best 3rd party Remedy Application tel +44 1494 468980 mobile +44 7710 377761 email [EMAIL PROTECTED] web http://www.westoverconsulting.co.uk 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
Re: How to perform a single search of multiple items and return all the matching records???
It does work George. Here's a small def file and data in a zip file to demonstrate. Rename the attachment to .zip extension. David Sanders Remedy Solution Architect Enterprise Service Suite @ Work == ARS List Award Winner 2005 Best 3rd party Remedy Application tel +44 1494 468980 mobile +44 7710 377761 email [EMAIL PROTECTED] web http://www.westoverconsulting.co.uk -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of George Barsan Sent: Tuesday, July 25, 2006 2:18 PM To: arslist@ARSLIST.ORG Subject: Re: How to perform a single search of multiple items and return all the matching records??? Hi Brian, Yep, if you have several ISBN's in one field and try to run search for 'ISBNinRemoteForm' LIKE (( % + $ISBNtoSearch$) + %) it won't work. I would do it differently: 1) Create a transaction form (Book Search), in which one record is created per Query 2) Behind that form, build a work flow that: * assigns a unique ID to each search * walk through the ISBNtoSearch field and generate one record per ISBN-number in a second form (i.e. Book Search Request storing the unique search ID there as key) 3) Generate a join form between the Book Search Request form and the one where all known ISBN's are stored 4) Create a table and using the join form created in step 3 to present only records which exist in both forms for that specific search 5) Create i.e. an escalation which cleans up and removed searches search requests older then 24h George On 7/25/06, Sokol, Brian [EMAIL PROTECTED] wrote: Hi David, I tried this and it would not return anything if I put the ISBN List field first in the qualification. If I reverse the order it will return an answer but only if there is a single entry in the ISBN List field. If I enter more than 1 value it does not return any records. -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of David Sanders Sent: Monday, July 24, 2006 4:39 PM To: arslist@ARSLIST.ORG Subject: Re: How to perform a single search of multiple items and return all the matching records??? Hi Brian I think you should be able to do this with a simple table field qualification. Say you have a form with a field containing the string of ISBN values in a field called ISBN List, and a table field pointing to your book records which have an ISBN data field. Normally for a table field you would have a qualification like ( 'DataTableField' LIKE ( $CurrentFormField$ + % )). In this case you need to turn the qualification round, like $ISBN List$ LIKE (( % + 'ISBN') + % ) To allow the search to ignore the dashes, you would need a 'shadow' field on your book asset record (set by a filter) containg a stripped version of the ISBN. Then you could change the table field qualification to ($ISBN List$ LIKE (( % + 'ISBN') + % )) OR ($ISBN List$ LIKE (( % + 'ISBNStripped') + % )) It won't be the most efficient query, but it should work HTH David Sanders Remedy Solution Architect Enterprise Service Suite @ Work == ARS List Award Winner 2005 Best 3rd party Remedy Application tel +44 1494 468980 mobile +44 7710 377761 email [EMAIL PROTECTED] web http://www.westoverconsulting.co.uk 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 ___ UNSUBSCRIBE or access ARSlist Archives at http://www.wwrug.org PK yù4.| Ñj
Re: How to perform a single search of multiple items and return all the matching records???
Thanks David, you're totally right! Sorry, I was thinking too complicated. On 7/25/06, David Sanders [EMAIL PROTECTED] wrote: It does work George. Here's a small def file and data in a zip file to demonstrate. Rename the attachment to .zip extension. David Sanders Remedy Solution Architect Enterprise Service Suite @ Work == ARS List Award Winner 2005 Best 3rd party Remedy Application tel +44 1494 468980 mobile +44 7710 377761 email [EMAIL PROTECTED] web http://www.westoverconsulting.co.uk -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of George Barsan Sent: Tuesday, July 25, 2006 2:18 PM To: arslist@ARSLIST.ORG Subject: Re: How to perform a single search of multiple items and return all the matching records??? Hi Brian, Yep, if you have several ISBN's in one field and try to run search for 'ISBNinRemoteForm' LIKE (( % + $ISBNtoSearch$) + %) it won't work. I would do it differently: 1) Create a transaction form (Book Search), in which one record is created per Query 2) Behind that form, build a work flow that: * assigns a unique ID to each search * walk through the ISBNtoSearch field and generate one record per ISBN-number in a second form (i.e. Book Search Request storing the unique search ID there as key) 3) Generate a join form between the Book Search Request form and the one where all known ISBN's are stored 4) Create a table and using the join form created in step 3 to present only records which exist in both forms for that specific search 5) Create i.e. an escalation which cleans up and removed searches search requests older then 24h George On 7/25/06, Sokol, Brian [EMAIL PROTECTED] wrote: Hi David, I tried this and it would not return anything if I put the ISBN List field first in the qualification. If I reverse the order it will return an answer but only if there is a single entry in the ISBN List field. If I enter more than 1 value it does not return any records. -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of David Sanders Sent: Monday, July 24, 2006 4:39 PM To: arslist@ARSLIST.ORG Subject: Re: How to perform a single search of multiple items and return all the matching records??? Hi Brian I think you should be able to do this with a simple table field qualification. Say you have a form with a field containing the string of ISBN values in a field called ISBN List, and a table field pointing to your book records which have an ISBN data field. Normally for a table field you would have a qualification like ( 'DataTableField' LIKE ( $CurrentFormField$ + % )). In this case you need to turn the qualification round, like $ISBN List$ LIKE (( % + 'ISBN') + % ) To allow the search to ignore the dashes, you would need a 'shadow' field on your book asset record (set by a filter) containg a stripped version of the ISBN. Then you could change the table field qualification to ($ISBN List$ LIKE (( % + 'ISBN') + % )) OR ($ISBN List$ LIKE (( % + 'ISBNStripped') + % )) It won't be the most efficient query, but it should work HTH David Sanders Remedy Solution Architect Enterprise Service Suite @ Work == ARS List Award Winner 2005 Best 3rd party Remedy Application tel +44 1494 468980 mobile +44 7710 377761 email [EMAIL PROTECTED] web http://www.westoverconsulting.co.uk 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
Re: How to perform a single search of multiple items and return all the matching records???
I tend to think that in Remedy, anything is possible, just some things are more difficult than others. Would your users be ok with entering the ISBNs individually (i.e. in a dialog), clicking a button to send it to another form, and displaying the currently entered ISBNs in a table field? Then you could do the data validation against those table entries, and run a search that will display the matched records in another table. Maybe add a quick print or report capability to that, and it seems like it might work. That what you had in mind? Rick -Original Message- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Sokol, Brian Sent: Monday, July 24, 2006 12:43 PM To: arslist@ARSLIST.ORG Subject: How to perform a single search of multiple items and return all the matching records??? 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
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
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: arslist@ARSLIST.ORG 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
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: arslist@ARSLIST.ORG 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: arslist@ARSLIST.ORG 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
