Explorer like interface via ASP??

[PulsaMatt]

I am trying to build a tool to display a bill of materials on a website. Some of the parts using in ItemA may have sub parts (and some of those subparts have subparts, etc). There are some BOMs that go 7 or more levels deep.

The problem I am running into is how to display them properly.

Currently we only list level one and you can click on one of those levels to go to another page which will show you its subparts, etc.

I would like to make an explorer like interface for the webpage.

The problem I am running into is how to do it. I could get code for an explorer like interface in javascript easily, but javascript doesnt connect to databases (the mySQL DB isnt directly available to the internet).

Any ideas and/or helpful nudges?

Thanks!!!


Matt Laski
Network Administrator
Pulsafeeder SPO

 

[ahmun]

One thing you can do is have VBscript/ASP output the javascript. Then you can have your ASP read from the DB, and form a Javascript code block.

I've also embedded ASP inside a Javascript Code Block... something like this:

[blue]
<%
  Dim vbscriptVariable
  vbscriptVariable = "coolness!"
%>
[/blue][COLOR=brown]
<script language="javascript>
  var AlertMsg;
  AlertMsg = "A Javascript alert with vbscript inserted: ";
  AlertMsg = AlertMsg + "[blue]<%=vbscriptVariable%>[/blue]";
  alert(AlertMsg);
</script>[/color]

Earnie Eng

 

[PulsaMatt]

Outputting the data into javascript isnt the problem as much as trying to figure out how to dynamically look up each item and each subitem without knowing how many levels for a particular BOM.

Matt Laski
Network Administrator
Pulsafeeder SPO

 

[ahmun]

Without knowing how your BOM structure is...

what you can do is design your Javascriptstructure first, to include what current levels you have in your BOMs. Then identify what parts of that JS code is repeatable and needs dynamic data. Use VBscript to populate data for those parts and you should be good to go...

Most Javascript collapsable menu trees contain all the items, right? It just "hides" the sub-items until the user clicks to expand. So right from the get-go your Javascript tree-structure should have ALL items and sub-items...

Figure out a loop logic that will traverse through your database of BOMs.

Maybe if you post how your database is structured I can draft up a quick pseudocode that you can try out...

Earnie Eng

 

[PulsaMatt]

The database is currently a huge flat file. There is an master item number, sub item number and the quantity used in that particular pump.

The user searches for the master item number first. This does a table look up and returns all of the sub items numbers that make up the first level of the BOM. While it is outputting the BOM it does a table lookup to determine if the sub item number is a master item number in the table. If it is, it creates a hyperlink instead of a text output.

The BOMs are of the following structure with a level depth that is dynamic depending on the particular pump and how many subassemblies it has.

1
  a
  b
    i
    ii
    iii
  c
2
  a
    i
  b
  c
    i
      *
        o
        oo
          -
          --
        ooo
      **
    ii
  d
3

I tried creating a loop that would dynamically create database connections as needed, but I couldnt get the execute() function to work properly for opening the recordset with a query.

It would be easy if there was a fixed number of levels, but from what I am told there are some BOMs used in our semi-custom pumps that are 7 levels deep, but there could be some that are deeper in the older much larger models.

I havent been able to figure out a looping structure that would work properly without having a fixed number of if statements and database connections. Problem is if that number of if statements and database connections is broken ten the app wont work properly and that would be an issue since manufacturing would never tell me if they developed an 8 level BOM or more. *Shrug*

Thanks!

Matt Laski
Network Administrator
Pulsafeeder SPO

 

[ahmun]

Maybe you can give me the table structure in your database...

how many tables you have
how many fields in each table...

Do you have just a single table with all items and subitems with a field to denote what level?

from that maybe I can figure out how to write a loop that will dymanically generate the levels...

Earnie Eng

 

[PulsaMatt]

Unfortunately the data I get is an export out of our business system. Its in one file. I cant get the programming people to schedule a project to give me better data at the moment.

Master Item Number | Sub Item Number | Qty Used

Sub Item Numbers could techically be master item numbers.

So the loop needs to search to see if each sub item number is also a master item number.

Matt Laski
Network Administrator
Pulsafeeder SPO

 

[ahmun]

wow... now I can see the frustration...

A couple of considerations before we scratch our heads and think of some cleaver programming loops...

[ul][li]How often do you expect this list to change? Do you receive updates often?

If it isn't something that chanes often you might be better off figuring out what level each item is by hand, put taht into your db table and have less of a challenging loop to write.

[/li]
[li]Will you ALWAYS get your data in this format? In other words, if you expect you can get a better version/format of the data from your programming people, it might not be worth thinking up some kind of code to parse that data you currently have.

If you expect the data to ALWAYs be that way and the data comes often... we might figure out a loop...

If you expect the data to come at you in a better format in the future, you might resort to what I mentioned in the first point.[/li]
[/ul]

Earnie Eng

 

[ahmun]

and also... what is contained in the "Sub Item Number" if it is the very last level/sub-item... is it a null value?

Earnie Eng

 

[PulsaMatt]

The subitem number is always full. It has a part number which could potentially be in the master item number if its a subassembly of its own.

Another fun problem ... the file I get is a 75 MB CSV file with approximately half a million records.

I download the entire 75 MB file weekly for this website. The programming department decided at some point not to timestamp the BOMs so we have no easy was of determining which ones have been added/updated in order to only upload the changes.

If I can figure out exactly how I want the data file sent to me I might possibly be able to get something from programming. They have what they claim to be a 3 year backlog of projects so it would have to be something incredibly easy and/or get someone in upper management to make it a "#1 priority".

Matt Laski
Network Administrator
Pulsafeeder SPO

 

[Tarwn]

Here's a thread on outputting db data to reeviews: thread333-902773

I don't know what version of my treeview object i posted there, it may be the IE-only one or the later one that works in both. If you hit the search above there are several other threads where we led people through building there own treeviews (some db-driven, some not).

-T

 

[DreXor]

also this might be of help, it's a method/logic test for preliminary steps for making a drop down menu system based on DB.

granted the table structures whatever might be different for you, but the fields used in this are in the same table

the 2 arrays are to make it more configurable, in order the fields are the levels of the tree branching, and the paired set array right below it are where each level should link to using the value for each field name should there happen to be different pages for each.

hopefully the code is along the lines of what you're working on.

<html>
<head>
<STYLE TYPE="text/css">
<!--
TD {font-size : 10pt;
font-family : Arial; 
color : "#000000";}
A:visited {color:"#0000FF";text-decoration:none;}
A:link {color:"#0000FF";text-decoration:none;}
A:hover {color:"#FF0000";text-decoration:underline;font-weight:bold;}
-->
</style>
</head>
<body>
<table cellspacing=0 cellpadding=8 border=1 width="80%">
<%
Public Count,Indent,Layer,Layers,LayersLinks

Count=0
StackArr = ""
Layers = split("MFG,general_category,category,Sub_Category",",")
LayerLinks = split("SearchMFG.asp?SearchMfg=,SearchMFG.asp?SearchGenCat=,SearchMFG.asp?SearchCat=,SearchMFG.asp?SearchSubCat=",",")
TotalLayers = Ubound(Layers)
Execute("Dim Compares(" & TotalLayers & ",1)")
SQLAdditionalFilter = " AND (MFG = 'Hughes' OR MFG = 'Sony') "


Set Con = Server.CreateObject("ADODB.Connection")
Con.Open WhateverConnection

For Each Layer in Layers
	If SQL1 <> "" Then
    	SQL1 = SQL1 & ",[" & Layer& "]"
    else
    	SQL1 = SQL1 & "[" & Layer& "]"
    end if
	If SQL2 <> "" Then
    	SQL2 = SQL2 & " and [" & Layer & "] is not null and [" & Layer & "] <> ''"
    else
    	SQL2 = SQL2 & "[" & Layer & "] is not null and [" & Layer & "] <> ''"
    end if
Next


    SQL = "Select Distinct " & SQL1 & " from Product where " & SQL2 & SQLAdditionalFilter & " Order by " & SQL1 & ""
    Set RS = Con.Execute(SQL)

