Developing A New Module Concerning Customer Returns in an Existing Excel Help Desk

This question stems from a long series of questions involving a Custom Help Desk created in Excel 2007/2010. The link to the Previous question is below. In this question, the intent is to have an existing form added into the Help Desk with validation and code that will allow the form to function with the existing Help Desk and database.  

Previous Questions

I have a database which contains a table with all of the Sales Orders and the corresponding information for each line. This table is updated nightly by a link to an Oracle database. If a Sales Rep has a customer that wants to return an item, they are required to use the Returns Authorizations Form (attached). While using the Returns form, there are certain validations for each field that are listed below:


Date = Current Date
sales Order No. = Select from a drop down list of existing Sales Order Numbers in database table.
Inside Rep = Current Username/Environ("Username")
Outside Rep = Free Text-Optional (Inside Rep can input this)


Vendor = Select from a drop down list of existing Vendors in database table (Dependent on Sales Order Number)
Item Number = Select from a drop down list of existing part numbers in database table. (Dependent on Sales Order Number)
Qty = Free Text
Reason Code = Select from a drop down list of values
Action Taken = Sales Rep cannot type in this field.  This is strictly for the Returns Coordinator.
Vendor RA# = Free Text
Restocking Fee = Currency field/free text
Total = Auto calculation: Qty * Restocking Fee * Vendor Cost for item (not shown/value in db table)


Oracle Return No = Free Text Field
Return Coordinator Signature = Free Text Field/Maybe some way to have it digitally signed?

Customer = Select from a drop down list of existing Customers in database table.
Vertical = Auto populated based on Inside Sales Rep/username.  Values in db table.

The two checkboxes (Marketing & Promotion, Margin) are open for the user to check. This is for reference only.  

Qty (4 identical fields) = Free text
Part Number (4 identical fields) = Select from a drop down list of existing part numbers in database table. (Not dependent on Sales Order)
Restocking Fee = Currency field/free text
Total Order Amount to Write Off = Auto calculation of: Qty * Selling Price of Part Numbers Selected in the 4 identical dropdowns * Restocking Fee
Total Cost of Items to Write Off = Auto calculation of: Qty * List Price of Part Numbers Selected in the 4 identical dropdowns * Restocking Fee

Explanation = Multi-line free text field

Signature = This is a mandatory field if the value of the Total Cost of Items to Write off is greater than $2,500.
Date = Current Date that the above Signature field is populated if possible.  If not, then current date.

Currently, this is setup as a Userform.  If you think it would be better to create in an actual spreadsheet, I can do that as well.  I will be adding the db in the next thread shortly.

Again, thank you so much for all of the help EE!!!
Returns-Form.xlsm
Christopher WrightDirector, Service DeliveryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Not really an answer here but...

Why reinvent the wheel?...

There are dozens of off- the shelf solutions around.
We use this:
http://www.hesk.com/
It's web based, and at 39 bucks at you're all set....

Even this:
http://www.zendesk.com/product/pricing
...in the top version is 125 USD and again, you are all set.

Think of it this way, ...if your company is paying you to do this, ...at the low end you might be getting 25USD/per hour
This mean you would have to build something similar to what is out there in less than 5 hours...!
;-)

Even Access has a template DB for projects like this:
http://office.microsoft.com/en-us/templates/issue-database-TC001218693.aspx


To me, this is more of a database application, and as such, it will be a bit beyond Excel when it comes to  things like: relationships, Programming, distribution, scalability, concurrency, data integrity, ...etc


JeffCoachman
0
Martin LissOlder than dirtCommented:
Working on it.
0
Martin LissOlder than dirtCommented:
Thanks for all the information but you know me, I always have questions.

1) Sales Orders table: Where exactly is it?

2) Return Coordinator Signature. Is the sheet/userform going to be sent as a "report" in a similar fashion to what we do now?

3) Should a record of the return be added to Tracking.mdb?

4) The two checkboxes: Is it required that one or the other be checked?

5) Can there be more than 7 of the Vendor, etc. gray lines?

6) Digital signature: I've never done this but I don't see any reason why I can't do it. How would a Sales Manager sign this? Would he walk over to the desk of the person creating the RA, or?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Christopher WrightDirector, Service DeliveryAuthor Commented:
1) Sales Orders table: Where exactly is it?
 
- Sales Orders table is located in SalesOrders.mdb.  I have uploaded this to my Mediafire account.  The link is below:
   Sales Order Database

2) Return Coordinator Signature. Is the sheet/userform going to be sent as a "report" in a similar fashion to what we do now?
 - Yes it will be sent to the Returns coordinator.  In terms of the sheet being emailed, yes this will be sent like the others.  In terms of formatting, I was hoping to keep the same structure of the userform.  This is why I questioned if I should remove the userform and simply created this on a normal worksheet.  

3) Should a record of the return be added to Tracking.mdb?
 - Yes sir

4) The two checkboxes: Is it required that one or the other be checked?
 
- No sir

5) Can there be more than 7 of the Vendor, etc. gray lines?
 - Yes sir.  Typically, we have one vendor per form but they are allowed to add another vendor or two.

6) Digital signature: I've never done this but I don't see any reason why I can't do it. How would a Sales Manager sign this? Would he walk over to the desk of the person creating the RA, or?
 - Currently, the form is emailed to the manager for approval. He prints it out, signs it, then re-scans and emails back to the returns coordinator.
