1 2 Previous Next 16 Replies Latest reply on Dec 13, 2013 4:23 AM by Mike_Mitchell

    Many to Many Relationship and auto-updated field


      Im not fully sure how to ask, but i can give an example of what im looking for:


      I have a many to many relationship with a join table. In that join table i have the the two ID fields links to their Key field in the other parent tables and all works well. What i want to do is have a field in the join table that will auto update to whats in a field in one of the parent tables. This field is the name of that 'group' and over time it may change. Due to this, we need that field in the join table to be updated to the parent table newly updated field so the updated data will show in the portal. I hope that is clear to what im trying to do.

      It would be amazing if we could use the relationships to have multiple table data in a portal (the join and the other parent table). I know i can write a script that once the data is updated in the parent table it then does a search and loop to update all the join tables. Im not sure if that is a better process vs anything else that may be available.


      thanks for any help,

        • 1. Re: Many to Many Relationship and auto-updated field

          Rob -


          What you want to do is possible. However, I would first ask if it's necessary. There are only a few circumstances when echoing data from a parent table to a join table is required. You can usually get by with the field from the parent table.


          What's your goal for echoing the data? Maybe we can achieve it another way.



          • 2. Re: Many to Many Relationship and auto-updated field

            It is - thats why im doing it.

            Users will join 'groups'. The groups have names. The names will show in the portals of the users as to what groups they are members of. But over time there may be a need to change the name of the group and would need the portal to show that change in name. (history has proved these groups mature and then change in name to show the changes in the groups and the focus).


            I hope that helps explain it a little better. thanks for the help,

            • 3. Re: Many to Many Relationship and auto-updated field

              You can put the name of the group from the parent table in a portal that points to the join table. The field doesn't have to be in the join table to do that.

              • 4. Re: Many to Many Relationship and auto-updated field

                Unless it is recorded in another place, overwriting that field in the join table will erase the "name history" of a group. If – as Mike suggested – you simply display the name, then you have the same problem, and you don't even need an update script …


                Think of invoice line items and product prices, which are snapshots; a price change shouldn't modify historical data, that's why you set the line item price as auto-enter and don't update it when you change the price. (And unless you have a price table, the line items table is the only place to track the development of your prices.)


                An alternative – if you're interested in preserving "historical" names – is to have a group name table and set one of those names (or the only one) as default name for … well, whatever your entity is; auto-enter the name table primary key into your join table and display/access the name via this key. When you change the name, new join table records will auto-enter the new key, but old one won't change, and each record will have the correct "group name" as of the moment it was created.

                • 5. Re: Many to Many Relationship and auto-updated field

                  Thank you for your reply.

                  When i do this (just add a merge field) I get '<Index Missing>' in its place. Same if i just add a normal field too.


                  What could i be missing? Its a pretty basic set up im testing and have not been able to make this work as you have explained. That would be exactly what im looking for if it did work that way.

                  • 7. Re: Many to Many Relationship and auto-updated field

                    Yes, if you want to record an actual "history" of what the group names were over time, then you'll need a separate table to do that. But I didn't get the sense that was what you were trying to do. Is it?

                    • 8. Re: Many to Many Relationship and auto-updated field

                      Personally i dont feel the data needs to be in the join table. But i cant see a way to add any other data from other tables to the portal without it in the join table. (that is what i first tried and did again after mike suggested it was to use a merge field linking back to the Parent table with the changing data on it). Maybe i am missing something - it sounds like i am. What could i be doing with my relationships that would not allow the parent data to show in the portal of the other parent table?


                      Im looking for the tech solution. The user culture is what it is. The name of the group changes over time as needed but much of the staff stay the same. A year or more may pass and the 'group name' field in parent may change. It will not be static and will be required in the portal for the users to see. The organization and staff involved have a process and the tech needs to follow. This is not a invoicing solution. It is a staff management solutions.


                      That is a good question on keeping a record of historical names and having a default name. I would think history of the fields would be kept in a separate database. Or ever have a second field that displays the previously named field for a more user friendly experience. But first i would like to get this basic need taken care of. My guess is that the rest will come together once it is.


                      Im pretty sure the fix for the problem will be simple and using the merge field from the parent table will be my answer once we figure out what could be blocking that in the portal. Thanks for all the quick help, you guys are great!

                      • 9. Re: Many to Many Relationship and auto-updated field

                        Rob -


                        Did you look at the example I posted?

                        1 of 1 people found this helpful
                        • 10. Re: Many to Many Relationship and auto-updated field

                          Mike, I am pulling in a csv file from a radio station's airtime data.   This table has records which I want to relate to the parent table, the value is basically a radios station's call letters which I want to be the referencial field to it's child data which is commercial end times going back into content (fwiw, to maximize the station's ratings ).  FileMaker ProScreenSnapz001.png

                          I look thru your project and I don't understand wherre you create a value for UUI, is it a global variable that I am missing?


                          I am simply trying to duplicate the call letters ( text) of the parent into a field of the same in the child so that this can become the (only) relational field.


                          FileMaker Pro AdvancedScreenSnapz001.png


                          I am simply trying to populate DaillyPPMDate table's CallLets field from a feild in the stations table upon import (from a CSV file)..  


                          Am I missing something?

                          • 11. Re: Many to Many Relationship and auto-updated field

                            Nevermind on the UUID thing I see it in the list of get functions...

                            • 12. Re: Many to Many Relationship and auto-updated field

                              Thank you Mike for the example. I was pulled away and could not reply promptly. I do appreciate you quick replies.


                              My solution is set up the same as the your example. I can not figure out what could be wrong. the relationships are working, but the data does not show in the portal. frustrating. Maybe i need to rebuild this from scratch this and start from the beginning.



                              • 13. Re: Many to Many Relationship and auto-updated field

                                Your relationship between the tables isn't valid. When FileMaker says "<Index Missing>", it's telling you it can't index the field you're using for a key. That means it's one of the following:


                                1) A global field

                                2) An unstored calculation


                                You need to set the field to an actual, indexable field. Look again at my example, specifically at the key fields being used for the relationships.



                                • 14. Re: Many to Many Relationship and auto-updated field

                                  Yes Mike - that was it. Thank you for that help and the very quick replies. I was just logging back in to share it also - I also figured it out when i saw in your example that indexing was turned on and not in my solution.

                                  ...So each field under the 'manage database' that needs to be shared in a portal needs to also be indexed.


                                  What im not clear on is 'minimal' vs 'all' with indexing. Also, what is Automatically create indexes as needed. Digging in my books and manuals i have im not fully understanding why i would use one over the other.

                                  1 2 Previous Next