Focal Point
[SOLVED] Enter List in Dropbox for Table Insert

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/9987020196

January 09, 2019, 08:52 AM
Tim A
[SOLVED] Enter List in Dropbox for Table Insert
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.1
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.1
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.1
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.1
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:
-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,


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All
January 16, 2019, 01:40 PM
Tim A
Outstanding, this solution works well
Thank you!!!
Tim A


WF 8.1
January 16, 2019, 01:49 PM
Hallway
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.


Hallway
WF(Prod):8202M
WF(Test):8202M
OS/Platform:Win 10
Outputs:All