Line item portal not working for purchase order

[my4sons]

I have a basic customer database. Each customer has specific related departments and products available for order. Within each department there are related products. Also each product can appear in more than one department. I have created a layout that has the customer information and a portal for the related products to that customer. The problem is when I try and perform a calculation of products ordered minus products shipped, any calculation I enter shows up for all the products in the portal. How do I limit the quanity ordered, shipped, and calcualted difference to only relate to one product in the line items portal on the layout? Thank you in advance for the assistance!!

[Fenton]

First, it was brought to my attention, as a moderator of the Relationships forum, that you'd double-posted this question. That's a "no-no," because then the answers become fragmented and continuity is lost. So I deleted the duplicate. Most of use read the forum as a list of the most recent posts, rather than "forums," so double-posting doesn't make your question more visible.

I wonder what/where your calculations are that they are totalling all items in the portal. Usually people ask the opposite question. It sounds like you're using Sum(portal relationship::amounts) in the Order table.

To get the total for only 1 item, you'd just do normal calculations in the line items table, and show the result in the portal line. But that is so usual that I wonder if that's what you're asking.

Also, it sounds like each product in the portal has numerous quantities, some of which are shipped now, and some later. To keep a record of what was shipped you're really going to need another table, sort of a "shipped" line items of a single "ordered" line item.

[my4sons]

Sorry about the double post. I will keep that in mind. Thank you for the information. I will work on this further.

[Fenton]

Yes, this is a tough situation, when only partial quantities are fulfilled on an order. It seems you should probably separate the "Order" (ie., the original order) from the "Shipped Out" (when items were actually sent out). They are really 2 different things, though they might happen at almost the same time.

They would each have their own line items. Shipped line items would start out as a duplicate of Order line items, for any particular Order. But then some of the quantities might be decremented. Some of the items might be deleted altogether, if none are being shipped then.

Alternatively the Shipped items could be chosen 1 at a time (if only a few are shipped). But this means you'd have to decide whether a new Shipped record needs to be created at that time.

Subtraction between the two would show how many remain to ship. These could be shown in a portal in either the Order or Shipped, with a filter on the value list items (only filled quantities being filtered out). The next shipping would be done in a new Shipped record, which would only have those line items being shipped at that time.

This would give you the flexibility to either have a 1 to 1 relationship from an Order to Shipped Order, or a 1 to many, ie., several shipments to fulfill the original order. Or possibly you could only have shipped records for invoices that were not immediately and fully fulfilled. That would save records, but would be more complex.

Perhaps one of the real invoice/order experts will chime in. Ugo DI LUCA had a white paper with quite a complex invoice/order/purchasing chart. Don't know where it is though.

[Ugo DI LUCA]

Hi,

The paper you are referring to should be in the Article Section, but was done with FM5 in mind and this actually makes quite a big difference with what can be done with 7.

The logic remains though.

When working with businesses involving partial shipments of orders, or partial orders of quotes, or on the contrary fusions of more than one order into a shipment or/and multiple shipments into an invoice, the solution should begin within a specific construction of the Line Items.

Basically, having a Line Items where one same record would hold either the orderID and the shimentID (even with different quantities) is troublesome. Splitting the Lines into 2 separate Tables also prevents for any real comparisons from one "document" to the "other", as requested here.

Having faced these situations, I would actually highly recomand to integrate these scenarios even when they shouldn't happenr, as noone knows how business would evolve. When this wasn't taken into account from the start, it becomes a real nightmare to rethink the database diagram, or to adapt the solution for some oddities.

I was going with a 4 files (Quotes.fp5, CustomerOrder.fp5, Shipments.fp5, Invoices.fp5) and a Line Items file stucture in FM5, but I recently migrated to a 2 Tables solution with a SalesDocument Table and a SalesLineItems with FM7.

With such solutions, you need to "track" each line individually, from the very start of the Sales decision to its ending point, and this, IMO, is done by introducing a TrackingId and referencing it as the main key of each transaction and document.

Here's attached, in case you can dissect it, an extraction from my own graph.

I adopted a Shopping Cart technique where once selected a Customer, you may pick an item

- from his opened lines, including those partially shipped

- from the Catalog, with a way to match either old purchases made by this customer for this product or eventually again all possible opened lines for this product and customer.

I sligtly changed it to match the Shipments only, but the process is always the same, whatever the document we are building (Quotes, Orders, Shipments and Invoices).

Hope this isn't too confusing.

[Fenton]

So, what you're saying is "keep 1 line items table," but "create a new line for each new transaction (shipped quantity) for that item"? And tie the line items together with the original Order ID (a self-relationship within the line items table)?

Each shipment would also require a new "documents" record, with its collection of line items?

