Sort OrderedItem list, assign 1 OrderNum per Supplier & send email

[T0bias]

Hello everyone,

I'm currently working on a project to automatize the ordering process of samples and to manage an material library.

Architecture:
Availability status tables, linked to exported data info tables (.xls) which also hold data like location and also the suppliers, so there are several records for each product, each for a different supplier. The supplier contact info is in another table.
Also for the ordering process, I have a "OrderedItems" table, which lists every item ordered and I'm planning to link a "Orders" table, which summarizes orders, if there is more than one item ordered per supplier.

That already brings me to my problem:
Whereas I was able to manage to create everything for a single item order process, I'm not able to think of a suitable vba/macro solution, when there is a whole list of different items to order.

Right now, the single ordering process is structured like that:
Input (Material Number) -> Order Form with all different suppliers as different records and "Order now" button. Function appends selected Item to OrderedItems list and generates mail to the supplier, open to review and send. For assigning a order number I used the

OrderNum = Nz(DMax("[OrderNum]", "OrderedItems"), 0) + 1

function, as every order only contains one item. The mail generation is afterwards done by pulling things like receiver info, supplier info (mail, name,...) out of the OrderedItems table and the generation of a Outlook.Application.
That already works nice


However, when it comes to automating the process, if there are several items to be ordered, I'm hitting my conceptional vba limits


So what I'm trying to do is:
-Add Items to OrderListTEMP by "collect orders"-button, not directly order it (already done)

On the click of another button, that list should be further progressed:
-sort by SupplierName
-assign only one order number per supplier order, not per ordered Item
-prepare one mail per supplier, of course this time not only mentioning one but several items
-append that order to OrderedItems table, delete it out of the OrderListTemp, maybe also add that order number to the "Orders" table


So I'm relatively new to coding:
I know that I'm going to need a loop going through the records, assigning one number (+1 to last used one) per supplier, collecting the different numbers in one string and inserting that string into the mail instead of the single number. After that do the same thing with the next supplier until none is left. Afterwards delete the records out of the temp-table (or do that after every set of records?)

However, I'm not able to put that into code

Any help, how to do something like that would be greatly appreciated.

I'm also attaching the current "single order"-mail code as I use it


Thanks in advance,
Tobi

Public Function sendmailF()

Dim strmail, strBody1, strBody2, strBody3, strCC, strMatID, strName, strCountry, strMerchInCharge, strBuyer, strOrderNum As String
Dim objOutlook As Outlook.Application
Dim objmail As Outlook.MailItem
 
'Creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.Application")
Set objmail = objOutlook.CreateItem(olMailItem)

'Creates strings
strmail = [Forms]![SingleOrder_F]![mail]
strMerchInCharge = [Forms]![SingleOrder_F]![Merch]
strMatID = [Forms]![SingleOrder_F]![Ref_num]
strName = [Forms]![SingleOrder_F]![Name]
strCountry = [Forms]![SingleOrder_F]![Country]
strBuyer = [Forms]![MaterialInput]![UserName]
strOrderNum = [Forms]![MaterialInput]![OrderNum]
 
'strBody definitions (contain confidential info, therefore I'm not gonna include them here)
 
'Creates and sends mail
With objmail
    .To = strmail
    .CC = strMerchInCharge
    .Subject = "Fabric Sample Order #" & strOrderNum & " "
    
    .Body = strBody1 & strBody2 & strBody3
    .Display
    
End With
Forms!MaterialInput.Form.Requery
Forms!MaterialInput.Form.Refresh
'Set objmail = Nothing
 
Exit Function

P.S: every "mail" had an "e" in front of it, however I had to delete that for submitting the post as I'm not allowed to include email addresses with <10 posts

 

[T0bias]

Anyone? Maybe also parts of the process?

Thanks

 

[The_Doc_Man]

Good morning, Tobias. I don't know from where you posted, but my system says that the WHEN of posting was at 4:35 AM - which might explain why nobody answered before now. I'm a night-owl, but even I am not up at that time in my time zone. Remember, with the spread of time zones for a global forum, it often takes half a day for late posters to get a response. For future reference, if you feel the need to "bump" your question, give us a bit longer. Don't take that as anything more than a reminder of the scope of this forum.

Now, on to the problem.

