Two to three times a week we receive an Excel file from Dell that details all of the orders we have in process with them. I need a simple web tool developed that will allow us to pull up a form, enter a PO# and then be able to get order information, click through on a link to Dell's official order page, etc.
I believe we can automate the process of getting this file dropped into a folder via FTP or a "network" drive that is tied to a specific site location. Or, if it was possible to just create a rule in Outlook to forward to another address or save the attachment somewhere.
From our customer's perspective, they would simply input their PO# to start a search. At this time, we are not looking for a username/password combination, just the PO#. We would return all matches for a PO# even if they were from multiple customers that happened to use the same PO#.
Some, if not most, PO#'s have multiple line items with data. When a customer searched on their PO#, it would need to return all related line items in a table format.
The data we would show to the customer would include the info below. A summary table result would return first, at least showing PO#, Company Name, Order Qty, Item Long Name, Current Estimated Ship Date, Dell Order Number (with link). The customer could click through to see more detail on any line item.
E: Company Name Shipping
F: Street Address 1 Shipping
G: Street Address 2 Shipping
H: City Shipping
I: State Abbrev Shipping
J: Postal Code Shipping
N: PO Num (Note, the actual customer PO number is everything contained within this field except for the first and last character. For example, a PO# of 162 would show up in the report as 6162D. We would need to tool to be able to find PO's without the "6" and "D".
O: Dell Order Number
(This would be hyperlinked to the Dell Order Status Lookup Tool. This is where the actual PO# shown in column N, including the first and last character would be used, along with our Zip Code 80222 for validation of the search). An example URL for the link would be as shown below, but would vary based on the PO#. We sometimes uses a 6 and sometimes a 7 before the customer PO# and the D. Also, if we place multiple orders against the same PO#, then the PO might show as 738805D-2, etc. It's consistent to always start with a single number (6 or 7). Some customer PO#'s also are alpha-numeric and could contain a "D" so will have to have some intelligence to make sure we search correctly.
T: Order Qty
X: Order Status Desc
AA: Current Estimated Ship Date
AB: Shipped Date
AG: Svc Tags
AH: Item Num
AI: Item Long Name
Hidden fields, viewable only to our company would include. We would want a link that we could click, enter a company-wide password, and then be able to have these additional fields be visible.
Z: Order Date
AC: Inv Date
AD: Master Inv #