I need to allow users to "drop" lists of bill numbers in an htm web text box, and hold them i n a file to use for multiple applications. However, i can't seem to accomplish this using a set statement or single character string. Any ideas? I don't know how to create a text file upload either.

This message has been edited. Last edited by: FP Mod Chuck,

1. What release/gen?
2. What tool set? App Studio, InfoAssist?
3. Can you provide a very specific use case? From what you've described you'd like to create some kind of a HOLD file. The syntax for that is ON TABLE HOLD AS filename FORMAT ALPHA but a more detailed description of your requirements will help us give you more concrete advice.

Thanks for the help, I am using WF 8.1 with Application Studio.

I'll actually be updating a DW table with the end results, and don't really need help with that piece. However, I have hit a wall trying to figure out how to format a list of bill numbers input through a WF .HTM test box to use in an SQL where statement with single quotes and commas.

I've created a htm page with a test box and input a list of bill numbers as below as &BIL_NBR with the intent on figuring out how to reformat this into a where statement.

11111111
22222222
33333333
44444444

If this is possible, i was hoping that the reformatted &BIL_NBR string (lets call it &BIL_LIST) would = '11111111','22222222','33333333','44444444'
SELECT
BIL_NBR,
BIL_AMT

FROM DW.RECEIVABLE TABLE

WHERE
BIL_NBR IN ('&BIL_LIST')

-*INSTEAD OF THE FOLLOWING WHERE STATEMENT
-*WHERE
-*BIL_NBR IN (
-*'11111111',
-*'22222222',
-*'33333333',
-*'44444444',
-*)
;

So the SQL output would be something like the following after reading the receivables table
BIL_NBR BIL_AMT
11111111 $35
22222222 $45
33333333 $55
44444444 $100

Runs fine with one bill number entered, but a list of 4 bills pasted in, or entering single quote and commas in the HTM edit box will obviously not process.

My ideas were to
- reformat the htm text box to accept commas and single quotes
- read a pasted list using a loop in the fex running the sql
- As a workaround - learn how to incorporate an upload text file into my htm page (this would not allow tableizing the bill numbers, but ok for auditing)

quote:

reformatted &BIL_NBR string (lets call it &BIL_LIST) would = '11111111','22222222','33333333','44444444'

Can you show what &BIL_LIST contains once the user enters the values (before reformat)? It sounds like all you need is a way of separating the values with ',' in a variable.

I think we are in business. I re-created the htm using a text area instead of a text box, and now it runs the bill numbers together on one line.

entered in htm text area set as &BIL_LIST
11111111
22222222
33333333
44444444

The print out in a fex for &BIL_LIST is the following
11111111222222223333333344444444

I'll have varying volumes of bill numbers entered (in this case 4) up to thousands. Is my only option a variable loop to edit this?

FYI the bill numbers i am using are actually char(26), so how could i use that to divide the total characters in &BIL_LIST by 26 = '&N'and insert ',' at each dividing point &N - 1 times?

In the properties of the text area, make sure that you have the Multiple property set to Multiple and Multiple:Add quotes set to Yes.


This will make the output look like this:

'11111111' OR '22222222' OR '33333333' OR '44444444'  

Then in the fex with your variable in it, change the ' OR ' text to a comma with this:

-SET &COUNTRY_VAR = TRUNCATE(STRREP(&COUNTRY_VAR.LENGTH, &COUNTRY_VAR.QUOTEDSTRING, 4, ' OR ', 1, ',', 1000, 'A1000'));  

That will change the variable value to this:

 '11111111','22222222','33333333','44444444' 

This message has been edited. Last edited by: Hallway,

Outstanding, this solution works well
Thank you!!!
Tim A

EXCELLENT!! I'm glad it helped.

Don't forget to add '[SOLVED]' to the front of the subject line of your post if it solved your issue.