Whereas I was able to manage to create everything for a single item order process, I'm not able to think of a suitable vba/macro solution, when there is a whole list of different items to order.

However, when it comes to automating the process, if there are several items to be ordered, I'm hitting my conceptional vba limits

Explain a bit more about this process. You say you are sending out samples but then talk about an ordering process. Who is ordering the samples? You or your customer / client? How many samples are ordered per single shipment? How do you intend to distribute the samples among recipients?

The reason I'm asking it this way is that you intermixed lots of thoughts with techno-jargon but perhaps it wasn't a clear picture of your goal. That's why we often ask (and I am asking now) for the English-only description of the process. The techno stuff comes later once we wrap OUR heads around your problem. And please don't take that as a "ping" because TRUST me, you aren't the first person to ask a question this way.

I'm going to give you general advice first, and it might help you to compose your explanation that we will need, because what I'm going to tell you applies not only to you but also to us.

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

That means that you need to thoroughly design your process on paper first so that you can implement it. Access is an inanimate object despite the frequency with which we often insult it. It's dumber than a box of rocks, so YOU have to supply the smarts. It knows nothing about your project. You have to supply the ideas. The reason it applies to US is that if the question is not narrowly targeted, then WE have to understand your problem in good overview in order to give you suggestions and hints. Which is why I asked for an English description of the problem.

I know that I'm going to need a loop going through the records, assigning one number (+1 to last used one) per supplier, collecting the different numbers in one string and inserting that string into the mail instead of the single number. After that do the same thing with the next supplier until none is left. Afterwards delete the records out of the temp-table (or do that after every set of records?)

Because you have external tables in Excel which are linked, and are talking about your records having numbers, it leads me to a question that is relevant to how I or anyone else would advise you. Do those tables have to stay external or when you automate this can you move the tables into an Access table? (Is there a hard-and-fast business reason to NOT move them?) Because if you import those tables into Access, you can just set up an autonumber field in the target table and you will never need another loop to number them. Then adding a new supplier just creates a new autonumber record. The only issue that some people have is that autonumbering can sometimes skip a number if the record insertion fails.

Again, since we can't see your business rules, we can't tell if there is an issue that requires contiguous numbering - which is why some folks use the "DMax+1" method such as you described. This is why the initial description is so important.

 

[pbaldy]

Pssst. Doc, 4:35am...2 days ago.

 

[The_Doc_Man]

And who says I'm fully awake when I read these things, Paul? This forces the cobwebs out of the brain (mostly, except for those particularly well-ensconced ones) when I wake up. But you are right, I didn't catch the date and I now understand why the bump.

 

[T0bias]

Hey Doc, hey Paul,

Sorry if I wasn't able to explain everything understandably, it's harder than one would think if you are working with that database for several weeks

So, to come to your questions:
The process is about ordering material samples for a library, so me or someone else of our department is the one who orders. As it is only a very small area, compared to the whole company, I have to accept some given infrastructure. That is e.g. the need to import the material info from external sources, as there already is a big, up-to-date oracle database for the production orders.

Apart from the material info, I have an internal table for availability info in the library, so whether the sample is currently available or not, if it should be ordered ..... and another external linked table with all the supplier info in it.

Concerning the ordering process, I currently have an OrderdItems access table, which also uses the auto number function to ticket every ordered item - which already works well if there is only a single sample to be ordered.
However, when there is a need for multiple samples, e.g. after checking the inventory, it happens, that there are several samples needed from a single supplier. Obviously, I want to avoid that one supplier is sent multiple emails with only one item ordered in each.

I already was able to completely realize the process for single orders, so my problem is already quite tangible:

Instead of sending out a single mail after selecting item&supplier from a form and appending that to my OrderedItems list with a single number, I have another button which adds it to a (temporary) table.

The actions that have to be done are pretty clear but I'm lacking the programming skills to realize it:
1. Sort the created temp order table by supplier
2. "Group by" supplier, so collect all records of a single supplier and only assign a single order number
3. Send out the same mail as already created for single orders, only with maybe several items


