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,
WF 8.2
January 09, 2019, 09:11 AM
BabakNYC
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.
WebFOCUS 8206, Unix, Windows
January 11, 2019, 09:32 AM
Tim A
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)
WF 8.2
January 11, 2019, 11:14 AM
BabakNYC
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.
WebFOCUS 8206, Unix, Windows
January 11, 2019, 11:47 AM
Tim A
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?
WF 8.2
January 11, 2019, 11:53 AM
Tim A
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?
WF 8.2
January 16, 2019, 12:54 PM
Hallway
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: