Community Tip - Did you get called away in the middle of writing a post? Don't worry you can find your unfinished post later in the Drafts section of your profile page. X
Version: Windchill 12.1
Use Case: We have a use case where we have custom numbers stored in excel sheet and whenever a number is taken it is marked as used by engineer,engineer creates WTpart using that part(those numbers are standard to business,the sequence cannot be changed).and picking of this numbers is not in sequence,user takes whatever number they want from list.Because of this auto numbering became challenge
Description:
To address above issue, I am planning to load all the available numbers in to a table in database and create a data utility to compare numbers between WTPARTMASTER and MasterNumbers Table(Created by me) and create a JSP page with Find available numbers button that should run the data utility to show the available numbers.
1.
Database Query
1.
Database Query
Create a query to find the free numbers by comparing the
MASTERNUMBERS
table with the
WTPARTMASTER
table.
SELECT
mn.number
FROM
MASTERNUMBERS mn
LEFT
JOIN
WTPARTMASTER wp
ON
mn.number
=
wp.NUMBER
WHERE
wp.NUMBER
IS
NULL
;
2.Create a Custom Data Utility
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.util.ArrayList;
import
java.util.List;
public
class
FreeNumbersDataUtility
extends
DefaultDataUtility
{
@Override
public
Object
getDataValue
(String component_id, Object datum, ModelContext mc)
throws
WTException {
// Implement logic to fetch free numbers from the database
List<String> freeNumbers = getFreeNumbersFromDatabase();
return
freeNumbers; }
private
List<String>
getFreeNumbersFromDatabase
()
throws
WTException { List<String> freeNumbers =
new
ArrayList
<>();
Connection
connection
=
null
;
PreparedStatement
preparedStatement
=
null
;
ResultSet
resultSet
=
null
;
try
{
// Establish the database connection
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@your_database_url"
,
"username"
,
"password"
);
// Prepare the SQL query
String
sql
=
"SELECT mn.number FROM MASTERNUMBERS mn LEFT JOIN WTPARTMASTER wp ON mn.number = wp.NUMBER WHERE wp.NUMBER IS NULL"
; preparedStatement = connection.prepareStatement(sql);
// Execute the query
resultSet = preparedStatement.executeQuery();
// Process the result set
while
(resultSet.next()) { freeNumbers.add(resultSet.getString(
"number"
)); } }
catch
(Exception e) {
throw
new
WTException
(e); }
finally
{
// Close the resources
try
{
if
(resultSet !=
null
) resultSet.close();
if
(preparedStatement !=
null
) preparedStatement.close();
if
(connection !=
null
) connection.close(); }
catch
(Exception e) {
// Handle exceptions during resource cleanup
} }
return
freeNumbers; }}
3.Register the Data Utility in service Properties
4.Create Custom JSP Page
Create a Custom JSP Page
Develop a JSP page to display the free numbers.
<%@ page language=
"java"
contentType=
"text/html; charset=UTF-8"
pageEncoding=
"UTF-8"
%><%@ taglib uri=
"http://www.ptc.com/taglibs/wt"
prefix=
"wt"
%><html><head> <title>Free Numbers</title></head><body> <h2>Available Numbers</h2> <ul> <wt:forEach
var
=
"number"
items=
"${freeNumbers}"
> <li>${number}</li> </wt:forEach> </ul></body></html>
I am new to customization,just want to see if this approach works,if we can any other ways to achieve this.
Thank you
I am not sure a data utility is the right customization. That is mainly for rendering an attribute or UI component. Can you describe the pattern of these custom numbers? How many possible numbers are there?
Thank you for your reply.
We have numbers starting from 2000000 - 2709998 ,as of now random numbers in between are used and created wtparts.Customization should compare used numbers from wtpartmaster and all the numbers in Masternumbers table (created by me) it has all the numbers(2000000 - 2709998) and the utility should show all available numbers by comparing this two tables.
Is it good idea to show 709998 rows to a user?
How often the table comparation should be done?
PetrH