So, most of my text was trying to explain the surrounding conditions (which it obviously didn't completely ), the problem I have really just is about "grouping" the table by supplier, assigning only one number per group, and then inserting all of the items in the group into a string for using it in the email.


Thanks already and also in advance

 

[The_Doc_Man]

Typically, when you want to change a single order for a single item to a list of multiple related items in one order, you break the items into a child table dependent on the order, which stays single. And even after this reconstruction, if a given order IS single, you put the item in the child table just to make the algorithm consistent. Your child table could have two fields to make a compound key, where one part is the PK of the order and the other part might be as simple as the DMax+1 of all items with the same order number - though DCount might be better since it handles the null case better and gives the same result for this case. Of course, based on principles of normalization, you put order details in the order table and item details in the item table.

What you would then do is, when preparing your e-mail, you still access the order BUT you use a query rather than the raw order table or raw item table. Make a JOIN between orders and items-in-orders. You would construct the join ON the order number that appears in the order table and items table. It could be an INNER JOIN since you aren't expecting to have no items in the order. This query would then have two members in its ORDER BY clause, the order number first (because that's the independent item) followed by the numbers for each item. Whether that is a supplier number or something else is up to you, not me.

If you are not sure about the correct syntax for the JOIN, a strategy that works well is to establish a formal relation between the two tables and use the query design grid on the two tables. The designer will recognize and honor your relationship, so you can then just drag-n-drop fields from the tables. Then save the query and switch to SQL view to see how it was done.

The code that constructs the body of your e-mail would then have to build several parts, some of which could possibly fall into the "boilerplate" category. I'm thinking along the lines of a string function that will return the partly or completely constructed mail body.

The heading and introduction portion of the body might be constant. The order number information could start off with order-number data including shipping number, dates, weights, or whatever else you do. The detailed contents information on what is in the order. If there is a closing statement, that might also be constant.

The way I would approach that is to have a subroutine to build the body based on an input order number. In the routine, have a recordset that is based on your join query. Open the recordset, do a .MoveFirst followed by a .FindFirst with the order number as the thing to be found. Before stepping through the list of items, use the first record to build the order information. Then start the loop that takes the record's item info and builds lines to describe the items. At the end of the loop, if you are not already at .EOF for that recordset, do a .FindNext using the order number again, looping until you finally reach the .EOF=True situation. Do any final comments and return the body of the message. Don't forget to .Close the recordset before you exit. Once it is closed, the rest of the things that need to be done will be handled during the Exit Sub or End Sub processing and is of limited concern.

I have used the proper method names and have given an explicit suggested algorithm. You can look up some of the methods I used (beginning with ".") to see what they do. You also need to look up JOIN queries and parent/child tables.

This forum has an excellent Search functionality in the skinny blue ribbon near the top of each pages, third item in from the right. If you need more, there is always the search of the great Google brain or whatever other search engine tickles your fancy.

 

[T0bias]

Okay, that's quite something to start with

I'll try to implement that within the next days, and if any other questions which aren't answerable by just searching pop up, I'll ask them here. Otherwise I'll report back when I'm finished.

Thank you!

 

[T0bias]

I managed to implement the functionalities

If anyone is reading this, having similar challenges I'll put together what I did

What I did for numbering:
-Load data of temporary order table into two recordsets ordered by supplier, make one go to first record and the other one to second. Then, a loop checks for every row whether these two records have the same supplier. If not, a new order number is given to the record otherwise it's still the same.

Multi ordering was implemented in a quite similar manner:
Go through the numbered temporary order list. If record has the same number, append Material to email body string, otherwise open a predefined email for that supplier, clear string variables and load them with next record.
Afterwards update "OrderedDate" to date() append the temporary table to orderedItems/orders (linked by OrderNum), each piece of information where it belongs. In the end clear the temp table.


It got a little bit more complicated, because I needed to implement different receiver info for a hand full different centers around the world. I did this by having a receiverInfo table, and checkboxes for the different receivers on a form (for single orders) and in the tempOrderTable for multiple orders at once, respectively.


Thanks Doc for your inspiration

 

[T0bias]

However, I encountered another challenge which I'm not able to find something about (probably because I'm not sure about what exactly to search for).

So, I have two tables linked with an one-to-many relationship, named "Orders" and "OrderedItems". As it often happens, that orders arrive incompletely, I have a "ReceivedDate" tab located in the "OrderedItems" table. Now I want two Boolean fields in the "Order" table, directly showing if something is "received completely" or "partly received".
So what I want to do is check if the Many-side (Ordered Items):
-> all have some value in the "ReceivedDate"field --> "Received completely" checked
-> some have a value in the "ReceivedDate"field --> "Partly received" checked
-> none have a value in that field, so both checkboxes remain unchecked.