LastLayer = 0
Record = 0
do while not rs.eof
    Record = Record + 1
    Layer = 0
    for each field in rs.fields
        Compares(layer,0) = RS(Field.name)
        Layer = Layer + 1 
    next

    Layer = 0
    for each field in rs.fields
        If UCase(Compares(Layer,0)) <> UCase(Compares(Layer,1)) Then
            If LastLayer < Layer OR Record = 1 Then
                LastLayer = Layer
                response.write HTML_GroupStart((Compares(layer,0)))
            ElseIf LastLayer = Layer Then
                LastLayer = Layer
                response.write HTML_GroupItem(Compares(layer,0))
            ElseIf LastLayer > Layer Then
                for i=LastLayer to Layer+1 step -1
                    HTML_GroupEnd()
                next
                LastLayer = Layer
                response.write HTML_GroupItem(Compares(layer,0))
            End If
        End If
        Layer = Layer + 1 
    next

    Layer = 0
    for each field in rs.fields
        Compares(layer,1) = RS(Field.name)
        Layer = Layer + 1 
    next
    rs.movenext
loop
for i=Count to 1 step -1
    HTML_GroupEnd()
next

Set RS = nothing
Con.Close
%>
</table>
</body>
</html>
<%
Function HTML_GroupStart(HTML_GroupStart_Value)
    response.write HTML_Indent(Count) & "<UL>" & VbCrLf
    response.Write HTML_GroupItem(HTML_GroupStart_Value)
End Function

Function HTML_GroupItem(HTML_GroupItem_Value)
    response.write HTML_Indent(Count) & "<li>"
    response.Write "<a href='" & LayerLinks(Layer) & Server.URLEnCode(HTML_GroupItem_Value) & "'>" & HTML_GroupItem_Value & "</a>"
    response.write  "</li>" & VbCrLf
    count = Count - 1
End Function

Function HTML_GroupEnd()
    response.write HTML_UnIndent(Count) & "</UL>" & VbCrLf
End Function

Function HTML_Indent(HTML_IndentX)
    If HTML_IndentX > 0 Then
        For HTML_IndentY = 0 to (HTML_IndentX * 4)
            HTML_Indent = HTML_Indent & " "
        Next
    End If
    Count = Count + 1
End Function

Function HTML_UnIndent(HTML_UnIndentX)
    Count = Count-1
    If HTML_UnIndentX > 0 Then
        For HTML_UnIndentY = 0 to (HTML_UnIndentX * 4)
            HTML_UnIndent = HTML_UnIndent & " "
        Next
    End If
End Function
%>

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[PulsaMatt]

Here is some code I found and that is getting the information I need properly. The problem is the code is SLOW!!!!! Any ideas on a better way or a way to optimize it?

function outputChildren(parentID)
	
	' ===== Create the new recordset and run the sql query =====
	dim rsChildren
	set rsChildren = conOTD.execute("SELECT * FROM bom WHERE part_item_no='" & parentID & "' ORDER BY component_line_no")
	
	' ===== Check for an empty recordset, and run of recordset contains info =====
	if rsChildren.EOF and rsChildren.BOF then
		'response.write("No Records Returned for Parent ID# " & parentID)
	else
		do while not rsChildren.EOF
			'response.write("There are Records Returned for Parent ID# " & parentID)
			response.write("<tr><td align='left'>" & rsChildren("component_item_no") & "</td><td>" & rsChildren("component_desc") & "</td><td>" & rsChildren("component_qty") & "</td></tr>")
			outputChildren rsChildren("component_item_no")
			rsChildren.movenext
		loop
	end if
	
end function

I ran this on a test BOM ... 2 levels, 37 lines on the first level, 7 lines on level 2 (split between 4 different level 1 entries). The page took upwards of 2 minutes to run.

The server is a combined IIS & mySQL server ... Dell PE2650 2.8 GHz Xeon (single proc), 2 GB RAM, 280GB+ free Hard drive space, and Windows 2003 Std Server. I'm using mySQL v4.1.7 and myODBC v3.51.

Any ideas? Thanks!

Matt Laski
Network Administrator
Pulsafeeder SPO