0
Martin LissOlder than dirtCommented:
5) Can there be more than 7 of the Vendor, etc. gray lines?
 - Yes sir.  Typically, we have one vendor per form but they are allowed to add another vendor or two.
That seems contradictory.

6) OK that's easy. I just literally have a dotted line where they can sign manually after they print it.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
For question 5.  
I was saying that the user can enter more than 7 vendors; however, they usually do not have that many returns at one given time.  

For Question 6:
Is there a way to allow them to Digitally sign?
0
Martin LissOlder than dirtCommented:
5. Then I think a sheet will be easier to code then a userform.

6. Since the Sales Manager (is there more than one Sales Manager?) will have the spreadsheet on their machine then I can add a new table to ADS.mdb that would contain the Sales Manager(s) user name and a password, and they would have to enter that password in order to "sign".
0
Martin LissOlder than dirtCommented:
It's possible that we may have a problem here and that is due to the size of these databases.  In addition to the Item Master which is pretty large, the Sales Order Database I just downloaded is also large. I'm made that one somewhat smaller by deleting some of the past years tables and then compacting/repairing but I also see that there's a link in that database to a OracleSalesOrderLines database and I assume that that will be even larger. BTW I can't use that link because it refers to your server so I assume I'll need my own copy of the OracleSalesOrderLines database.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
My apologies Marty. I intended to delete that link to the Oracle table. I added the local tables which are everything in the Oracle db. Sorry for the confusion. Those local tables were imported by year from the Oracle db.
0
Martin LissOlder than dirtCommented:
Okay thanks.

Do you prefer a userform or a sheet for the RAs?

The advantage (for me) of a userform is that we can use all those nice controls (BTW what is an 'Accelerator'?) but the disadvantage is that I'm not sure if I can figure out how to dynamically add more Vendor rows, or if it's even possible with VBA.

The advantage of a sheet is that it's relatively easy to add more Vendor rows but it won't look nearly as nice as your userform.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Sorry again for the delay.  Crazy week at work.  :(

Pertaining to the sheet or userform; I am totally open.  In terms of aesthetics, that takes a backseat when it comes to performance.  From the sound of it, I think a sheet would be the most practical way to go.  I will develop a sheet and send over.  I think that would be easier for maintenance after the project has been completed too.  Thanks buddy!
0
Martin LissOlder than dirtCommented:
Okay, but a problem with the sheet is that you shouldn't use ActiveX controls, so let me know if you still want to go that way.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Roger that.  I try to stay away from ActiveX controls if at all possible.  Form controls are okay, correct? I have attached my prototype.  You can re-arrange/modify the form codes on the tab 'SalesOrders' as you needed.  Thanks again!
Returns-Sheet.xlsm
0
Martin LissOlder than dirtCommented:
That's quite nice! I'll need to do things like break up the "Qty:" and "Part #" cells into two pieces so that entering something doesn't wipe out the text but that shouldn't be too hard. I'll start working on it later today. I'm sure it will take several days to complete plus additional time for my inevitable questions:)
0
Martin LissOlder than dirtCommented:
Questions:

1) On the RA form you have both 'Yes' and 'No' checkboxes for write offs. I don't think we need the 'No' checkbox since if the 'Yes' isn't checked then that means No. In fact I just think we need this.
Checkbox
2) Do you actually want a textbox for signature so that the user types in his name in Excel, or do you just want a dotted line for a manual signature after printing?

3) What should the text of the Return Authorization request say on the ADS Help Des Sheet? (Used to...?)
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Answers:

1) On the RA form you have both 'Yes' and 'No' checkboxes for write offs. I don't think we need the 'No' checkbox since if the 'Yes' isn't checked then that means No. In fact I just think we need this.
Checkbox Checkbox
- I totally agree.  That totally makes sense. Actually, I was wondering if that entire bottom section could be hidden unless the checkbox is checked. If the user checks the box, the bottom section will appear and then the user can update as necessary.

2) Do you actually want a textbox for signature so that the user types in his name in Excel, or do you just want a dotted line for a manual signature after printing?
- Actually I only put that there for reference.  I apologize for not specifying that.  A dotted line for a manual signature works for me. Really, anything that you feel that is more practical, I say go for it.  Thanks

3) What should the text of the Return Authorization request say on the ADS Help Des Sheet? (Used to...?)
- How about: "Used to submit Sales Order/Customer Returns.  Please ensure that all fields are filled completely."
0
Martin LissOlder than dirtCommented:
1) Will do

2) No problem

3) OK

Thanks.
0
Martin LissOlder than dirtCommented:
I hope that the following changes are OK with you.

OK?
The icons at the top won't show up in the sheet that gets mailed.

The "+" on the little command button will cause a new gray row to be added when it's clicked.

They don't show up in the picture but there are dash lines above "Signature" and "Date".
0
Martin LissOlder than dirtCommented:
In your requirements you say "sales Order No. = Select from a drop down list of existing Sales Order Numbers in database table.". Are you referring to the ORDER_NUMBER field that occurs in the multiple OracleSOTable_20nn tables? If so that's a big problem because there are hundreds of thousands of rows involved in each year (due to multiple line items per SO) and that will take a long time to process even using SELECT DISTINCT. Also when the process is done I don't know what good a long list of sales order numbers will do the user. What about just making it a free text field and then validating that the value exists in one of the tables (which will still take time to determine)?
0
Martin LissOlder than dirtCommented:
I also feel that since most of the cells on the RA will be protected that I should make all the protected cells one color and the unprotected cells another color. One of the colors can be in fact no color. If you agree then what color scheme do you prefer?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Answer Thread ID: 39606114
I am perfectly fine with that change.  The form looks great man! Thanks!!!

