here's another way using a value list as a "portal within a portal":
The field above is now a global text field (TOPIC::g_Text) based on the new value list. The checked boxes populate the global field with a list. The value list is just work name based on "Include only related values starting from TOPIC"
1 of 1 people found this helpful
You could use a summary report for this based on the work topic link table.
Use Go TO Related Records (GTRR) to get a found set of Topics on a Topics based layout.
Use GTRR again with the "found set" option from there to get a list of all links records--which will also be a list of all works for those topics.
If, at the start, you saved the ID of the Word record from which you started, you can now use it to constrain this found set to omit that work.
Sort your records by topics and set up sub summary parts "when sorted by topic" to form sub headings and you'll have the desired report.
This is very helpful thanks. I am however not advanced enough in FileMaker to immediately understand how you solved it and therefore be able to transfer it to the full scale system.
1. Why does 'list' work when a portal does not?
2. You seem to have given two alternative solutions. Would you prefer one to the other?
3. I assume that text functions could be used to remove the original work from the list?
This sounds like a very creative approach but I need a little more detail.
1. Perhaps I should have mentioned that this 'List of related works' is just one section of several in one report (Called 'Catalogue') based on the WORK table. I cannot change that unless I make a separate report for the 'List of related works' can I?
2. When you say 'Use', I presume you mean write a script with these functions in?
3. When you say save the original WORK id at the start, where would I save it such that it is still accessible at the end? Do I need global variable?
Indeed - I provided a confusing solution with too many parts. To get to the portal shown in answer #3 you only need the calculated field ListOfWorkIDs and the relationship WORKbyListOfIDs. The portal is based on this relationship. You don't need a value list or global fields.
FileMaker has a dubiously nifty feature that allows you to relate a list of values (instead of one value) to many values in a child table (not a many-to-many relationship; but combined one-to-many relationships).
1) Probably Yes. Sounds like David's method will be a better option for you, but there might be ways to combine--such as generating a PDF where each section is appended to the previous section of the report.
2) yes in a script
3) Since this is in a single script, you just need a script variable. No need to make it a global variable.
I have now had a chance to play around with your (two) solutions in the real database...
a) The solution that calculates a list of related work names into one field belonging to TOPIC works well and already takes me close to where eI want to be. I can actually include several field fro WORK or TOPIC WORK LINK by making a compound field first; the only limitation is that I cannot easily arrange the results to look like a table.
However, I have not yet found a way of omitting the work from which we linked to the TOPIC in the first place (the outer scope in the report).
b) The solution where you use the list of WORK Ids to generate a subset of the WORK table (using a separate occurrence) is a nice idea, but does not seem to work with the portals, either in your solution or mine. In your example you put the generated list outside the portal and it seems to show all the WORKS for all the topics, which is not what I want. I want a list for each TOPIC. I tried putting this portal inside the TOPIC portal, but that did not work either. Weirdly, it still shows all the WORKs for all the topics against the first topic, and is empty for all the other TOPICS. I do not understand why that would be the case, because the field is separate per TOPIC so it should generate a separate list of WORKs? I am guessing it must be something to do with the way FileMaker regards records as 'Related' through WORK TOPIC LINK? Can this be narrowed?
I have to run out, but for now, I can say that the portals can be filtered to prevent showing the current work. It looks something like not(workID = TO::workID)
The question is how the filter knows which is the WORK in the outer nest of portals?
Anyway, I still have not got the portal itself to work - see previous reply.
To summarise this problem:
1. I have a layout based on WORK
2. On it, I have portal based on TOPIC. This correctly show a row for each Topic to which the Work is related.
3. If I add the field ListOfWorkID that is also show with the correct values.
4. However if I add a nested portal based on WORK for ListOfWorkID, it shows all the works for all the topics against the first topic and then nothing against the others. In other words, it shows the same as it does if placed directly on the WORK-based layout (where this behaviour makes sense). It seems that somehow the link to WORK for ListOfWOrkID makes it forget the TOPIC context??
This is still a puzzle.
However if I add a nested portal based on...
Nested portals are not Possible in Filemaker. Not sure what you have there, but it's not a nested portal. You can't create a portal object that is inside another portal object. If you attempt to do so, the portal sits on top of the other portal object and functions from the context of the layout, not the portal you tried to put it inside of.
to be clear, I referenced the concept of a "portal within a portal" using a list as a part of the portal row. (see #2 and #3 above).
Well that would explain (a) why it didn't work and (b) why my attempts to put a portal within a portal gave the same results as putting the 'inner' portal on the outside.
However, it is not entirely true (in Filemaker 15 Pro Advanced) that you can never have a portal within a portal. It works perfectly well for an ordinary chained sequence of tables. For example I have a table EXHiBITIONS linked to WORK by link table EXHIBITION-WORK, and a table LOCATION linked to EXHIBITION by EXHIBITION_LOCATION. So each work can be in more than none exhibition and vice versa ans an exhibition can take place at more than one location.
The report is based on WORK. I have a portal to EXHIBITION, listing the exhibitions that have shown the work, then for each exhibition I have another portal listing the LOCATIONs at which the EXHIBITION took place.
No problem there, so why in the current example? I see nothing wrong conceptually with the idea of nested portals.
So it seems the only solution is to gather all the data I need into one field using List combined with calculated fields. (The link from the list to a second occurrence of the WORK table is also no use if the resulting portal does not behave).