I suggest looking up "summary reports" in the filemaker help system
If you were to print out the report in this format:
list the wanted items
get price from shops
search for car
see car seller
put a bit
It's easier to set up in filemaker.
Create a summary report based on your subquery table.
Create a sub-summary part (Sorted by subquery::queryid, Print Above)
Place the fields you want from Query in your sub summary part.
Find the records you want and sort the result by subquery::queryid.
Report will be immediately visible in FMP 10. In earlier versions, enter preview or print to see this report.
Once you get that working, if you really want the format you specified, let me know and I can show you a way to get it.
Thanks a lot PhilModJunk for reply.
It worked with sub summary.
Is there any way I can expract to excel or csv file in that format?
Quoting from the filemaker help system:
"If your FileMaker Pro contains , you can group by a sorted field in order to export , such as subtotals generated by a report with grouped data. This process exports one record for each group. For example, if you have a report that totals sales by region, you can export one summary value for each region."
Select Export Records, and use the group by pane in the dialog to specify the same field(s) by which you group your summary report.
Is there any way I can list the quries does not have any sub-query too in the same preview page?
not easily. There has to be at least one record present. You could create a single record that is blank except for the fields needed to identify it as a member of a specific sub-summary group--but this may cause other problems for you.
Thanks a lot PhilModJunk,
The report they asked me to create for them I have to create that blank sub-query.
Perhaps this general outline of a script will work:
Create one blank record for each type of query that does not have any in the current found set. (You may have to perform a find for each type and use Get(foundcount) or use a relationship to figure out which query types don't have any records for the specified date range or other criteria you use to set up the complete report.)
Mark a field with a unique value such as "Dummy" or "Place Holder" so that you can easily find these blank records.
After the report is viewed/printed/PDF'd, Find all records marked in this fashion and delete them.
The alternative is to base your report on your Query table and use a portal to display your subqueries. With slide up selected on the portal, you can set the portal to display a very large number of rows, but slide up to eliminate unused space. The drawback here is that if you ever get more subqueries than you have rows in your portal, you won't see all of them in the report.