1 2 Previous Next 23 Replies Latest reply on Sep 25, 2016 12:01 AM by andy_edinburgh

    Moving between instances of the same table

    andy_edinburgh

      I am a relative newcomer to FileMaker, using FileMaker 15 Pro Advanced. I am trying to do something that sounds as if it should be easy but isn’t. Due to the complex structure of my database, I unfortunately have to have several occurrences of the same table, OBJECT, called OBJECT 1, OBJECT 2, OBJECT 3, etc. I have several layouts, LAYOUT 1, LAYOUT 2, LAYOUT 3, ... each attached to the corresponding occurrence. In some cases, I have more than one layout on the same occurrence, so LAYOUT 1a and LAYOUT 1b are both attached to OBJECT 1.

       

      Now, when the user moves between layouts on the same occurrence, e.g. LAYOUT 1a to LAYOUT 1b, FileMaker preserves the OBJECT 1 ‘state’ comprising the last find, sort order, and selected record for OBJECT 1. If she moves from LAYOUT 1 to LAYOUT 2, however, FileMaker leaves her OBJECT 1 state behind and gives her the OBJECT 2 state, i.e. the last find, sort order and selection she did last time she was there; initially, the full set and the first record.

       

      What I want to achieve is that the user moves between all these layouts as if they were attached to the same occurrence. Correct me if I am wrong or overcomplicating it, but it would appear that I need the script triggered by the button to move from LAYOUT 1 to LAYOUT 2 to capture the state of OBJECT 1 and apply it to OBJECT 2? It seems that it needs to make a note of which records are in the current OBJECT 1 found set, what the sort order is and which one is currently selected; then go to LAYOUT 2 based on OBJECT 2 and construct a new found set, sorted as in OBJECT 1; and finally select the record that was selected in OBJECT 1.

       

      1. Is there a simpler solution? You may assume that I am not able to base the layouts on the same table occurrence – I assure you I would if I could!
      2. Can you tell me how to write such a script? It took me ages to master the simple act of selecting the same record; I don’t know where to start with capturing the found set and sort order! I guess I need a loop that records a list of the record ids in the order they appear in the ‘from’ occurrence, OBJECT 1, then perform find on each of them in the ‘to’ occurrence, OBJECT 2? Note that OBJECT has a unique numeric primary key called #Object_id.

       

      Is this a common problem? Should we get FileMaker to add some in-built functions? Or to change the nature of relationships so that multiple occurrences are not necessary? I find them very annoying! They seem to arise out of FileMaker’s abandonment of the one-to-many relationship, treating all as many-to-many and hence creating apparent ‘loops’ in relationship graphs where none is really present. Is that necessary?

       

      Cheers,

      Andy

        • 1. Re: Moving between instances of the same table
          hankshrier

          What it sounds like you need is not several instances of the same table, but different relationships between the table and the various layouts.

           

          Tell us exactly what you are trying to do and why. We may be able to help.

          • 3. Re: Moving between instances of the same table
            andy_edinburgh

            No. Go to Related Record is a great feature, which I use when I can, but if you try to go to a 'related' record in a different occurrence it gives you an error message saying it is not related. Occurrences are unfortunately not 'related'. It would be a great solution if they were! Maybe FileMaker could consider that?

            • 4. Re: Moving between instances of the same table
              andy_edinburgh

              OK. Hankschrier, since you ask…

              My app is to catalogue a collection of works of art. The works (paintings, drawings, sculptures etc.) are in the table WORK in the database.

              The catalogue records citations of the works, reproductions of the works, exhibitions (and therefore catalogues) in which they have appeared and groups them by ‘topic’, with bibliographic references to the topic.

               

              So I have a table BIBLIOGRAPHY (which is imported from Zotero), a table EXHIBITION, and a table TOPIC. Then I have ‘link' tables: CITATION links BIBLIOGRAPHY and WORK; REPRODUCTION also links BIBLIOGRAPHY and WORK (but not with the same records); EXHIBITED WORK links WORK and EXHIBITION, and EXHIBITION CATALOGUE links EXHIBITION and BIBLIOGRAPHY; WORK TOPIC links WORK and TOPIC and TOPIC REFERENCE links TOPIC and BIBLIOGRAPHY.

              Graph.jpg

              FileMaker sees these as multiple loops from WORK to BIBLIOGRAPHY so I have to have multiple occurrences of WORK or of BIBLIOGRAPHY or both. I can’t find a way to avoid this, but am listening if you can!

              • 5. Re: Moving between instances of the same table
                Markus Schneider

                without knowing more about Your FM solution, it's hard to tell - but at a first glance, it could also be the db-model behind..

                • 6. Re: Moving between instances of the same table
                  andy_edinburgh

                  Thanks for answering, Markus Schneider. I just posted a graph ... take a look and advise me if I can design it differently.

                  • 7. Re: Moving between instances of the same table
                    padje

                    gtrr is indeed not a solution for your problem.  To early here in Belgium.

                     

                    What I do in these cases is a "navigate script"

                     

                    Set variable  :  id_record

                    nav to new layout

                    perform find :  id = id_record

                     

                    Maybe not the most elegant solution but it works for me.

                    • 8. Re: Moving between instances of the same table
                      andy_edinburgh

                      Dankjewel, Padje.

                      Yes, your solution is in essence what I am doing at the moment...

                       

                      Copy [ <from-occurrence>::Work id ]

                      Go to Layout [ <to-layout> ( <to-occurrence> ) ]

                      Enter Find Mode [ Pause : Off ]

                      Paste [ No style; <to-occurrence>::Work id ]

                      Perform Find [ ]

                      Enter Browse Mode [ Pause :Off ]

                       

                      ... works OK. But of course the new found set is just the one item last selected. This does not preserve the full set of records previously selected so is not the most user-friendly solution. If I use the same solution to switch back to the first layout, then the original find is lost altogether.

                       

                      I tried copying all records, but it just concatenates all the values into one field, so they do not work as a search term in the to-layout.

                      • 9. Re: Moving between instances of the same table
                        Markus Schneider

                        oops - did not see the answer above when writing my answer, was created at about the same time )-:

                        • 10. Re: Moving between instances of the same table
                          andy_edinburgh

                          No probs. Any new ideas?

                          • 11. Re: Moving between instances of the same table
                            padje

                            Can't you modify your script ?

                             

                            Make sure you set all record id's in one or multiple variables

                             

                            Open the new layout in a new window (this way you don't loose your original found set)

                             

                            Make sure before going to the new window to add a script step go to field [ ]

                             

                            In this way filemaker has no focus on a record(field)  Else you could get a message "can not modify this record, because it is modified in another window" (or something similar)

                             

                            Perform find

                            Extend found set as necessary

                             

                            Just an idea from a filemaker padawan

                            • 12. Re: Moving between instances of the same table
                              andy_edinburgh

                              Of course I can modify my script, and you raise some good points...

                              • "Make sure you set all record id's in one or multiple variables" - Yes, but how??
                              • "Open the new layout in a new window (this way you don't loose your original found set)" - I don't lose it anyway as long as I navigate back by just selecting the layout.
                              • "Make sure before going to the new window to add a script step go to field [ ] - In this way filemaker has no focus on a record(field)  Else you could get a message 'can not modify this record, because it is modified in another window' (or something similar)" - I did not have that problem but note the handy trick.
                              • "Perform find Extend found set as necessary" - Yes, but how??

                               

                              Great! Many thanks. I think this is the general idea I am looking for but I am not familiar enough with scripts to know exactly how to collect the set of records in the <from-occurrence> and make it into the found set for the <to-occurrence> - your first and last points. Could you give me an example?

                               

                              Cheers, Andy 

                              • 13. Re: Moving between instances of the same table
                                BruceRobertson

                                andy_edinburgh wrote:

                                 

                                No. Go to Related Record is a great feature, which I use when I can, but if you try to go to a 'related' record in a different occurrence it gives you an error message saying it is not related. Occurrences are unfortunately not 'related'. It would be a great solution if they were! Maybe FileMaker could consider that?

                                Not true.

                                But the technique is a little subtle.

                                Use GTRR in a valid TO for the current context.

                                HOWEVER when selecting target layout, choose a layout based on the TO you wish to arrive at.

                                You van still choose same window or new window.

                                1 of 1 people found this helpful
                                • 14. Re: Moving between instances of the same table
                                  dtcgnet

                                  andy_edinburgh wrote:

                                   

                                  Dankjewel, Padje.

                                  Yes, your solution is in essence what I am doing at the moment...

                                   

                                  Copy [ <from-occurrence>::Work id ]

                                  Go to Layout [ <to-layout> ( <to-occurrence> ) ]

                                  Enter Find Mode [ Pause : Off ]

                                  Paste [ No style; <to-occurrence>::Work id ]

                                  Perform Find [ ]

                                  Enter Browse Mode [ Pause :Off ]

                                   

                                  ... works OK. But of course the new found set is just the one item last selected. This does not preserve the full set of records previously selected so is not the most user-friendly solution. If I use the same solution to switch back to the first layout, then the original find is lost altogether.

                                   

                                  I tried copying all records, but it just concatenates all the values into one field, so they do not work as a search term in the to-layout.

                                  The use of Copy and Paste is generally frowned upon. When you "Copy" without the user knowing you've done that, then you replace whatever the user had on his clipboard, and users don't like wondering what in the heck just happened to their clipboard.

                                   

                                  Instead of Copy, Use:

                                  Set Variable [ $WorkID ; Value: <from-occurrence>::Work id ]

                                  go to layout is fine

                                  enter find mode is fine

                                  Set Field [ <to-occurrence>::Work id ; Value: $WorkID ]

                                  Perform find is fine

                                  No need to enter Browse Mode, since you automatically enter browse mode after the find.

                                   

                                  Set Variable/Set Field instead of Copy/Paste.

                                  1 2 Previous Next