Purchase Orders - Receipt and Outstanding Qty

[Jordonjd]

Afternoon everyone, (apologies if this is the wrong group)

I am currently trying to piece together Purchase Order and Goods Receipt to add onto a database.

I need to be able to receipt against the order line, either partially or complete.

Ideally i would like to essentially bring up a copy of the PO, with the outstanding qty displayed in the line, and then enter a Qty_in value.
This would save a transaction of x received against the PO item line, reducing the outstanding qty.


But i am not entirely sure how to tackle this, because there is a one to many from PO item line to the Qty_In table and i have to use an aggregate query to get the sum of the received qty's it makes the record set not updateable.

Does anyone have any useful tips, should i possibly have an outstanding qty in the PO detail table, or is there a clever way i could use an unbound field in the form (although i would like to be able to update multiple item lines so i am not sure how effective unbound controls would be, datasheet style item lines subform)

Anyway no rush, but any advice or nudging in a certain direction would be appreciated.

Thanks

Jordon

 

[CJ_London]

I presume you can't receive more than ordered on one order -i.e. you have two orders for item A and supplier delivers both orders at the same time

One way - you could have a multi select listbox to display the qty-in values, suitably filtered to the relevant order/item and excluding previously allocated then select the ones relevant to the invoice line. Perhaps also have an 'outstanding' textbox which calculates the difference between the order quantity and selected Qty_in's

Once you are happy, click a button to update the Qty_in records with the PK of the order lime item

 

[Jordonjd]

I presume you can't receive more than ordered on one order -i.e. you have two orders for item A and supplier delivers both orders at the same time

One way - you could have a multi select listbox to display the qty-in values, suitably filtered to the relevant order/item and excluding previously allocated then select the ones relevant to the invoice line. Perhaps also have an 'outstanding' textbox which calculates the difference between the order quantity and selected Qty_in's

Once you are happy, click a button to update the Qty_in records with the PK of the order lime item

Hi,

the process would be to select the po and only receive against that po/order, if there were multiple deliveries they would be processed separately.

Im not 100% sure what you mean on the listbox approach, I did think about a dlookup textbox but i wasnt sure how that would work on a datasheet style subform if it were to be referenced for stopping people from adding too many qty in, like ( 90 ordered and 100 qty in)

The only other thing i was thinking was to use something similar to the SAP GRPO, where you would lookup the po and then select the lines you want, it then appends them into the Receipt form and you enter Qty's, again maybe using a d lookup for outstanding displayed qty's.

I just really liked the idea of that simply, visual layout, almost like the northwind version, but with the outstanding qty and a stored transaction

 

[Jordonjd]

I put a dsum and calculated text field in, the Qty_in unbound text field doesnt work, but maybe i could just make a field in the table that i clear once i've used it to append the qty in transaction.. or something along those lines. anyway i will have more of a rummage through it all on monday

 

[Pat Hartman]

Not sure why you need to sum anything. I always have two options. Receive all and Receive partial.

Receive All, selects all items for the given PO and creates an append query that appends a row for each line item into inventory.
Receive Partial, moves down a level and then the user has to either Receive All or specify a quantity, each line item at a time in the given PO.

You probably do need to allow receipt of more than what was ordered since that can happen in the real world. What you do about it later is something else.

 

[Jordonjd]

Not sure why you need to sum anything. I always have two options. Receive all and Receive partial.

Receive All, selects all items for the given PO and creates an append query that appends a row for each line item into inventory.
Receive Partial, moves down a level and then the user has to either Receive All or specify a quantity, each line item at a time in the given PO.

You probably do need to allow receipt of more than what was ordered since that can happen in the real world. What you do about it later is something else.

I see what you mean, but when you use your receive partial, does it not display an outstanding qty if someone has already partially received against it?

The restriction on the extra qty is so the po can be amended by another user before the extra units are receipted.

 

[Pat Hartman]

It does but uses dsum() to ensure that the form is still updateable. It calculates the difference also and automatically populates the quantity field. So, if 30 were ordered and 12 were received already, the to be received quantity is defaulted to 18 in the current event so the user can just press save.

 

