Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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.2
 
Posts: 22 | Location: US | Registered: September 28, 2017Report 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: 1853 | Location: New York City | Registered: December 30, 2015Report 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.2
 
Posts: 22 | Location: US | Registered: September 28, 2017Report 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: 1853 | Location: New York City | Registered: December 30, 2015Report 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.2
 
Posts: 22 | Location: US | Registered: September 28, 2017Report 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.2
 
Posts: 22 | Location: US | Registered: September 28, 2017Report This Post
Master
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
Member
posted Hide Post
Outstanding, this solution works well
Thank you!!!
Tim A


WF 8.2
 
Posts: 22 | Location: US | Registered: September 28, 2017Report This Post
Master
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

 
Prod: 8202M1
Test: 8202M4
Repository:
 
OS:
 
Outputs:
 
 
 
 
 
Posts: 608 | Location: Salt Lake City, UT, USA | Registered: November 18, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders