Creation of portal record makes new record in table

[BigDoom]

I'm trying to setup a backorder system for my invoices.

I now have an invoice table with a related list items table for use of a portal on my layout which actually displays the invoiced parts.

On my invoice layout I create a new record and on that record I have a portal in which I add the parts ordered and delivered to the customer. The portal works just fine and creates new records in the list items table. In the list items table I created a calc field wich uses qty ordered and qty packed to calculate the amount that's left in backorder.

I want for each record that gets created in the list items table to create a new record in the table Backorders and fill that with item ID and qty in backorder from the list items table. Also i want to add the name of the customer from the invoice table (but that's secondary priority).

For some reason I can't get it to work to create a new record in the backorder table when a new record is created in the list items table via the invoice portal.

The reason I want to use a separate table for my backorders is that if I want to delete backorder records it may absolutely not mess up my invoices! To keep my backorders clean only with parts that are in backorder and not parts with 0 in backorder, that's a little confusing and messy.

I don't know if it's even possible to do or maybe I'm just missing something.

Current relations:

Invoice <-> Invoice List Items

I try'd to use this relation but it didn't work: Invoice <-> Invoice List Items <-> Backorders

As related field I use invoice ID for Invoice <-> list items

[imoree]

Remember that you may have to go to the related table layout, create the unique id to establish the "VALID RELATIONSHIP" then go back to original layout. YOu can also use a global field to transfer id from 1 table to next to establish the relationship value. RElationships have to be valid in order to use a portal as well as your "CONTEXT" needs to be correct as well.

YOu have given us very little to work with.

[BigDoom]

The portal works fine but bassicly what I want is to fill another table with some of the data that I add in the portal.

[imoree]

So in order to do that you need to have that other table related to your portal table occurence. NO?

If so, then copy the portal ID to NEW TABLE_ID then copy all teh data using a script.

BY the way i am still learning as well..

[BigDoom]

Yes I thought that too, but if I setup as follows:

Table Invoice List Items: List Items ID <-> Backorder: List item ID and select allow creation and deletion of record for the backorder table nothing happens when i create a record :S

Probably because I create new records in the table list items via a portal in the invoice table and therefore it's not possible to create a new record in a third table that is related to list items.....

[LaRetta]

"For some reason I can't get it to work to create a new record in the backorder table when a new record is created in the list items table via the invoice portal."

An action needs to happen where you actually set any field in the BackOrder table. You have not provided enough information to give you an exact answer but let's consider this: You have fields in LineItems for InvoiceID, LineItemID, CustomerID. Create those three fields (matching data types to same as in your LineItems) in your BackOrders table.

Relate them as:

LineItems::LineItemsID = BackOrders::LineItemsID

AND

LineItems::InvoiceID = BackOrders::InvoiceID

AND

LineItems::CustomerID = BackOrders::CustomerID

In the relational graph below on BackOrders side, enable 'allow creation of related'. Now when you set any field in BackOrders such as Quantity backordered, while in the LineItems portal or layout, it will insert a new BackOrder record and set the Quantity field. It will also nicely set the Backorder CustomerID, InvoiceID and LineItemID in BackOrders.

The button must be in the portal row to fire the script. And if you put the BackOrder quantity in LineItem portal (to show the quantity you are setting to backorder) remember that it will require a refresh to display the value so end your script with Refresh Window [ flush cached join ].

[comment]

I want for each record that gets created in the list items table to create a new record in the table Backorders and fill that with item ID and qty in backorder from the list items table.

You'd need a script to create such record. However, I can see no reason to create a record with no new data of its own.

[imoree]

i would agree with comment..

[LaRetta]

Comment said, "I can see no reason to create a record with no new data of its own."

But there will be new data - the amount remaining on backorder which may split the quantity of a single LineItem. It is a 1:1 relationship to track what was originally requested and on what order. Invoices must be posted (frozen) and closed. Items lingering on an invoice because of backorders screw up accounting. LineItems tie directly to AR, backorders do not.

A backorder should never be charged until it is shipped at which time the quantity is added to a new invoice referencing the original invoice with an alpha suffix. I've always used a BackOrder table and a Returns table.

UPDATE: As an aside, you don't need script if you can tolerate no refresh. Just place the backorder field (from BackOrders) right within your LineItem portal. Enter the quantity in either 'shipped' or 'backordered'. It will still create the new record - no script required at all.

[comment]

I am afraid I still don't see the need for a 1:1. But if that is the preferred way, then yes - it can be done without a script by moving one of the line items fields (I admit I didn't understand which) into the BackOrders table.

