Solved

Shipping door and associated orders webpage

Posted on 2012-04-02
6
299 Views
Last Modified: 2012-04-03
I'm a sysadmin/beginner developer and am filling in a little bit to give our development team some help. My goal is to create a simple webpage which refreshes data periodically from an sql query and displays a kind of realtime status of shipping door order assignments and when orders are staged to be loaded.

SQL:

select distinct WMSLocation.WMSLocationId as Door, salesline.salesid
from WMSLocation
inner join WMPallet on WMPallet.recidLocation = WMSLocation.RecId and WMPallet.dataareaid = WMSLocation.Dataareaid
inner join WMCartonGroup on WMCartonGroup.idPallet = WMPallet.Id and WMCartonGroup.dataareaid = WMPallet.Dataareaid
inner join salesline on salesline.recid = wmcartongroup.refrecsalesline and salesline.dataareaid = wmcartongroup.dataareaid
where WMSLocation.AisleId = 'Door' and WMSLocation.InventlocationId = 'CITY' and WMSLocation.dataareaid = 'company';

Example Results:

+-------------+-----------------------
| Door-13 | 433132
+-------------+-----------------------
| Door-14 | 158795
+-------------+-----------------------
|  Door-C  | 125432
+-------------+-----------------------
| Door-14 | 125845
+-------------+-----------------------

Results returned are assumed staged since it is required in the users workflow to change the order status. Max number of orders to a door is 12 (rare) but I guess it happens. Order numbers are either 6 digits or an "EDI-000000" number sequence.  I'm going to show this on a large wide screen display. I haven't settled on a resolution and table size combination for maximium visiblilty, but I can work that out if we get a functional page.

The results are displayed in a simple fixed height, variable width HTML table.  Doors with no orders are shown:

+-----------+--------------------------------------------------------------
|  Doors | Orders
+-----------+--------------------------------------------------------------
|     14    | 125845, 158795
+-----------+--------------------------------------------------------------
|     13    | 433132
+-----------+--------------------------------------------------------------
|     12    |
+-----------+--------------------------------------------------------------
|     11    |
+-----------+--------------------------------------------------------------
..down to door 1 then A, B, C, D (A-D, 1-14)

When a door has an order the rows background is changed to green and all orders for that door are listed comma seporated. When the door is no longer in the data the order is cleared from the table and the background returns to white.

The attached page is where I am currently. It is a hodgepodge of attempts to do the things I want then bring it all together.

I have a feeling I'm over thinking this one and there is a easy method to acomplish this using tools which I'm not yet aware.
shipping-dashboard.asp.txt
0
Comment
Question by:bonipak
  • 4
  • 2
6 Comments
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 37799456
0
 

Author Comment

by:bonipak
ID: 37802631
Thanks for the suggestion. I may go that route.

I cleaned up my code and have a semi funtional page. I do not have all the doors in my html table, which is ok for now, and I'm also not able to group my order numbers into a single door row yet. That may be where your suggestion may comes into play.

I am posting my progress so you experts don't spend time on something I've already done. I'm working on figuring out the correct combination of HTML, SQL and VB logic at this point.
door-status.asp.txt
0
 

Author Comment

by:bonipak
ID: 37804045
just taking some notes, im away from my computer on another project:

dim dock(0 to 17, 0 to 24) As String
fill dock
dock(0,1)=Door-A,$ORDER1
dock(0,2)=Door-A,$ORDER2
dock(5,3)=Door-01,$ORDER3

if dock has no orders, create a normal table row with dock name in column 1
else green background, create table row with dock name in column 1 and loop through and response.write orders for column 2

?
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:bonipak
ID: 37804050
make a new query?!
0
 

Author Comment

by:bonipak
ID: 37804326
I did make a new query with help from our sql savvy developer.

select distinct 
  WMSLocation.WMSLocationId as Door
, (
     select salesid + ' '
     from ( 
        select distinct
          salesid
        from SALESLINE
        where salesline.RECID = wmcartongroup.refrecsalesline
        and salesline.DATAAREAID = wmcartongroup.DATAAREAID
     ) intern
     for xml path('')
  ) as orders
from WMSLocation 
inner join WMPallet 
on WMPallet.recidLocation = WMSLocation.RecId 
and WMPallet.dataareaid = WMSLocation.Dataareaid 
inner join WMCartonGroup 
on WMCartonGroup.idPallet = WMPallet.Id 
and WMCartonGroup.dataareaid = WMPallet.Dataareaid 
inner join salesline 
on salesline.recid = wmcartongroup.refrecsalesline 
and salesline.dataareaid = wmcartongroup.dataareaid 
where WMSLocation.AisleId = 'Door' 
and WMSLocation.InventlocationId = 'city' 
and WMSLocation.dataareaid = 'company' 
order by 'Door'

Open in new window


I now get all orders for the doors and feel it serves it's purpose. Thanks for the information on transposing my query. You deserve the points for educating me.
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 37804612
Glad I could help you figuring it out ...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
In this tutorial viewers will learn how to embed an audio file in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: : The declaration should display (CODE) HTML5 is supported by the most recent versions of all major browsers…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now