Help with creating a query with a list of account #s

[mpnorris]

I am a new user to Impromptu and I have a question that I am hoping I can get help with. I need to perform a simple query where I want to retrieve data for a list of account numbers. I don't know how to search one field "Acct Num" for a list of numbers that are in a string format. I don't want to have to do each account number. I have tried the "In" function but it doesn't seem to work.

Does anyone have any suggestions? Any help would be greatly appreciated. The help functionality doesn't seem very helpful.

Thanks.

 

[DoubleD]

So you only want the choose from records where "Acct Num" is non-numeric?
Sounds like you'll have to build two reports. The first will create a list of records you want the ability to choose from.
The filter for Report 2 says "Acct Num" In (Prompt1). Prompt1 is based on the first report.

Pain is stress leaving the body.

DoubleD

 

[dzum]

I've used a work-around (in Impromptu 6) a number of times for something like this....

Save your list of account numbers to a .csv or .txt file (one account number to a line). In your query, create a file picklist prompt for account number, pointing to that text file, where your filter says:
account_number IN (?promptname?)

Run the report and select all of the values in the picklist (select the first one, and then scroll to the bottom and shift-click the last one).

Keep in mind that Impromptu has a default picklist limit of 100, I believe (that is, no matter how large your picklist file is, only the first 100 are displayed). You can increase that limit by editing your impromptu.ini file.

 

[DoubleD]

dzum,
That limitation can be changed in the Impromptu.ini file.
We have ours set to PicklistLimit=9999.

You don't need to save the records in a file, you can look them up from the first report.

Pain is stress leaving the body.

DoubleD

 

[dzum]

DoubleD's solution works well if you can generate your list of account numbers with a query. However, if you have some "random" list of accounts, working from a text file may be your only option.

 

[mpnorris]

How do you create a file picklist prompt? As I said I am new to Impromtu? I have created the .TXT file with the account #s. Just not sure how to create the prompt to ask for that file. Do you have to put it in a special directory?

Any help would be greatly appreciated.

 

[dzum]

These are the steps in Impromptu 6.0. I have no idea if it would be different in 7.0

1) Save the text file and put it someplace you can find. Make sure the file is no longer open.
2) Go to the filter of your report and add
a) AND account-number IN (
b) click on "prompt manager"
c) if you already have prompts defined in the report, choose "new"
d) give the prompt a name (like "acct #")
e) choose "file picklist"
f) type any prompting text you want (like "What account number(s) do you want?")
g) select the text file containing the account numbers
h) click OK twice to get back to the filter
i) click on the closing parenthesis
j) click OK to finish your filter and run the report

 

[mpnorris]

Thanks dzum. That worked. Now I can get something accomplished.