6 Replies Latest reply on Jul 11, 2015 1:56 AM by MariusPirv

    Print records from 2 tables

    MariusPirv

      Title

      Print records from 2 tables

      Post

      Hello Guys,

      I have a small problem with printing records from 2 separate tables.

      Table Invoices and Entries both have a Date field, I can't get around on how to print the records sorted by this date together for each product. The tables are not joined, they have in common the Products table as F key.

      Please help me in finding a solution.

      Thank you,

      Marius Pirv

        • 1. Re: Print records from 2 tables
          philmodjunk

          What exactly does this mean?

          The tables are not joined, they have in common the Products table as F key.

          And how would you want this report to appear on the screen or the printed page?

          • 2. Re: Print records from 2 tables
            MariusPirv

            Ok, I'll try to explain better.

            So I have these tables Product, Invoices Data, Order Data (Entries from manufacturer).

            I need for any product to be able to generate a Balance sheet, which means, having the Initial stock and all the invoices and Entries that concern that product. Thing is that I need to have them in order by Date so the stock changes make sense to the viewer.

            Example: Product Oreo

            Initial stock 5;

            From supplier 10 stock 15

            Sell to customer 5 stock 10

            From supplier 5 stock 15

            but Selling is done in Invoices Data and Entries in Order Data.

            How can I print them together ordered by Date :(

             

            Thank you for your help so far :)

            • 3. Re: Print records from 2 tables
              philmodjunk

              Sounds like your order and invoice line items should be records in the same table.

              See this thread for a description of how to take a "Line Items" table, use it for both sales and purchase orders in order to produce a "transactions ledger" that shows both additions to, subtractions from your inventory level as well as computing a "balance" to show you what is currently available for sale.

              Managing Inventory using a Transactions Ledger

              • 4. Re: Print records from 2 tables
                MariusPirv

                I understand what your saying and I agree. Sadly the program was not designed for this from the beginning and now it would take too long to change it, plus it would be inoperable in the process. Is there any way I can do just this print with both table records sorted by date value ?

                I'm using the default Invoice you mention in the post, but I added a lot of stuff.

                 

                Thank you,

                Marius

                • 5. Re: Print records from 2 tables
                  philmodjunk

                  I do not see a way. At best you would need to copy the data for the desired product and interval of time from the two tables into a new temporary use report table. This would be cumbersome, slow and you would need to be very careful how you managed the data in this table.

                  But:

                  it would be inoperable in the process.

                  This is not the case. Adding a new table and importing this data into it from the existing two tables could be managed without shutting the database down for an extended period of time. The safest approach would be to take a copy of your file to both make the needed design changes and to test it to make sure that your design changes work. Once that's done, you can either take the database down, replace it with the new copy and import the data via a script into your new copy or you can carefully replicate your design in the current file during a series of such fairly short down times (close the file on server, open with fileMaker Advanced, make your changes and then quit FileMaker and re-open file on the server.) You can keep the new design inaccessible from the user until all data has been transferred to the new table and your scripts/layouts are all in place to use it.

                  • 6. Re: Print records from 2 tables
                    MariusPirv

                    In the end I followed the merge part of your link, and now its all working.

                    Thank you for your help.

                     

                    Marius