1 2 Previous Next 19 Replies Latest reply on Dec 2, 2016 8:34 AM by andy_edinburgh

    Make a list of related works


      I have another challenge in my project to build a database of works of art in a collection. The attached 'sandbox' is a very simplified version of the real problem, but displays the difficulty I have.


      What I want to do is relate each WORK [of art] to one or more TOPICS. Each TOPIC would normally relate to several WORKs, so there is a WORK TOPIC LINK table. So far so good. I can use a portal to display the TOPICs a WORK is related to and vice versa.


      What I now want to do is, for a given WORK, make a list of the topics it is related to, and for each TOPIC a list of the other WORKs related to it. This is the bit that does not work. The cream on the cake would be also to skip in the lists the WORK in the main layout as we do not need to know that it is related to itself.


      I therefore have nested portals WORK -> TOPIC -> (related) WORKS. The problem is that the inner portal of related WORKs only lists the WORK I started with in the outer layout - sometimes repeated (if it appears in one or more topic). I already tried making a separate occurrence of WORK called RELATED WORKS for the inner list, but that made no difference.


      Any ideas anyone? This cannot be such an extreme requirement can it? 


      Kind regards,  



        • 1. Re: Make a list of related works
          David Moyer


          this isn't exactly what you asked for, and doesn't omit the current work, but it's very easy.

          the new field is TOPIC::ListOfWorks = Substitute ( List ( WORK::WorkName; "¶"; "; " ) )

          • 2. Re: Make a list of related works
            David Moyer

            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"

            • 3. Re: Make a list of related works
              David Moyer

              1 of 1 people found this helpful
              • 4. Re: Make a list of related works

                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.

                1 of 1 people found this helpful
                • 5. Re: Make a list of related works


                  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? 




                  • 6. Re: Make a list of related works

                    Phil (?),

                    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?

                    • 7. Re: Make a list of related works
                      David Moyer

                      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).

                      • 8. Re: Make a list of related works

                        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.

                        • 9. Re: Make a list of related works



                          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? 


                          Learning fast!



                          • 10. Re: Make a list of related works
                            David Moyer

                            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)

                            • 11. Re: Make a list of related works

                              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.




                              • 12. Re: Make a list of related works
                                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.

                                • 13. Re: Make a list of related works
                                  David Moyer

                                  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).

                                  • 14. Re: Make a list of related works



                                    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).




                                    1 2 Previous Next