Answer Thread ID: 39606329
I think the free text field is a good idea. I am concerned about the validation taking time.  Would it be better if I simply added a table of just the Sales Order numbers with corresponding information.  That would greatly reduce the number of lines to handle.  If that's not a good idea, then I am totally okay with going with the free text field and validation.

Answer Thread ID: 39606400
Great idea man. I should have thought of that. Haha.  How does Grey sound.  If the cell is unprotected, then it will be grey while the others have no fill.  

Again, let me say thank you so much for all of your help with this project.  You have been a life saver!  Literally.
0
Martin LissOlder than dirtCommented:
I think the free text field is a good idea. I am concerned about the validation taking time.  Would it be better if I simply added a table of just the Sales Order numbers with corresponding information.  That would greatly reduce the number of lines to handle.  If that's not a good idea, then I am totally okay with going with the free text field and validation.
If you can create a single table that lists all sales order numbers that would be great and there's no need for free text. I assume that you'd want to show the user some of the "corresponding information" to help them pick the right SO. If so which fields should be shown?

You have been a life saver!  Literally.
I hope not literally.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
I will provide a table that shows Sales Order Headers and related information. Concerning the fields to be shown, I would show the Vendors in the dropdown (limit the selection to only Vendors on that Sales Order), Item Number (limit items just as you would do the Vendor Names), and the restocking fee for that particular Vendor when selected.  I hope I answered your question correctly.


Yes, you have saved me from having my boss decapitate me for project deadlines and the like.  Hahaha
0
Martin LissOlder than dirtCommented:
I will provide a table that shows Sales Order Headers and related information. Concerning the fields to be shown, I would show the Vendors in the dropdown (limit the selection to only Vendors on that Sales Order), Item Number (limit items just as you would do the Vendor Names), and the restocking fee for that particular Vendor when selected.
I'm a little confused. I'm talking about the 'Sales Order Number:' number field where I assume you want the user to select a sales order.  I also assume that you will be creating a new table with sales order numbers and some other fields. I don't see how I can "(limit the selection to only Vendors on that Sales Order" since the sales order hasn't been selected yet. And when I asked about what other information I should show, I meant that in addition to the sales order number itself what other identifying information would help the user make the choice? Would the identifying information be the sales order number and the vendor name for that sales order, or something else or in addition?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Ohhhh, my apologies.  I thought you meant on the corresponding table below.  I think the Sales Order Number, Created Date, and the Vendor Name would work. Is that okay?
0
Martin LissOlder than dirtCommented:
Sure. I await the new db. Take your time since I've got other stuff to work on.
0
Martin LissOlder than dirtCommented:
I hope you approve.

This is the RA as the user will first see it:
Unchecked
And this is after the Write Offs checkbox is checked:
Checked
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Very nice!!! I do have one question.  Can we have the grey a little lighter. This way it won't be such a strong contrast between the locked and unlocked cells. Are you okay with that?  I am working on the database.  Would you like for me to just add a table to the Sales Order db or create a new database altogether?
0
Martin LissOlder than dirtCommented:
Sure.
0
Martin LissOlder than dirtCommented:
1) In the requirements you say
Total Order Amount to Write Off = Auto calculation of: Qty * Selling Price of Part Numbers Selected in the 4 identical dropdowns * Restocking Fee

Total Cost of Items to Write Off = Auto calculation of: Qty * List Price of Part Numbers Selected in the 4 identical dropdowns * Restocking Fee
The only total fields that I see are the ones in column K. Is that the "Total Order Amount to Write Off"?

Where does "Total Cost of Items to Write Off" go? If it's the "Restocking Fees" field the you also say that that is free text.

2) I'm also a little confused about this:
Date = Current Date that the above Signature field is populated if possible.  If not, then current date.
Can you describe a situation where it should be anything but the current date?

3) Is the Vendor (column C) always the same? If not how do I generate the list of Part Numbers (column D:E)?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Hi Friend. I have provided answers to your questions below:

This is a mistake on my end.  The form I provided was missing two fields named 'Total Order Amount To Write Off' and 'Total Cost Of Items To Write Off'.  My apologies for that.  I have provided the form including the two fields I left off.  Man, I'm so sorry!

Concerning the second date; I was actually referring to having the form signed digitally.  What would happen is the form is digitally signed and the Current Date is triggered to populate the current date.  This way you would see when the form was created and then when the form was signed thus making the form permanent and ready to archive.  This would also prevent people from simply reusing the form that is sent and forcing them to go back to the help desk to complete a fresh sheet from scratch.

When you refer to the Vendor column always being the same, are you asking if every row is the same? The vendor column could contain multiple Vendor names.  For instance, once Sales Order 123456 is selected, then all of the vendors pertaining to that sales order are available to be selected in Column C.  The user could then select the corresponding vendors according to the Sales Order they selected.  Is this possible? Thanks again!!
Returns-Sheet.xlsm
0
Martin LissOlder than dirtCommented:
You may not have seen the 3rd question in post ID 39609499 because I edited the question.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
I edited the answer too.  I think I may have submitted just before you.  There is a third paragraph concerning the Vendor Column now.  

Also I wanted to add a little more to the first answer.  The restocking fee is separate and apart from the two missing fields.  Yes, it is mostly free text (Numbers Only).
0
Martin LissOlder than dirtCommented:
modYes I was asking if all the Vendor names in col C would be the same and I understand now that they are not the same. So let me see if I understand the process:

The selects a sales order number from the dropdown list in J4:K4. Then for the Vendor selection in cells C11:C18 he selects from a list of vendors on the Sales Order. I assume then that the user should then be able to select Part Numbers in D37:D42 from the sales order number selected. Is that all correct?

Does my placement of the new fields and the shading change work for you? (I might move the two new Write off fields down one row)
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Yes sir.  That is correct.  

The new write off fields look perfect!  Thanks again man!  I would move the Write Off fields down as well.  Haha, that's just because I'm uber OCD though.
0
Martin LissOlder than dirtCommented:
Call me "Mr. Monk"
0
Martin LissOlder than dirtCommented:
Do we have the cell color scheme backwards? In other words should we change it so that the cells where the user can manually enter data are white and the protected cells are gray?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
That works.  That may be better suited to identify the cells needing to be addressed.  Good call Mr. Monk.
0
Martin LissOlder than dirtCommented:
deleted
0
Martin LissOlder than dirtCommented:
I just opened the new database and I have a few questions.

- There is a single SalesOrders&Vendors table and several SalesOrders&Vendors_yyyy tables that all have the same 3 fields. Can I delete all the SalesOrders&Vendors_yyyy tables to save some space? I

- In the requirements you say "Total = Auto calculation: Qty * Restocking Fee * Vendor Cost for item (not shown/value in db table)" - where do I get the Vendor Cost from?

- You also say "Select from a drop down list of existing Customers in database table." - Where do I get "existing part numbers" from

- "Auto populated based on Inside Sales Rep/username.  Values in db table" - Where do I get that info?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Sorry about that.  Let me resend you the db here shortly.
0
Martin LissOlder than dirtCommented:
I previously added a little '+' command button below the 8 Vendor, etc rows that when clicked will add a new row. Would a similar button be helpful here?
pic
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Yes sir.  I think that would make the form much more flexible for adding new rows.  Good idea man!
0
Martin LissOlder than dirtCommented:
Is the Explanation field a required field when the Associated Write Offs checkbox is checked?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
No sir.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
I have addressed the issues below:

- There is a single SalesOrders & Vendors table and several SalesOrders&Vendors_yyyy tables that all have the same 3 fields. Can I delete all the SalesOrders&Vendors_yyyy tables to save some space?
I have removed all tables that were similar to leave only SalesOrders&Vendors Table.

- In the requirements you say "Total = Auto calculation: Qty * Restocking Fee * Vendor Cost for item (not shown/value in db table)" - where do I get the Vendor Cost from?
I have added a table named OrderedItems which provides the Vendor Cost.

- You also say "Select from a drop down list of existing Customers in database table." - Where do I get "existing part numbers" from?
I assumed you were referring to Existing Customers instead of Existing Part Numbers so I added the Customer to the SalesOrders&Vendors Table.

- "Auto populated based on Inside Sales Rep/username.  Values in db table" - Where do I get that info?
I have added the Inside Sales Rep and the Outside Sales Rep to the SalesOrders&Vendors table.
0
Martin LissOlder than dirtCommented:
Did you mean to add the URL?

Also while it won't do any harm, I don't think I need the Inside Sales Rep. Or do I?
0
Martin LissOlder than dirtCommented:
Part Number (4 identical fields) = Select from a drop down list of existing part numbers in database table. (Not dependent on Sales Order)
Rather than displaying a list of a million part numbers can I do the same thing here as I'm doing for the sales order number? In other words make it free text that I will validate against all the existing part numbers.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Yes sir, that works for me.  There are quite a bit of items. So, this should be a faster process.
0
Martin LissOlder than dirtCommented:
I'm glad you agree because that's what I already did:)
0
Martin LissOlder than dirtCommented:
I'm confused about Inside Rep. In the requirements for Inside Rep you say
Inside Rep = Current Username/Environ("Username")
and for Vertical you say
Vertical = Auto populated based on Inside Sales Rep/username.  Values in db table.
Does that mean that when you set down to test this that your username should appear in the 'Inside Rep' cell and that your username will be found in the SALES_REP_NAME of the SalesRep&Vertical table?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Yes, kind of.  I am going to log in as a sales rep and test.  This way I will be able to test multiple Inside Rep names.   So I will log in as an Inside Rep that will be found in the SALES_REP_NAME of the SalesRep&Vertical table.
0
Martin LissOlder than dirtCommented:
You don't have to log in as someone else if you don't want to. I'm adding code that when I'm testing will set the Inside Rep name on the sheet to one of the people in the  SalesRep&Vertical table and I can do the same for you if you tell me the name you want to use.
0
Martin LissOlder than dirtCommented:
I believe that I should be getting the data for the dropdown list for 'Part Number' from the ITEM field in the SalesOrderHeaders database. For the writeoff totals I need to know both the Selling Price and the List Price of those items but that information is currently not in SalesOrderHeaders. Can you add those fields to that table so that I don't have to do a second lookup in the ItemMaster each time I want to do the calculations? If you do add them please tell me the field names for each if you make those names anything other than SELLING_PRICE and LIST_PRICE.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
To answer Thread ID: 39625302
Please set my name to 'Jarvis, Parker' AKA Parker Jarvis.


