Better you do a new layout for Reports and have a Sub Summary Part where you have your Supplier. Then you can list your Orders in the Body and if you have a Statistic field you show the running total for each Supplier
That's what i'm currently doing after importing it manually.
I want to know if there is a way that i can create this report using relationships or some other way that doesn't involve me exporting and importing it manually.
I would not work with importing. Better to store data inside FileMaker and just to a report based on that data.
If you have Orders and on each Order you have a field saying with Supplier it is, they you can do this in a regular FileMaker Report.
Create a new Layout. Base it on Orders table.
Create a new Sub Summary Part based on your Supplier field
Go to Browse Mode and sort on Supplier field
Then you have your report
I have 6 Order tables. All in different databases. How do i get all the data on one report?
Why do you have them in 6 different tables? I would try to migrate them all into one database.
With the Tool Base Elements you will get a step by step tool that will migrate your tables into one
If you have same fields on all 6 order, then why not just have one table where you store all data
Because it's 6 different Companies who share the same Supplier base.
That's why they are all in different Databases. They are all huge databases.
They share the same shareholders that's why they want a combined report.
If you had one solution you could just add one new Field on your Orders database where you stored what Company that Order belonged to. Then you have a very nice solution where you have all orders in the same place
In a perfect world that would solve all my answers. Unfortunately that wont happen.
These companies all work and where set up entirely different.
I need another solution to combining everything into one report.
1 of 1 people found this helpful
Then what I would do is to have a Data Warehouse solution of your solution. Meaning with this is for statstics purpose.
In each of your other 6 solutions, create a Script that create/update data from each solutions. The script should show all records in all your tables and the Loop through each table and create/update in your Data Warehouse solution. You can then set your FileMaker Server to run this script as often as you need your update and it will make sure you have a fully loaded system where you can do all sorts of reports.
If you heard about sync, a great tool for that is MirrorSync from 360Works that can do sync on the fly
360works mirror sync . It will handle all syncing within that product and you do not need to create any kind of scripts in FileMaker.
Are you strictly listing Suppliers and order count?
Supplier A - 100 orders
Supplier B - 325 orders
If so, you could script it with a virtual list and some variables passed between the 6 files.
maybe even use Execute SQL.
can you provide some more details?
Will you always run it from the same file?
Will multiple people need to run it?
Are you searching a date range?