| Author |
Message |
TorLang
Groupie
Joined: 13 Jun 2012
Location: United States
Online Status: Offline
Posts: 50
|
   Topic: Importing large amounts for selection
Posted: 04 Mar 2016 at 2:28am |
|
Quick question for you experts.
I need to use a large amount of account numbers as part of the selection for a data pull.
I realize that I can use a parameter, then import the accounts numbers as a txt file. However, I have about 30000 of them. Is there any other way to do this, besides moving over pages after pages of accounts in the multi selection parameter?
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Mar 2016 at 2:39am |
|
I am unclear what you are asking.
Are you asking how to create the possible values that populate a run-time crystal parameter? Or are you asking how to filter the content of a report using this 'list'?
|
|
IP Logged |
|
TorLang
Groupie
Joined: 13 Jun 2012
Location: United States
Online Status: Offline
Posts: 50
|
Posted: 04 Mar 2016 at 3:02am |
|
What I am asking is there another way getting thousands of account number into the report selection other than having to import a txt file into a parameter, then when running it, having to move pages upon pages of account numbers over to the right side in the paramete, due to the limitation of "multiple value" parameters where Crystal breaks up the values into pages.
Edited by TorLang - 04 Mar 2016 at 3:03am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Mar 2016 at 3:06am |
|
if you have the them in a table you can just use it as an inner join.
If you know the conditoins that got you the 'list' to begin with you can use those conditions on the where clause.
If you have the ablity to create a stored proc or a view you can import them into that.
You can use the formula editor in the select and just paste them all.
|
|
IP Logged |
|
TorLang
Groupie
Joined: 13 Jun 2012
Location: United States
Online Status: Offline
Posts: 50
|
Posted: 04 Mar 2016 at 3:12am |
|
Dan,
When you say past the 50000 accounts into the select statement, I dont follow how that can be done easily.
This is my current select:
{TPB200_BILLING_INVOICE.ivo_ext_id} = {?IVO}
IVO being the account # field parameter. How would I formulate this if I just paste the accounts into this selection?
By the way, they are string fields in the database table.
3000011160-0001
3000108940-0001
3000010904-0001
3000011241-0001
3000011257-0001
4000064516-0001
4000110787-0001
4000119659-0001
4000123798-0001
4000127062-0001
4000146968-0001
4000153694-0001
4000154807-0001
4000157969-0001
4000163825-0001
4000172407-0001
4000186686-0001
4000187662-0001
4000187911-0001
4000190336-0001
Edited by TorLang - 04 Mar 2016 at 3:14am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Mar 2016 at 3:16am |
|
So that was my original question.
Are you trying to populate the ?IVO list so a user can pick from this list to run a few a time? or are you trying to use all records from an '?IVO list' to limit a larger table to these 30,000 records that are part of an ?IVO list.
|
|
IP Logged |
|
TorLang
Groupie
Joined: 13 Jun 2012
Location: United States
Online Status: Offline
Posts: 50
|
Posted: 04 Mar 2016 at 3:20am |
|
I am using all the records from the ?IVO list to limit a larger table. I am having this issue pretty frequently where accounting people are asking for huge data pulls on selected accounts, and I just haven't found an easier way of getting these high numbers of accounts into the selection w/o using a parameter :-)
Edited by TorLang - 04 Mar 2016 at 3:20am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Mar 2016 at 3:21am |
|
How are they delivering the list to you? Word? Excel?
|
|
IP Logged |
|
TorLang
Groupie
Joined: 13 Jun 2012
Location: United States
Online Status: Offline
Posts: 50
|
Posted: 04 Mar 2016 at 3:24am |
|
Excel, with just a single column populated. I then dump them into a txt so I can import (into parameter selection)
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 04 Mar 2016 at 3:36am |
|
You might be able to just use the excel file to include as another source and use it to join to {TPB200_BILLING_INVOICE.ivo_ext_id}.
When they give you a new file do a save as and replace the joined excel file with a copy of the new list.
I have had issues with some text files doing this so test it out. If you can get it working it is the simplest to maintain.
Or
export the data into word
do a find and replace
find: ^l
replace with: ","
this would replace all of the line feeds with a ","
turning
3000011160-0001
3000108940-0001
3000010904-0001
3000011241-0001
3000011257-0001
4000064516-0001
4000110787-0001
4000119659-0001
4000123798-0001
4000127062-0001
4000146968-0001
4000153694-0001
4000154807-0001
4000157969-0001
4000163825-0001
4000172407-0001
4000186686-0001
4000187662-0001
4000187911-0001
4000190336-0001
into
3000011160-0001”,”3000108940-0001”,”3000010904-0001”,”3000011241-0001”,”3000011257-0001”,”4000064516-0001”,”4000110787-0001”,”4000119659-0001”,”4000123798-0001”,”4000127062-0001”,”4000146968-0001”,”4000153694-0001”,”4000154807-0001”,”4000157969-0001”,”4000163825-0001”,”4000172407-0001”,”4000186686-0001”,”4000187662-0001”,”4000187911-0001”,”4000190336-0001”,”
fix the start and end to correct format
[“3000011160-0001”,”3000108940-0001”,”3000010904-0001”,”3000011241-0001”,”3000011257-0001”,”4000064516-0001”,”4000110787-0001”,”4000119659-0001”,”4000123798-0001”,”4000127062-0001”,”4000146968-0001”,”4000153694-0001”,”4000154807-0001”,”4000157969-0001”,”4000163825-0001”,”4000172407-0001”,”4000186686-0001”,”4000187662-0001”,”4000187911-0001”,”4000190336-0001”]
select all
copy
open your select expert
go into the formula
{TPB200_BILLING_INVOICE.ivo_ext_id} IN (paste values)
|
|
IP Logged |
|
|
|