To answer Thread ID: 39625913
Database Table
0
Martin LissOlder than dirtCommented:
Thanks.
0
Martin LissOlder than dirtCommented:
I think something is wrong. I downloaded the new db and while the name is SalesRep&Vertical.zip the file inside is SalesOrderHeaders.mdb.  Do I no longer need SalesRep&Vertical?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Yes sir, we still need the SalesRep&Vertical.  I simply used the same minimized database I used before to upload files to MediaFire instead of creating a new database altogether.  I apologize for not specifying that.  It was a late night at the office and I was rushing to get out.  I simply thought you could read my mind.  Again, I am sorry.  :(
0
Martin LissOlder than dirtCommented:
I'll need to download the old salesOrder&Vertical db again so if the file is still in MediaFire please post the link.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
I have renamed the db to ‘DefaultDB for MediaFire’ so that it won’t cause any more confusion down the road.  Thanks


DB Link
0
Martin LissOlder than dirtCommented:
That zip does not contain the SalesRep&Vertical table which is what I need.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Sorry.  Link to the new database is below:


Updated DB
0
Martin LissOlder than dirtCommented:
There are part numbers such as 2000-010-110 in the SalesOrderHeaders table that have multiple SELLING_PRICE values. What field(s) besides ITEM do I need to use in order to be able to find the one that I should use for the Total Order Amount to Write Off calculation?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Let's try the ITEM_CARD_COST.  That should be the same for duplicate part numbers. So, in this situation, there are multiple parts with different selling prices; however, the ITEM_CARD_COST should be the same for each.
0
Martin LissOlder than dirtCommented:
Seems strange that something called ITEM_CARD_COST would be the same as the selling price, but I'm fine with that. The problem is that that field isn't in my current SalesOrderHeaders database so guess what? :)
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Here is a link to all the updated tables:

