Are orders ever split between two or more invoices?
Is there a line items table listing items ordered?
It sounds like you need a join table so that you can set up a many to many relationship between invoices and orders. That way one order can be listed on several invoices and one invoice can list several orders--then comes the challenge of selecting the specific line item records from the list of orders to include in a specific invoice...
Yes, I can have both ways: several orders for one invoice and several invoices for one order.
And I use 2 portal rows in both (one for delivery places, second for goods description).
As I see it - I need extra child table for invoices and possibility to select order numbers there using some script.
This child table should have search possibility in the header and portal row with available orders for named customer to choose in the body (thanks god I don't have orders for several customers).
I see the way it should be, but i need one of your eurica punches to start. For example, how should I set relations between my ORDERS, INVOICES and INVOICE.child tables?
It's not really a "child" table, it's a "join" table as it will serve as a link between orders and invoices:
Orders::OrderID = Order_Invoice::OrderID
Invoices::InvoiceID = Order_Invoice::InvoiceID
You can place a portal to order_invoice on your orders layout and select Invoices to link them to a given order. You may want a script with a button that create new Invoice records and automatically connects them by creating the linking Order_Invoice record for you to make it appear in the portal. A button in this portal can use go to related records to switch to the linked Invoice record.
The interesting challenge here is that orders usually have a related line item table. If one order is split between two invoices, then I would think that you would next need to select line items from that order to designate them for one invoice or the other. In some cases, you might even need to split such an item between the two orders. (Send 20 cases of motor oil with invoice 235 and 30 cases of motor oil with invoice 456...)
Join table, exactly!
I already have this "make invoice" button. As i explained most of the time i have 1 invoice vs. 1 order. So, I have OrderID and InvoiceID fields in both Orders and Invoices table. And my script set the values crossed to both tables. So, for the simple cases it works perfectly.
And now I going to make an ADD button on my Invoices layout that can add link (connection) to another Order if it is needed. ADD button will call pop-up window with list of orders related to this customer and I can choose one or multiple orders to "attach".
Wow, seems to be it is all done. Thank you so much, Phil, for helping! It appeared to be easier then I expected.
My I ask you one more question. I made it all right and now my "join" table looks right (you can see my attached screenshot), but i need a field in my invoice table that will show all my orders connected to this invoice divided with comma, for example: "order1, order2, order3". I thought that this will work, ex:
Substitute ( List ( Order_Invoice::order.number; ) ; ¶ ; ", " )
but it doesn't ;(
on the screenshot below I need to repeat RE.ab.number, ex.:
Sorry, i'm stupid. It is working indeed. Used bad link for the field. It is sooo good now ;)
But, what should I use to count referenced line? I need to check IF there are more then 1 line - then it will use LIST command, if not - just show single order number?
I used ( Case( RE.linked.AB::RE.linked.re; Get( RecordNumber)) ) > 1 and it seems to be working. Thank you very much, Phil
I don't see why you need that.
List with one related record will return just that one item. List with multiple related records will list them all and your substitute function then replaces the returns with commans to produce your horizontal list.