That would keep the tables down to a minimum, and only create redundancy when necessary; ie., when an item had a partial quantity (rather than every time such I foolishly proposed -).

[Ugo DI LUCA]

Yes, partly, Fenton,

I'm using 2 line items (one for the Sales, another for the Purchases) and 2 documents Table (I was thinking of one only...).

There are in fact many situations of partial shipments of orders, but let's take 2 examples.

ex One order of 3 lines

Product1 -Qty = 50

Product2 -Qty = 100

Product3 -Qty = 40

Conversion ex.1 :

Shipment N

[Fenton]

If you are using 1 line items table for Orders (we won't consider Purchase Orders at this time), then I can see how you could use 2 line item records to split an ordered vs. shipped amount for a specific ordered product.

Would the originally ordered amount (50) then be in its own field? So you'd have separate Ordered Amount and Shipped Amount fields?

Or do you just have 1 field, Amount, and always generate a new record for every transaction; ie., order is 1 record (50), shipment is 1 record (30)? But what if the shipment is for the total quantity amount? Do you still create 2 line item records?

Also, you only have 1 Documents table, with both Orders and Shipments, using an auto-entered field to say which is which?

I'm just trying to clarify this. I've done something like the Documents table, using 2 ID fields, one the serial ID, the other a common ID (the original order serial ID).

But I've never done the "trackingID" in the line items (haven't had partial quantities yet). It's a great idea.

[Ugo DI LUCA]

Hi Fenton,

The structure of the line items, in term of fields, is rather easy, but the process is heavyly scripted, to keep it fast.

LineItem Structure

_____________________________________________________________________________________________________

lineItemID - unique random ID

customerID - text

documentType - text - 1 for quote, 2 for order, 3 for shipment, 4 for invoice

documentID

quantity

price

lineTotal

c_quoteFilterKey - equal to "1" (referencing documentType-could be auto-entered text)

c_orderFilterKey - equal to "2"

c_shipmentFilterKey - equal to "3"

c_invoiceFilterKey - equal to "4"

trackingID - text indexed - unique Random set by script once and propagated later on -

Then 3 indexed textFields that get scripted along each transaction, which are the result of calculations within 'selfjoins' in the Line Items, and that are used to filter down the lines to the tragetted ones...

notAllOrdered

notAllShipped

notAllInvoiced

For example, notAllShipped is the result of a SetField[ ] using the context of the PreviousDocuments in the Graph, and converting the result of this calc.

Case(

//This is an order//

documentType=2;

//no line with this trackingID and a documentType = 3 (shipment)//

Case(IsEmpty(ShippedLines::lineItemId)

//OR//

or

//There is at least one line with this TrackingID and a documentType = 3 (shipment), but the quantity shipped is inferior to the quantity ordered//

not (Sum(ShippedLines::quantity) >= quantity);

//Returns Line ItemID so we can dig farther into the relation by referencing it//

lineItemId;"");

//The Document is a quote//

Case(documentType=1;

//There is no line with this tracking ID and a documentType =2 (order)//

Case(IsEmpty(OrderedLines::lineItemId)

//

AND//

and

//This quote wasn't directly shipped either - because it can happen that the customer is delivered from stock without any order//

IsEmpty(ShippedLines::sl_lineItemId);

//Return the LineItemID or nothing for further investigation in the relationship//

lineItemId;"")))

As you may see for this "calc" structure, the process being the same whatever the documents, you'd be increasing the documentType to filter either the lines to be ordered from the Quotes *point of view* or the lines to be invoiced from the Shipments *point of view*.

Documents Table structure

_____________________________________________________________________________________________________

documentID - unique Random autoentered

documentDate

documentNumber - the classic Quote#, Order#, etc.

documentType (1 for quote, 2 for order, 3 for shipment, 4 for invoice)

c_PreviousDocument =

Case(//The document is an order//documentType="2";"1"//previous document is a quote//;

//The document is a shipment//

sd_documentType="3";

//result can be either a quote or an order//

"1

[Fenton]

Wow. Thanks Ugo. It's seems pretty clear. The basic idea being that you can handle the progression of an item through the process by using a document type for the overall transaction, and tracking# for the indvidual line item (which may have more than record as it moves through the process).

I'm intrigued by the "g_magicKey," which I can't recall. If it's not too much writing, do you have any documentation or link to that?

[Ugo DI LUCA]

Hi,

The magicKey is something I first referred as an oddity, and then decided to use as a feature for creating related records on a multipredicate join without even moving to a specific layout nor specify globals to be passed through as with FM6.

Bruce was one of the few to read my long post here and on Experts a while back, he has "officially" nothing to do with this "magic" word, but he sure helped me a lot to re-interpret this oddity.

Sorry I'm in a hurry but if you do a search for "magic", you should see a few posts.