10 Replies Latest reply on Feb 1, 2012 12:29 PM by philmodjunk

    Cross-related records



      Cross-related records


      Hello there,

         Could you please try to help me in situation with related records. I have system with to main tables: ORDERS and INVOICES. Each has it's own serial number ORDERS::ID.order and INVOICES::ID.invoice and I set the INVOICES::ID.order from ORDERS table to make them related.

      In easy deals (daily business) - this is enough, but sometimes I have more then 1 related order for invoice (for instance, when I load a truck with goods from two or more orders). Then I added ORDERS::ID.invoice and made a script to set proper fields. It was OK too.

      But I faced the situation when I have 2-3 different invoices shipped under different orders at the same time. In this case my cross-relations can't help, because ORDERS::ID.invoice is overlapped all the time. Seems to be I need to run one more child table for ORDERS to link all invoices it has reference to. My mind is blown and I don't see easy solution (any solution to say truth). Maybe you have? Please help

        • 1. Re: Cross-related records

          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...

          • 2. Re: Cross-related records

            Dear Phil,

            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?

            • 3. Re: Cross-related records

              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...)

              • 4. Re: Cross-related records

                Dear Phil,

                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". 

                • 5. Re: Cross-related records

                  Here's a demo file on many to many relationships you can examine for ideas: http://www.4shared.com/file/dZ0bjclw/ManyToManywDemoWExtras.html

                  • 6. Re: Cross-related records

                    Wow, seems to be it is all done. Thank you so much, Phil, for helping! It appeared to be easier then I expected.

                    • 7. Re: Cross-related records

                      Dear Phil,

                      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.:

                      12-004-005, 12-004-011

                      Thank you

                      • 8. Re: Cross-related records

                        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?

                        • 9. Re: Cross-related records

                          I used ( Case( RE.linked.AB::RE.linked.re; Get( RecordNumber)) ) > 1 and it seems to be working. Thank you very much, Phil

                          • 10. Re: Cross-related records

                            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.