Entire Database (SalesOrdersDB)
0
Martin LissOlder than dirtCommented:
In post IDs 39628913 and 39628937 I assumed that we were saying that ITEM_CARD_COST was the selling price. In the new database you sent me there are only two price fields ITEM_CARD_COST and SO_UNIT_SELLING_PRICE and since it appears that ITEM_CARD_COST is always less than SO_UNIT_SELLING_PRICE should I assume that ITEM_CARD_COST is the list price and not the selling price?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Yes sir that is correct.  ITEM_CARD_COST is equal to LIST_PRICE.
0
Martin LissOlder than dirtCommented:
Total Order Amount to Write Off = Auto calculation of: Qty * Selling Price of Part Numbers Selected in the 4 identical dropdowns * Restocking Fee
Given a Qty of 10 and a selling price of $250 and a restocking fee of $30, should the Total Order Amount to Write Off be $75,000 (10 * 250 * 30)?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
No sir, I did not explain it as thorough as I should have.  Go figure, huh?  I'm so sorry for all the screw ups.  :'(  The appropriate calculation should be structured as follows:

Total Order Amount to Write Off = (Selling Price * Qty) + (Restocking Fee * Qty)

Given the scenario you provided, instead of the Total Order Amount to Write Off being $75,000, it would be $2,800.

(250 * 10) + (30 * 10) = $2,800
0
Martin LissOlder than dirtCommented:
Besides yourself, what categories of people should receive the emailed RA? Inside Rep? Outside Rep? Buyer? Or?
0
Martin LissOlder than dirtCommented:
Oh and in addition to the above question, the requirements say "Signature = This is a mandatory field if the value of the Total Cost of Items to Write off is greater than $2,500". How would you like to do that?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Thread ID: 39631117
Currently we have one person that handles returns.  However, this person serves as a buyer so the categories of people receiving this should be buyers. And their corresponding manager.  Or, could we have a Category created that is called Returns Coordinator?  This way, I can assign that Buyer as the Return's Coordinator and if she leaves, I can simply replace the name?

Thread ID: 39632258
Could there be a validation that requires the Signature field to be completed before being sent. Or, instead of sending to the Returns Coordinator/Buyer, could we have it send to that Sales Rep's Team Leader.  I can provide an updated table that has that value?  That's your call.  Do you think we can do this?
0
Martin LissOlder than dirtCommented:
Or, could we have a Category created that is called Returns Coordinator?
That sounds good. Would that person's manager also get sent the report? If so then I'll need you to tell me how to pick the proper manager from the "Managers" data in Form Codes unless sending the report to all managers (as we do in Inventory Item Notifications) is acceptable.

Could there be a validation that requires the Signature field to be completed before being sent
Well currently there isn't a digital signature process in my code for either the Return Coordinator or Sales Manager's signature so there can't be a validation. If I were to attempt to add such a thing how would you envision the process happening in your company?

Or, instead of sending to the Returns Coordinator/Buyer, could we have it send to that Sales Rep's Team Leader.
If that removes the need to have a digital signature then great!

Before you go to the trouble of creating or modifying any tables because of any of the above, let's discuss what you are planning to do.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
I have provided answers below:

That sounds good. Would that person's manager also get sent the report? If so then I'll need you to tell me how to pick the proper manager from the "Managers" data in Form Codes unless sending the report to all managers (as we do in Inventory Item Notifications) is acceptable.
It is acceptable to send to all managers.

Well currently there isn't a digital signature process in my code for either the Return Coordinator or Sales Manager's signature so there can't be a validation. If I were to attempt to add such a thing how would you envision the process happening in your company?
Let's not set out on the Digital Signature endeavor?  Let's go with the Email approach.
 
Instead of the digital signature, let's have the request automatically send to that particular sales rep team leader.  Now, if it so happens that it is a team leader completing the form, then obviously it would not send to themselves.  Therefore, in that scenario alone, the request >$2,500 would go directly to the Returns coordinator.  To break it down:
• Inside/Outside Sales Rep completes Returns form.  If > $2,500, send to Team Leader.  If < $2,500, send to Returns Coordinator and manager.
• Team Leader or anyone else completing form, then send to Returns Coordinator regardless of value.
Inside/Outside Sales Rep and their respective Team Leaders will be identified in database table.  That is also how you can determine if form is being completed by Team Leader or anyone else.  Basically, if person filling out the Returns form is not found as an Inside/Outside Sales Rep in the table, treat them as though they are a Team Leader or someone else, then send directly to Returns Coordinator.
0
Martin LissOlder than dirtCommented:
OK so send me the new table. Please note that in my ADS database I link to various tables and those tables are inside two other databases. Those databases are called the SalesOrderDB.mdb for the ItemMaster table (there are other tables in this database that I don't need) and  the DefaultDB for MediaFire.mdb for the other tables. The files are large and so in order to not have unneeded large files hanging around and in order to keep track of where things come from I'd like you to alway send me new or updated tables in one of those two databases (as appropriate).

Also please establish the following Indexes in the tables before you send them so that I don't have to figure out which indexes I need and redo them if a table is modified at your end. You'll need them anyway when you test.

SalesOrderHeaders: ORDER_NUMBER (descending) and ITEM (descending)
SalesOrders&Vendors: ORDER_NUMBER (descending)  
OrderedItems: ORDER_NUMBER (descending)
ItemMaster: PART_NUMBER (descending) and VENDOR_ID (descending)
0
Martin LissOlder than dirtCommented:
In post ID 39633847 you talk about the Write Off signature under the Explanation. Do I need to do anything with the "Return Coordinator Signature" that's in the upper section of the form?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
No sir.  That is something she can print out and sign OR simply type her name in.  Is that possible?
0
Martin LissOlder than dirtCommented:
What she receives will be an unprotected workbook with no code so she can do anything she wants with and to it.
0
Martin LissOlder than dirtCommented:
1) I think I can work with what I have but are you aware that almost all of the records in SalesRep&Vertical are missing SALES_MANAGEMENT and ROLE_NAME vales and that many that do have a ROLE_NAME don't have a SALES_MANAGEMENT value? Also there aren't many records in SalesOrderHeaders that have that data.

2) Given the scenarios you described in post 373663 can you lay out for me using database table and field names and the Inside Rep and Outside Rep names on the Return Authorization sheet, how I would go about deciding who to send the reports to?
0
Martin LissOlder than dirtCommented:
In my post 39635026 I said
What she receives will be an unprotected workbook with no code so she can do anything she wants with and to it.
but thinking about it now I think that I should protect most of the sheet with the exception of the 'Action Taken' cells and the two signatures. Do you agree?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Thread ID: 39635121

1) I think I can work with what I have but are you aware that almost all of the records in SalesRep&Vertical are missing SALES_MANAGEMENT and ROLE_NAME vales and that many that do have a ROLE_NAME don't have a SALES_MANAGEMENT value? Also there aren't many records in SalesOrderHeaders that have that data.
No sir, I was not aware. However, looking at the table now, I see what you pointed out.  I will take a look at it now.

2) Given the scenarios you described in post 373663 can you lay out for me using database table and field names and the Inside Rep and Outside Rep names on the Return Authorization sheet, how I would go about deciding who to send the reports to?
I am looking into this now.  I will forward this over to you as soon as I figure this out.  This was pulled directly from our Oracle database, so it may be an incomplete table.  Sorry

Thread ID: 39635830

Yes sir, I totally agree.  Good idea by the way!
0
Martin LissOlder than dirtCommented:
Here's an idea to perhaps simplify/solve the "who to send it to" problem.

In form codes we currently have a 'Managers' table and perhaps we could add a 'Team Leader' table. If the team leaders are assigned by something like the Vertical market that we already have on the RA sheet then that would make getting that email address very easy. Is there more than one "Returns Coordinator"? If not then we could also have a 'Returns Coordinator' table with one entry, and if there are several hopefully there's some way to pick the best one based on some information we already have. And concerning managers, are they the same managers that get sent the IIN report? If so then I can do the same for the RA.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
For now, there is only one Returns Coordinator; however, there will be multiple here soon. So let's move forward as if there are already more than one.

The manager are the same as the IIN report. So fortunately we can do the same for the RA
0
Martin LissOlder than dirtCommented:
Okay if we were to add a 'Returns Coordinator' table we could add as many names as needed and temporarily mail to all of them.

