This is not possible with the current version of FileMaker.
It is possible to write a script that uses other methods that takes the records matching specific criteria and create a new table, but such dynamic table creation--except as the first step in something that the developer then takes in hand and finishes up manually, is not a method that works well in FileMaker. Instead, you can normally manage different groups of records within the same table without creating multiple tables "on the fly" to do so.
And I will note that I have a script that has been used for quite a few years in one of my systems that summarizes data from table into another but which does not use SQL to do so.
Thanks, it doesn't have to be SQL.
Cause I want to get the query result from 2 tables, table 1.
Table 1 has columns : material No, product line, coutry, vendor.
And table 2 with Material No, cost, Qty, Purchasing document.
And I want get table 3 with columns Material, vendor, Product line, sum(Qty), sum(cost)
Can your script do this? Would you like to share me your script?
This can be done without any script.
I am assuming that you have this relationship in place:
Table 1----<Table 2
Table 1::Material No = Table 2::Material No
Define two fields, sTotalCost and sTotalQty in Table 2 of type summary. Specify the Total of option for each and select the Cost and Qty fields respectively as the fields to total.
Create a list view layout based on Table 2. Add a Sub Summary part "when sorted by Material No".
Put the Material, Vendor, Product LIne and the two summary fields inside this sub summary layout part. The relationship will make it possible to add fields from both table 1 and table 2.
Click on the body part label and press delete to delete this part.
Perform a find to find the records you want in your report. (Use Show All records if you want to base the report on all Table 2 records).
Sort the records by Material No. (The layout will be blank if the records are not sorted by the "Sorted by" field specifed for the sub summary part.
The find can be performed in a script or you can use a different layout based on Table 2 to perform the find manually before changing layouts to this report layout.
I think you are right, to get table 3 from table 1 and table 2. But I can't remove the duplicate lines in the table.
Back to the first question, if I have only 1 table with columns: Material, vendor,PO number,country, cost and Qty
and I want get below summary table,
I recommend that you use LIST view, not TABLE view.
With that approach, you can then delete the body layout part from this layout to eliminate the individual records and just get the sub totals.
OK, I will try. Thank you!