Table Design help

[aron.ridgway]

Hi There

im after some advice im setting up a Purchase order System, i am now onto the Receiving orders section. I have an Order table and Order Detail Table.

I was thinking of setting up a Receive table and Receive Detail Table. Does this sound like the best way to set it up?

I want to receive the items in the OrderDetail and once they are all received it marks the Receive table as "Received" instead of "Live"

Any pointers would be great.

 

[Fran Lombard]

In a commercial PO system I once worked on it had a similar structure to the one you are proposing. There was a Receiving Header and Receiving Detail table used to record the shipping document that accompanied any incoming deliveries. This document was entered by the receiving dept and then processed to update the original P.O. line items for the quantities received. Since it is not uncommon for companies to sometimes ship partial quantities you need the ability to have multiple Receiving Documents per P.O. and sometimes multiple times for each P.O. line item.

Additionally, you may end up with discrepancies like over or under shipments that need to be tracked and ultimately resolved.

You may also want an additional status for the p.o. such as Partially Received.

Hope this helps.

 

[aron.ridgway]

Thank you Fran im glad im on the right lines, i have also added a partial received too. With regards to marking an order as fully received how did you go about this do you need to Code?

e.g all 3 items in the order detail table are fully received this in turn should now make the Order header Item status be fully received.

be a great help!

thanks

 

[Fran Lombard]

Yes it was done in code. Basically the P.o. receiver process code would read in the receiver then perform the necessary updates to the p.o. line items and header. This would include logic to interrogate all the detail lines as to status - if all were fully received the p.o. header was then also updated to fully received.

Good luck

 

[aron.ridgway]

Thank you Fran, can you give me any pointers or sample code before i get stuck into coding?

thanks

 

[Fran Lombard]

I don't have any sample code but I do have pointers.
I have found when ever I embarked on writing process code its best to map out the process in a flowchart/decision tree diagram before writing a stitch of code.

You start with inputs and outputs add decision points and branches of logic.
At the end of the day all process code is a series of record reads, record inserts and record updates with decisions as to what to do based on values encountered. If you have this visual map laid out in front you it becomes easy to determine if changes to the flow is needed and easy to decide what code is needed.

This flowchart can be sketched on a sheet of paper by hand or done more formally in a flowchart tool like Visio. Either way, the key point is to visually walk through the process from beginning to end before writing code.

If you want me to look over your flowchart or run into a specific problem in the coding phase feel free to post again.

Hope this helps.

 

[aron.ridgway]

Hi Fran

Im struggling with the setup of the Receive Detail Form, Have you got any pointers. I want the form to load working out the quantity already received for each item if any. And be able to receive more items which will in turn create a new record in the Receive Detail Table.

thanks

 

[jdraw]

I agree with Fran regarding pointers especially flowcharting with all options before trying to code. You may also have a situation where a "reasonable substitution" is received in place of the actually ordered product/item. You may not, but you know better than readers. All I'm suggesting is that back orders, partial deliveries, over/under shipments and substitutions are "real life" in many situations.
Good luck with your project.

 

[Fran Lombard]

Hi Arron

My approach would be to create a tabbed form for the Receiving document with the first tab being the Receiving Header and the second tab containing a subform datasheet for the Receiving Detail. I would keep the detail tab hidden until the header was saved and some event trigger the auto-population of the detail records I wanted. Since I like working in vba vs macros - I would open a series record sets on the P.o., P.o. detail and other receivers as needed to obtain the necessary information for auto-population of new receiving detail lines.

After populating the receiving detail table with these new records the detail tab would become visible allowing the user to enter quantities and other needed information.

Once this Receiving document was completed and passed all necessary validations, I would have a seperate Process that was initiated by the user to "Post" the document. This process would update any necessary records in other tables (P.O., On hand Inventory ....) and change the status of the Receiving Document to Processed thereby ensuring the document cannot be further changed or processed again.

I am sure the are other approaches, this is just how I would handle it based on my experience with the systems I've worked on and my preference for vba.

As mentioned by Jdraw there are a number of variations and discrepancies that are encountered in real life situations and you should plan for all t hoose that are known to be likely to show up.

Good Luck - Fran