You didn't address the Team Leader part of my idea.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
My apologies.  Team Leaders are assigned by Vertical. Creating a Team Leader table is great because Team Leaders change from time to time.  Sorry for not addressing that before.
0
Martin LissOlder than dirtCommented:
Chris, I'm interpreting this
Inside Rep = Current Username/Environ("Username")
as meaning that the name of the person filling out the form goes here.
picbut in your explanation of whom to send it to you say
[If] Inside/Outside Sales Rep completes Returns form.  If > $2,500, send to Team Leader.  If < $2,500, send to Returns Coordinator and manager
[If] Team Leader or anyone else completing form, then send to Returns Coordinator regardless of value..
Since the Inside Rep value is determined by what's in the pictured cell, won't the Inside Rep always be completing the form?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
No sir.  The inside rep won't always be the one completing the form.  However, they will be the one completing the form 99% of the time.  The exceptions would be if that particular Sales Rep is out of office, then their Team Leader or their Outside Sales Rep would be the one completing the form.  That's why I mentioned the fact that if the Team Leader completes the form, then it would be no need to have that form sent to them right after they just filled it out.  If that Inside Sale's Rep's Outside Rep were to fill out the form for them, and the value is > than $2,500, then it would send to the Team Leader.
0
Martin LissOlder than dirtCommented:
When I sit down to fill out the form it will show me as the "Inside Rep" so I assume that what you are saying is that when it comes to whom to send it to I have to look at the INSIDE_SALES_REP field in SalesOrderHeaders and compare it to the Environ UserName and that 99% of the time they will be the same (but obviously not in my case).
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Yes sir.  That is correct.
0
Martin LissOlder than dirtCommented:
This question is not directly connected to this new module but with the recent change to your email address I noticed the following in the various reports:

NIUF: You get a bcc copy
BOM: You get a To and a bcc copy
IIN: You get one or more To copies, a bcc copy via the Managers table, and a bcc copy
PO:  I can't verify this because I seem to have lost my OraclePOTable but it looks like you get the same as for IIN.