[Jordonjd]

It does but uses dsum() to ensure that the form is still updateable. It calculates the difference also and automatically populates the quantity field. So, if 30 were ordered and 12 were received already, the to be received quantity is defaulted to 18 in the current event so the user can just press save.

Ah ok I think I see what you mean.

I will have a try tomorrow and see what I can do. Thanks very much!

Would you have any advice for the record source of the 'qty in' field?
it can't be an unbound text field, so I wasn't sure whether to use the genuine field from the related "qty in" table I was going to use to store the transaction details.. or whether I add a field to the 'Po detail" table which is cleared once the value has been used.

I was not sure how I would make sure the field remains blank if I were to use the related qty in field, and the extra field in the po detail table seems like it's not a proper method to me

 

[Pat Hartman]

Your inventory receipts should append rows, not update rows. Every inventory transaction should be auditable. If you keep running totals, you have no audit trail.

An unbound control is fine since you are doing an append query and you can pass the qty as an argument.

 

[Jordonjd]

Your inventory receipts should append rows, not update rows. Every inventory transaction should be auditable. If you keep running totals, you have no audit trail.

An unbound control is fine since you are doing an append query and you can pass the qty as an argument.

Yep that's the plan, but I was hoping the display and each order line at the same time in a datasheet style subform, which is why the unbound text box isn't suitable (unless I'm wrong about that).

Anyway, I'll build a few drafts tomorrow and see how it goes, at least if I have any more questions relating to this, at that point I should be able to provide some screenshots etc so show what I mean a bit better.

Thank you for the advice

 

[Jordonjd]

Afternoon,

There is now smoke coming out of my ears, I am completely second guessing everything now, could someone please advise as to whether the structure of these inventory_in tables makes any sense? whether the ID fields are ok and whether two tables is a good idea.. my brain is fried and what was simple is now the enigma code..

I'm required to build a PO and inventory receipt add on to an old db, so once the transactions are created Ithen need to append them into the old tables in the old db.

 

[Pat Hartman]

Inventory has many faces. The top level for inventory is a part table which defines what the item is. The second level is the detail and is where you would store the received date and quantity and PO if you want to link them. If you have serialized parts, you would also have a serial number field that is filled in for certain part types. You probably need a type flag on the Part table to control this.

The inventory detail table also needs a transaction type. That way you can identify what comes in and what goes out. This is the reason for using a table separate from the PO. In the inventory details, you also have to record the results of physical inventory discrepancies. Your records show you have 25 widgets but you count 26. So, you make an adjustment transaction for +1

When you use/sell inventory, you do not adjust the record tied to the PO. You add a new transaction.

You can keep all your inventory transactions with natural signs. Plus for add, minus for subtract. Then to find how many you have, you sum all the transactions.

All we can do is tell you general best practices. We don't know what kind of items you are tracking or how varied they are. If you are tracking penny nails, you use one method but if you are tracking computers, you would use more rigid controls. If you have a mix, you need to accommodate all types and that is what the type code in the product table will help you with.

All related tables should enforce RI. In the Inventory_In table, you have two potential parents? why? If you do a conversion of the old data, you have one.

 

[CJ_London]

I suggest the following relationships

PO>PODetail on POID=POID (with RI)
PODetail>InventoryInDetail on PODetailID=PODetailID (with RI) (you don't have this field in your InventoryInDetail table)
InventoryIn>InventoryInDetail on InventoryInID=InventoryInID (with RI)

Don't see why you need POID in InventoryIn or itemID and OrderLineID in InventoryInDetail

You can make the second relationship no RI 'just in case' you receive goods without an order. If this is the case, you will also need an itemID field to identify what was received

 

[Jordonjd]

Thanks both for your replies,

I was thinking of a inventory "in" transactions table and an inventory "out" transactions table as opposed to having both in the same table, I am not entirely sure why now, but that was what i was thinking.

For a transactions table, storing +5 of x and -10 of y... is it not normal that the table would have several possible "Parent ID's" from other tables, like Purchase Orders, Sales Orders, Stock Counts, Internal Productions, wouldnt each of these have different PK's and even if they were all autonumber without clashing, you would not be able to enforce RI? because there would technically be orphan records?

In the mixed transactions table, do you mean storing the negative values as -10 ?

The Order_Line_ID was my idea of storing the line id's of the possible multiple parent tables, i.e. the po_Detail_ID or the Order_Detail_ID and that was why it would not have RI

I think I have become very confused with RI and a transaction table that could potentially have multiple sources

 

[Jordonjd]

I just realised this is in the Query thread and I have moved into another set of questions really. I can start fresh tomorrow and post new threads in their relevant threads etc

 

[CJ_London]

In the mixed transactions table, do you mean storing the negative values as -10 ?

it is up to you. The rule of thumb I use is if manual input use all positive, if imported, use the same as the import data.

it is easy to calculate the negative value when required based on transaction type - I usually include a multiplier field populated with 1 or -1 in the transaction type table. Also easier when you want to match values - a simple sort on the amount field will put in's and outs of the same quantity next to each other

I was thinking of a inventory "in" transactions table and an inventory "out" transactions table as opposed to having both in the same table

again, up to you. My preference is one table - makes things like calculating stock in hand a lot easier - and matching values as previous paragraph

Purchase Orders, Sales Orders, Stock Counts, Internal Productions, wouldnt each of these have different PK's and even if they were all autonumber without clashing, you would not be able to enforce RI? because there would technically be orphan records?

At some point, yes - you will need to 'dive off in a different direction' so you will not be able to enforce referential integrity through the BE, but you still can using validation rules in a form. Unless you can store PO's and SO's in the same table because they are intrinsically the same - but then when you need to look up the customer or supplier from the transaction, you dive off at this point instead - unless you can store customers and suppliers in the same table because they are also intrinsically the same and so it goes on.

Keep them separate and what then when you want to calculate stock in hand, compare values, track movements. You are probably going to need to use a union query - which negates the use of indexing and therefore will impact on performance,

Sometimes it pays to have some fields which may or may not be completed depending on whether it is a supplier or a customer.

In summary, there is no 'best way'. It comes down to the most efficient way to meet the needs of the business - and only you know that. RI is a useful tool to use, but sometimes there are better ways

 

[Jordonjd]

That is very helpful,

I think I am going to call it a night, sketch it out again tomorrow and have a fresh look at it before I move forward.

Thankyou both for the advice and your patience!

 

[Pat Hartman]

You can make the second relationship no RI 'just in case' you receive goods without an order. If this is the case, you will also need an itemID field to identify what was received

No. You would enforce RI but you would make the FK NOT required AND you would set its default to null. so you don't have to specify a PO. However, if you do enter a PO, you MUST enter a valid PO. That is how RI works. However, when FK's are optional, you have to use left joins to avoid losing the records with the null foreign keys.

Just FYI, you should ALWAYS remove the 0 as the default for foreign keys anyway. All FKs should default to null. Some will be set to required but not all but no default is valid so leave it as null.

 

[Jordonjd]

ahhh i see, i completely forgot about that, i was losing my hair over the error message about needing related records in PO_ID table.

well again, thank you both very much, i will have another try tomorrow

 

[Pat Hartman]

In all the years I've been designing databases (50+), the ONLY reasons I have ever encountered for not enforcing RI is bad data that for whatever reason, the client wants to keep. OR because the relationships are incorrect and the client doesn't want to fix them up OR because of timing, the user wants to be able to create incomplete records that will later be validated. You can do this with flags and coding in a form.

There are rules that are hard or even impossible to enforce with declarative RI. That's when you might want to use triggers if your BE is ODBC or DataMacros if the BE is ACE.

For example, if your business logic requires at least one child record in a 1-m relationship, then you need programming logic in the main form to check when it closes if a child record has been added and if one hasn't, do something intelligent. Some people resort to unbound forms for this or even shadow tables but if you know about the rule from the beginning, it is easy enough to program around and to create your queries to exclude the 1 side records with no children.

The problem with relying on forms to enforce basic RI is that sometimes you have to enter data using other methods. Then you have to duplicate your validation logic or ignore it. Both bad practice.