[LaRetta]

If YOU didn't understand then I was not clear.

BackOrder would have a Quantity field also and so would LineItems. Typing into BackOrder quantity while in LineItems portal on Invoice layout will create the new record in BackOrder. Usually BackOrder contains additional fields which provide information such as asking Client what to do, i.e. release and bill immediately when items come in, contact client when items come in and if they then want to still purchase, etc. It depends upon the business requirements.

BackOrders are really notes of what might be in future. And no, they don't NEED to be in separate table but I've designed it both ways and lingering backorders on invoices from three years prior will haunt you.

[comment]

BackOrder would have a Quantity field also and so would LineItems.

Would they be the same initially? And if yes, at what point would you modify any one of them?

[BigDoom]

LaRetta this did the trick, thanks!

I made an field from the table backorders into the portal and setup the right relations and it works, but when I want to delete a backorder because it's closed (not needed anymore or some other reason) than it's field on my invoice becomes empty offcourse.

And I wanted to use another table so I could delete them to keep only backorders that are "open".

[LaRetta]

Would they be the same initially? And if yes, at what point would you modify any one of them?

BackOrder record only gets created if a quantity is entered into the BackOrder quantity field. In this way, it is not plaguing LineItems AR which is so date-entered and inventory/dollar dependent.

When User creates a new lineitem, system cannot yet display quantity available until record is committed. Only then can system check that product's inventory. So User enters desired quantity in LineItems quantity and trigger (or plug-in) splits the quantities appropriately (creating backorder record only if insufficient stock) and customer is asked their preference on the backorder.

BigDoom, I always kept backorders even if ‘closed’. Just flag them so they are done. Even, at the moment of lineitem creation, if customer said they didn't want to wait and to disregard the backorder quantity, I retain it and flag as "declined" in the BackOrder table. This provides an audit trail to the purchaser customer, displays on the invoice in a BO column (if BO is requested by customer) and, my favorite, it tells the Owner/Purchaser how they are ‘blowing it’ by not having sufficient quantity on hand. So don't delete, just change the Status on the BackOrder record.

Added part in blue

[BigDoom]

LaRetta, That makes sense to set them as closed instead of deleting them.

Thanks for your help!

[comment]

I am still confused by this.

trigger (or plug-in) splits the quantities appropriately (creating backorder record only if insufficient stock)

Don't you have a Shipments table? This looks like the opposite of one: you presume shipment unless there's a "non-shipment" record.

It's also not clear what happens when two orders are entered more or less at the same time; I have a feeling this relies on scripted update of the inventory?

system cannot yet display quantity available until record is committed.

Why not?

[LaRetta]

Comment said, "... you presume shipment unless there's a "non-shipment" record."

Essentially, yes, I guess you can say that. If it is on an Invoice, it is being charged (usually before an order is even packed). And if it is being charged, it is being (or has been) shipped. So of course the reverse would also be true. I always have shipping table as well but that tracks at Invoice level. The moment an item is added as a lineitem and quantity entered, that quantity has now been reserved.

Only 'not shipped and not paid' items go elsewhere. But these are business decisions and it has varied; same with inventory, it varies. If inventory is large, prior months are written to static tables and the 'balance in stock' is combination of static and current 'unposted' month. This keeps the speed up. Each User's commit updates the balance on hand for a product (no triggers).