It depends on your relationships and the type of report that you need to create. I suggest describing that report and those relationships.
Thank you, Phil. I have four tables: Properties, Annotations, Payments and Letters, The last three relate to Properties through a common field (ie, propertyID). I want to have asingle report that shows all happenings for a particular property, I tried subsummary when ordered by PropertyID, but it only shows one record from the other tables, not all records. Not sure if I am explaining myself right. Here's a screen shot of the way I want the report and the relationship graph I have:
1 of 1 people found this helpful
Yomango, for these kinds of things, I tend to "gather" the data into variable(s) then push to a merge with tab stops to simulate "columns".
I don't see from your example what kind of "sort order" your data might take, so am not entirely sure how to go from here. Although, you may need to verify if you have it "grouped" - as all the annotations are together, followed by the other related data.
Possibly an ExecuteSQL() for each relationship to be concatenated would give you what you need with the least amount of 'scripting'. But it may also be possible to List( relatedTable::fieldNeeded ) to "gather" for each related table and push to variable(s).
Perhaps you could think through the process as how you would manually get the information and type it out:
1. type the main information
2. lookup the related annotations and type them onto the report
3. repeat for the rest of the related tables/records
It's that process that is then turned into FileMaker scripts using the available steps and functions.
If you need to outline the process with the correct field names (with tables - TOs), we may help you further on various ways to get the information into the report. So many ways to go from here!
Beverly is making a good suggestion.
Another approach--that is not as flexible, but also is simpler to set up, is to consider using portals to list the details from each related record. There are limitations to this approach, but if you make the portals many rows tall and set them to slide up to reduce the total number of portal rows to just those used, it can be a workable option in some cases.
Hello , Beverly. Thank you for the insight. I think I got what I wanted using some of your advise: Global variables. I wouldn't know how to use the ExecuteSQL, I barely can pronounce SQL. BUt the good thing is that I got the report. Muchas gracias.
Hello, Phil. Yes, I thought about reporting through portals but the limitation is the amount of rows that can be shown. Some of the reports have hundred of records. But as I said to Beverly, I got it. I appreciate your time.
You may have missed a part of my last post. In a printed/previewed or PDF'd report, you can set up a portal to show only the number of rows actually needed to show all related records. So if that is the only reason not to use a portal, you might want to reconsider.
I hadn't thought about that, nor I grasped the entire meaning of your post. Thank you, I will keep it on mind.
I barely can pronounce SQL.
After several decades the jury is still out whether it's 'Sequel' or 'As-Queue-Al'. So don't feel bad about it.
btw, another option would be to combine these three tables into one, incl. a superset of the existing fields, and flag them by type (annotation, payment, letter).
You can still split them up when and if needed (via relationship, portal filter, Find), which usually is an easier task then bringing them together. Your new super-table wouldn't be totally by the book, but it'd work – especially producing a report would be a cinch because you can simply show all (or any subset of the) related records in a List view. (It's also easier to find stuff that now may be in either or several of the tables.)
If “not by the book” has any serious disadvantages is something that we cannot say without more information, so there's a pincher of salt in this suggestion.
When I have these relationships:
I sometimes base a summary report on either child1 or child2, put fields from parent in header, subsummary or grand summary parts and use a portal for the other child records. I select either child or child 2 to minimize the use of portals.
LOL. I say (spanish sound) "ES QUIU EL". But I guess "sequel" is easier. Thank you for the advise, I solve the issue by scripting the field I needed from the three related tables in a global variable using the List function, then merged it to the layout report. it's working. I still have to figure out how to line up data with the headers. Gracias por su tiempo
Phil, your kind advise is always welcome. Gracias
I still have to figure out how to line up data with the headers. Gracias por su tiempo
If you separate your items of data with tab characters (Char ( 9 ) ), You can set tab stops in the paragraph formatting found on the inspector's appearance tab for both your header text and your listed text to get columns that align.
I sometimes do that with data returned by ExecuteSQL() in order to get workable columns of data. (Think of ExecuteSQL as a more powerful form of the List () function.)
Thank you, much