Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Enter List in Dropbox for Table Insert
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Enter List in Dropbox for Table Insert
 Login/Join
 
Member
posted
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
 
Posts: 21 | Location: US | Registered: September 28, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1689 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 21 | Location: US | Registered: September 28, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1689 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 21 | Location: US | Registered: September 28, 2017Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 21 | Location: US | Registered: September 28, 2017Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 444 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
Member
posted Hide Post
Outstanding, this solution works well
Thank you!!!
Tim A


WF 8.1
 
Posts: 21 | Location: US | Registered: September 28, 2017Reply With QuoteReport This Post
Guru
posted Hide Post
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
 
Posts: 444 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Enter List in Dropbox for Table Insert

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.