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?
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 :)
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.
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.
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.
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.
In the end I followed the merge part of your link, and now its all working.
Thank you for your help.