1 2 Previous Next 17 Replies Latest reply on Oct 12, 2016 12:21 PM by ChrisJohnston



      I have a relationship that uses a couple of JOIN tables (pic). I have the need to do filtering based on certain criteria of many of the tables. The main filtering will take place in my SUBJECT:: table. I am looking at an idea where I need to filter through two JOIN’s. I am confused about the way that the criteria that needs to be filtered will pass through the JOIN tables. Should this be a calculation just mimicking what the actual value is? Or should it be written to a text field when the record is created? I like the idea of the calculation because it seems like things automatically kept correct. Any ideas or advice is welcome and appreciated (about the way to do what I ask or relationships). Thanks



        • 1. Re: Reach?

          I prefer Un-stored Calculations, whenever possible




          > Should this be a calculation just mimicking what the actual value is? Or should it be written to a text field when the record is created ?

          • 2. Re: Reach?
            Johan Hedman

            If you like to show relationships based on two tables you can always have a Virtual Table and set records by using Perform Script on Server.

            • 3. Re: Reach?

              A specific example of what you are trying to accomplish would be helpful.

              • 4. Re: Reach?

                My preference also just don't know when one or the other is best. Thanks

                • 5. Re: Reach?

                  I would like to know more about this Virtual table... is it only available on Sever? Thanks

                  • 6. Re: Reach?



                    I was asking you for a specific example of what you are trying to accomplish. I was looking for a bit more detail in order to see if I could make a helpful suggestion.

                    • 7. Re: Reach?

                      I like what you ask, it is a helpful question. I actually was trying to think  what would make sense for what is coming. What I was thinking now is what I need to filter in SUBJECT:: on the DATA:: table dates and types (HOWTO or CODE). I have had luck in the past making fields in the connecting table, and still using them in table on the other side of the join. All my data creation is done via scripts so I know I can set fields at that time. I know if I spot errors early,  It can be very helpful before more relationships and functionality exits. Thanks

                      • 8. Re: Reach?

                        That helps provide some more info, but really isn't a specific example.


                        You want to see records from Subject how? On a layout based on Subject? In a portal based on Subject? In a value list?


                        You want to use data in Data, HOWTO and TYPE in some way to control what records from subject are displayed. Can you provide an example with actual values in specific fields and explain how they need to affect the results produced?

                        • 9. Re: Reach?

                          Great, let me expand, because it was you who helped with suggestions that are the strong foundation of our information database to this day!:) This database is a collection of HowTo’s and Code. HowTo’s in the form of Video files, and Code (syntax, text) That we are constantly looking for. The main area that has me stuck is how things will be shared… for instance, we want to store a bunch of Code examples for MySQL but need to associate them with SQL info. We may create a record that is storing something tor JavaScript but later find we need the same SYNTAX for jQuery. I am having a hard time seeing CODE for one thing within its IMPLEMENT (the join table) for another. That Is why I set up the joins. However, I have not found a way to see things when they are associated with multiple SUBJECTS under the context of another that is sharing it. I can go to a record via its relationship and see its origin (who it belongs to) but cannot totally focus on a context of its own when associate it with another SUBJECT. I am also having a problem that you helped me understand, but this time it seems different. For instance, I set up a relationship to see the notes for the IMPLEMENT join, and  it seems to work but it keeps seeing only certain records first file. SUBJECT should be able to list the content of HOWTO and CODE but also associate them with  other SUBJECT records. I want to be able to shift the context based on my IMPLEMENT table to the notes that relates to the record with an IMPLEMENT join for another SUBJECT. Thanks so much

                          • 10. Re: Reach?

                            I think that you have several different issues and should try discussing just one at a time. For example, you want to link one subject to other subjects. I don't see anything in your data model that really supports that goal. You'd need a join table linking two occurrences of Subject for that.


                            And though you still have not answered my question directly, it sounds like you are trying to display data on a layout based on Subject. Is that correct?


                            From there, I can think of two things that match what you describe in terms of linking one subject to other subjects:

                            A portal could list the related subjects or a button could use the current Subject record to pull up a found set of subjects linked to it. Both require a relationship that I do not see in your current design.

                            • 11. Re: Reach?

                              I think my wording might of not been the best. I want SUBJECT to list (which I have and I am pleased with) DATA. DATA consists of different forms that help us. Per a SUBJECT this DATA, will be relative to another SUBJECT. This is only sometimes, but that is all that matters for functionality. This is where I am running into the problem. On a SUBJECT Layout that has a portal of DATA through IMPLEMENT I want to show another portal that is showing related SUBJECTS. I managed to work around this with two portals side by side to do one job. I could not find a way with one. But I am glad you mentioning the relationship. If you notice anything not recommended or a bad idea, that is what I want to know in the early stage. I was thinking that if I keep the SUBJECT related data for a piece of DATA I could always make the record show under the SUBJECT we are viewing it through. So I keep that DATA specific data in the IMPLEMENT table. But displaying it related to multiple SUBJECT records has not been easy. I also did not like the Idea of multiple portals stacked near each other to accomplish what we need to see in our layout. However, this way works for what is needed to be seen. I am glad you brought up that possibly the relations ships are not what is needed. If you see anything or want to advice for more efficient relationships, please do. I know discovering this early rather than late will be a good thing. Thank you so much.






                              • 12. Re: Reach?



                                Form a classic many to many relationship.


                                On a subject layout, you can set up either a portal to Implement with fields from data also placed in the portal row or you can set up a portal directly to Data. Both options have advantages for certain tasks though a portal to implement works best for building new links between subject and DATA in many cases.


                                But if you want to show related subjects, you'd need more TO's and relationships to the right of DATA in order to get the correct records to appear in that portal.


                                Subject----<Implement>-----Data---<Implement 2>-----Subject 2


                                This would then allow you to put a portal to either Implement 2 or Subject 2 on your layout.

                                • 13. Re: Reach?

                                  I got it! And it was what you said! That is… here. I need to read it every day before I try to work in FileMaker. I find context to be such an interesting thing. I had a year and a half layoff from my FileMaker work. I don’t think the other technologies I work in push me with context like FileMaker does. It is great habit to get back into. Now I have only one portal instead of tricks. At this point, I don’t have the filtering issues I first mentioned. Furthermore, the solution is much more responsive and snaps without notice to records. Thanks a lot.


                                  • 14. Re: Reach?

                                    I see what you mean, to the right makes total sense. However, when I set up this portal, I get the results I am looking for AND a lot of weird ones. I get 3 extra for the first record in the portal?? I am going to experiment and see if I can figure out why. Oohhh and I still need to refresh the window before it will change (when clicking on portal row). What I showed above uses a script (and ExecuteSQL) to set a global field, and it does not have any problems. I am going to investigate this in full to see why the weird results with the sensible relationship you suggest. Thanks

                                    1 2 Previous Next