I thought about realizing that through an update query, however I'm only able to update the fields if either none or 1+ of the records has a value. I wasn't able to manage the process, that a field should only be updated if either ALL of the records of one OrderNum are notNull or 1+ but not all are Null.

How to implement that in code or a query?

 

[The_Doc_Man]

To have two different states with two different (though related) meanings, you need two Boolean flags or one multi-state byte integer where your values can be something like

0 = nothing delivered OR perhaps delivery pending
1 = something delivered OR partial delivery
2 = everything delivered OR delivery completed

It's your problem so the meanings are your choice. Either two Y/N fields or one state field with more than two possible values would do the trick for you. However, to keep this from getting crazy on you, there might be a little bit of overhead, like a flag for the order table to show that you don't need to worry about it again for a given record; perhaps an OrderClosed flag to show that you are done with it.

As to the queries: For the "two Y/N" option, do TWO queries. When you are about to evaluate the statuses, do this

UPDATE Orders SET PartialOrder = FALSE, CompleteOrder = TRUE 
WHERE OrderClosed = FALSE ;

UPDATE Orders INNER JOIN OrderedItems on Orders.OrderPK = OrderedItems.OrderFK
SET Orders.PartialOrder = Orders.PartialOrder OR OrderedItems.ReceivedDate > 0, 
      Orders.CompleteOrder = Orders.CompleteOrder AND OrderedItems.RecievedDate > 0
WHERE Orders.OrderClosed = FALSE ;

{optional to do this here or later, but...}

UPDATE Orders SET OrderClosed = TRUE
WHERE OrderClosed = FALSE AND OrderComplete = TRUE ;

The fine point to remember is to set OrderedItems.ReceivedDate to 0 when you create the record initially so that the test in the UPDATE will work.

 

[T0bias]

Worked perfectly, thank you very much! If you once see it that way, it is a completely logical solution, however I wouldn't have ever thought of it myself. My project is now almost finished and I'm primarily working on a clearly arranged and good-looking UI.

So maybe I'm coming back with one or two questions about bugfixing, if any show up in the first weeks of usage of the ordering system.

Great board, I really appreciate your help

 

[Minty]

You can, if you have suitable fields, calculate a items status without needing to store and try and update that value based on multiple criteria.

You often see status flags used inappropriately, when there isn't any need for them. For instance if you had a "Shipped date" on an item, there is no need for a "Has it shipped" flag, as you can always check to see if there is a date present.

Similarly, if you have qty ordered on an order line, and a list of shipped qty's against those order lines, you can calculate if that order is partially completed, fulfilled or if that order line hasn't been processed yet.

Now these type of calculations can get quite complex quite quickly, but if your data is stored correctly, you can build this status calculation into one function and use it whenever you need to show it. Because it is calculated, not stored, if any underlying control data is updated, your status automatically changes to reflect those changes. You aren't reliant on a form event at a particular point in time to make that change for you.

It may seem like extra work but can save a lot of headaches further down the line.

 

[The_Doc_Man]

Minty is of course quite right. Technically you don't need the status flags, but they can be the basis of faster decisions OR easier decisions.

I chose that method based on your claim to be relatively new to coding (which implies a slightly lesser level of experience in other aspects of Access.) As you grow and start thinking more about the numerous alternatives, you will learn about sub-queries that could be used to dynamically evaluate those flags. TECHNICALLY, having those flags in the parent table very slightly denormalizes that relationship. However, as a quick and dirty (and simple) solution, sometimes a pragmatic approach is preferred.

 

[Minty]

@Doc - I wasn't making any criticism of your post at all, and sorry if it came across as that. Your advice was completely sound for the OP level of experience.

I really only added my post because having inherited some very "status centric" processes I've recently changed a lot of them over to a calculated status, and it's a pretty time consuming thing to do after the event.

 

[The_Doc_Man]

No problem at all, Minty. In fact, your point is important for the OP to know about. As you well know, working with Access is a perpetual learning experience and as you can tell from my posts, you can take the teacher out of the classroom but you can't take the classroom out of the teacher. So your comment gave me a chance to revisit the classroom for a very brief moment.