Do you want to get all those reports and more once this module goes live?
0
Martin LissOlder than dirtCommented:
There's a problem when comparing the Inside Rep Name on the sheet to either the INSIDE_SALES_REP OR OUTSIDE_SALES_REP AS FOUND IN THE SalesOrderHeaders database. The problem is that the name on the sheet comes from Environ UserName which is always(?) something like "MARTIN LISS", while in the database, those fields when they are a person's name (and sometimes they're not), are  always(?) "Liss, Martin". So what I'm going to do is to create a variable in the code that will reformat the Environ UserName so that it looks like the database names. There are potential problems with this (indicated  by the question marks) so if you can think of a better way please let me know.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
I have already provided that in the database.  I built a function which does what you are describing.
0
Martin LissOlder than dirtCommented:
I see this and not "BETSEY LANE", etc.
..
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
My apologies. It may be on a separate table, which, of course, is of no use anywhere else. Let's go with your description in the earlier thread. Sorry, I was trying to be helpful. Thanks for catching that too.
0
Martin LissOlder than dirtCommented:
And the question in post ID 39642384?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Sorry, I was using the mobile version of EE yesterday afternoon and did not catch your question in Thread ID 39642384. To answer your question, I do not want to receive all of the emails mentioned once this goes live.  I simply plugged my emails in each section for testing purposes.  Great catch.  That would have been a nightmare to receive several emails per request.  Sheesh.  Thanks again
0
Martin LissOlder than dirtCommented:
Okay, how do you want to deal with the email addresses? First let me point out that using Outlook's .Display method (as we do now in the code) rather than its .Send method that your email address doesn't have to appear at all and you can still open and look at the sheet the is "sent". So please tell me, by report name, where (to or cc or cc) if anyplace you want your email address to show up in addition to where it is generated via code because it appears in the "Managers" table.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Okay, hopefully I make sense below.  Basically, you can take me off of any cc or bcc that is currently setup and have me mailed as a manager.  For Returns Authorizations, please place me on the Managers table and the Returns Coordinator table.  Thanks again for all your help friend.  

NUIF:
 - Leave name only in manager's table.  No 'CC' or 'BCC.'

Kit BOM:
 - Leave name only in manager's table.  No 'CC' or 'BCC.'

IIN:
 - Leave name only in manager's table.  No 'CC' or 'BCC.'

Update SO (formerly Update PO):
 - Leave name only in manager's table.  No 'CC' or 'BCC.'

Returns:
 - Leave name only in manager's table; however, please add me as to the Returns Coordinator table as well.

 Thanks man!
0
Martin LissOlder than dirtCommented:
You are already in the ReturnsCoordinator table so I don't need to change anything there.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Okay.  Perfect.  I was trying to be as comprehensive as possible.  Thanks again!!
0
Martin LissOlder than dirtCommented:
For the BOM should I leave you as the "To"?
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
No sir, please just add me as the manager.  Wait, maybe we should create another table called 'Kit Manager' to work like the Returns Authorization process.  Would that be a big change?
0
Martin LissOlder than dirtCommented:
We can but that would be another question I think.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Roger that, I agree.  I'll keep that in mind as we wrap this module up.  That is actually a great idea because I have some slight modifications I thought of concerning the KIT BOM form's functionality with the NUIF.  That would be a series of questions there I think.
0
Martin LissOlder than dirtCommented:
One of the logistic problems that we have is keeping track of which Access tables that I use and which database they are found in, and a suggestion I made a while back concerning what tables should be in which databases when you upload them to me was an attempt at helping with that problem. I think I have a better idea though and that is that we should have only one database and that database should be ADS.mdb. As I'm sure you are aware we currently link from ADS to several other tables but if instead of linking we imported those tables we wouldn't have to worry about where the tables actually reside. It would be a big file to upload/download when we had to do that but if that turns out to be a problem we could extract any table that needed to be changed or modified into another database and just upload/download that smaller database.

Let me know if you see any problems with doing it that way. In any case I'm going to try it and see what happens
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
I think that is a great idea!  Like you said, the db would be quite large but it makes sense to extract the relevant table.  Do you think that could potentially speed up some of the searches?  Thanks
0
Martin LissOlder than dirtCommented:
It might.
0
Martin LissOlder than dirtCommented:
OK the file is half a GB but then again I don't have to keep any of several other databases around. Also I did a few fast tests and didn't run into any problems.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Sweet!!! Thanks man!
0
Martin LissOlder than dirtCommented:
When you test you may notice that the floating combobox for the RA sheet isn't working. It had to do with the way the sheet was locked and I've corrected it in my version.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Okay, this is what I have written up for the RA form.  I have also attached a process flow chart that shows how everything is processed.  I have listed out the line items which I’ve learned from testing:
 
• The Return’s Coordinator actually sends the form back to the Sales Rep IF a write off is needed.  The write off would be the bottom portion of our RA form.  
• If a write off is required, only the Part Numbers listed in the top portion of the RA form are to be used in the bottom portion.  So basically, the top table could simply be copied to the bottom.
• For the write portion of the RA form, we can add validations for the Customer and the Vertical.
                ¿ The Customer is based on the SO number and could be populated from that table.
                ¿ The Vertical is based on the Sales Rep.  We already have this setup in the top portion of the form.
• Once the form has been processed by the Returns Coordinator and a write off is required, the form is sent back to the Sales Rep and is complete.  It will be submitted to Accounting via the Sales rep apart from our Help Desk.
• The form is no longer being printed out for signatures.  If a signature is required, they simply type their name in the signature block.
Returns-Process.pdf
0
Martin LissOlder than dirtCommented:
• The Return’s Coordinator actually sends the form back to the Sales Rep IF a write off is needed.  The write off would be the bottom portion of our RA form.  
That's perfectly logical and it's an oversight on my part that it's not already factored into the code.

• If a write off is required, only the Part Numbers listed in the top portion of the RA form are to be used in the bottom portion.  So basically, the top table could simply be copied to the bottom.
Does that mean that it is always the same as what's above? In other words it is not free text and I don't need to +/- buttons for that part since it's protected?
• For the write portion of the RA form, we can add validations for the Customer and the Vertical.
                ¿ The Customer is based on the SO number and could be populated from that table.
                ¿ The Vertical is based on the Sales Rep.  We already have this setup in the top portion of the form.
The Customer is already validated against the dropdown list that is available there. You may not have recognized that there is a list there because if there's only one entry I automatically select it. If there is never more than one then we could protect the field.

The Vertical is already "calculated" and protected so I don't think any change to the code is required for it.
• Once the form has been processed by the Returns Coordinator and a write off is required, the form is sent back to the Sales Rep and is complete.  It will be submitted to Accounting via the Sales rep apart from our Help Desk.
Does this in any way change the rules that we discussed above concerning who to send the report to?
• The form is no longer being printed out for signatures.  If a signature is required, they simply type their name in the signature block.
Does this affect the distribution of the report or anything else from my point of view?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christopher WrightDirector, Service DeliveryAuthor Commented:
Does that mean that it is always the same as what's above? In other words it is not free text and I don't need to +/- buttons for that part since it's protected?
- That is correct.  I found that it will always be the same and does NOT need to be free text.  We won't need the +/- buttons either since it will be protected.

The Customer is already validated against the dropdown list that is available there. You may not have recognized that there is a list there because if there's only one entry I automatically select it. If there is never more than one then we could protect the field.
- There will never be more than one entry.  I think it is a great idea to protect the field.

The Vertical is already "calculated" and protected so I don't think any change to the code is required for it.
- I agree.  I thought the same thing but felt it was worth mentioning just in case.

Does this in any way change the rules that we discussed above concerning who to send the report to?
- No sir, this doesn't affect the rules pertaining who the report needs to be sent to.  The only thing that has changed pertaining to mail is related to the Returns coordinator sending the form BACK to the Sales Rep if the RC deems a write off necessary.

Does this affect the distribution of the report or anything else from my point of view?
- No sir, the distribution of the report does not change.  As I stated earlier, the only change really is adding the functionality for the Returns Coordinator to send the RA form back to the Sales Rep only if the RC states that a Write is necessary.
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
Thank you for answering all of my questions and supporting me throughout this project.  I am sincerely grateful for your help!
0
Christopher WrightDirector, Service DeliveryAuthor Commented:
I have created a new question pertaining to the "Return To Sender" functionality.  Link to new question is below.  Thanks man!


http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28300364.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.