1 2 Previous Next 20 Replies Latest reply on Aug 3, 2014 8:15 PM by FentonJones

    Help with a Join Table

    sccardais

      Title

      Help with a Join Table

      Post

           My FMP file is used to track membership info in our neighborhood Homeowners Association.

           My question is about maintaining a history of all current and previous owners for each lot.

           Lots are "owned" by a single Owner / Entity. Tables = Owners and Lots

           Owners can own multiple Lots.

           Over time, ownership changes. Owners may sell some lots and keep others.

           Likewise, over time, one Lot can be owned by multiple Members - but not more than one Owner at a time.

           If we want to keep a record of all Current and Previous owners, is this a situation that requires a Join table (e.g. OwnerLotTransaction) with match fields OwnerID and LotID?

           If a single lot had multiple records in the join table, how could we automatically flag the Current owner and change the status of previous owners to "Previous"?

           Would this require a calculation field (OwnerStatus) in a join table that somehow sets the value as Current in the new and Previous in the old?

           This is beyond my current knowledge of FileMaker. I know enough to know it CAN be done but not sure how!

            

        • 1. Re: Help with a Join Table
          philmodjunk

               Yes, you are describing a many to many relationship best managed through use of a join table.

               Start with these relationships:

               Owners-----<Owner_Lot>-----Lots

               Owners::__pkOwnerID = Owner_Lot::_fkOwnerID
               Lots::__pkLotID = Owner_Lot::_fkLotID

               You can place a portal to Owner_Lot on the Owners layout to list and select  Lots records for each given Owners record. Fields from Lots can be included in the Portal to show additional info about each selected Lots record and the _fkLotID field can be set up with a value list for selecting Lots records by their ID field.

          From this basic set up you can add a date field to Owner_Lot to record the date of when that owner acquired that lot. You can then place a portal to Owner_Lot on a Lots layout and sort the portal by this date in descending order to put the current owner in the first row of the portal.</owner_lot>

               You can also specfiy that the Owner_Lot records be sorted in this order as part of the Lots to Owner_Lot relationship. If you do that, then the current owner of a given lot will be the first related record via the Lots to Owner_Lot to Owners relationship chain. With that sorted relationship, you can put fields from Owners on your Lots layout and they will list data about the current owner.

          • 2. Re: Help with a Join Table
            sccardais

                 Phil:

                 For some reason, this is not working yet. The screenshot shows a layout based on table "Lots"

                 The portal is based on Transactions (What you called Owner_Lot above in your reply)

                 The relationship between Lots and Transactions is sorted on Transaction Date in descending order.

                 The Portal is also sorted by Transaction Date. 

                 The Owner ID and Owner Name fields are from the Owner table. As you can see, they don't match the sort order shown in the portal. Any idea what I did wrong?

                 Disregard the status field showing Current and Previous. That was another approach I was considering but hadn't quite worked out how to change the status of all other records with same Lot ID to Previous.

            • 3. Re: Help with a Join Table
              philmodjunk
                   

                        The relationship between Lots and Transactions is sorted on Transaction Date in descending order.

                   

                        The Portal is also sorted by Transaction Date.

                   You should not need to sort the portal if you have specified the correct sort order in the underlying relationship. What happens if you remove the sort order specified for the portal?

                   I suspect that the data in the first row of your portal will then match the fields outside the portal from Owners. If so, this suggests that you do not have the correct sort order specified at the relationship level.

              • 4. Re: Help with a Join Table
                sccardais

                     Phil:

                     I removed the sort order from the portal and set up the relationship between Lots and Transactions to sort on Transaction date / descending. The portal sorts the transactions properly. I added two fields from Owners outside the portal on the layout based on Lots. One of these fields is correct, the other isn't. The screen shot will illustrate.  Screenshot below also shows sorting relationships. I've tried every combination of sorting I can think of but none of them work as I'd expect. Can you see why this isn't working?

                      

                • 5. Re: Help with a Join Table
                  philmodjunk

                       The only thing that I can think of is that ID and Owner Name do not come from the same table occurrence. Enter layout mode select each one at a time and check to see what is shown in "display data from" on the inspector's data tab. My guess is that Owner Name is referring to a different occurrence of owners and thus is not showing data from the correct record.

                  • 6. Re: Help with a Join Table
                    sccardais

                         You are correct. The Owner ID that is outside the portal is from the Transaction table. My mistake.

                         I guess I was assuming that I could "tunnel" to the Owner's table from Lots thru Transactions because of the Relationship.

                         Do I need a separate TO to make this work?

                    • 7. Re: Help with a Join Table
                      philmodjunk

                           You should just need to add the Owner Name field from the correct table occurrence. The specified sort order in the relationship between Lots and Transactions should be all that is necessary here.

                           Just to test my assumptions, I created a small demo file and produced this screen shot from it. The transactions were entered in reverse order, the record with 7/27/2014 was the first record entered. The layout is based on Parent1, the portal is to Join and the other fields in the join table and the two at upper right that show an ID and a name are from Parent 2.

                           It all worked for me, but I'm not spotting what is different in your case.

                      • 8. Re: Help with a Join Table
                        sccardais

                             It's a mystery. As far as I can tell, I'm doing exactly what you're doing but getting different results. I've attached a dropbox link to my version in case you have time to look at it.

                             https://www.dropbox.com/s/n0o9f3vsipufvxt/ExperimentCurrent%20Owners%20Copy.fmp12

                             I've looked at this until my eyes crossed and I just don't see why I'm getting different results. Thanks for your help.

                        • 9. Re: Help with a Join Table
                          philmodjunk

                               After some testing I must conclude that I was assuming that the sort order between Lots and Transactions would also control what record from Owners would appear as the "first" related record and this is not the case--at least with FileMaker 13, I'm going to test with older versions to see if I've been wrong about how this works or if a bug crept in.

                               But the way to show the current owner is to use a one row portal to transactions and put the owner name in the row of that one row portal.

                          • 10. Re: Help with a Join Table
                            sccardais

                                 Thanks very much for digging deeper into this Phil.

                                 It's a relief to know that I wasn't missing something simple - which I've done MANY times before!

                                 Originally, I was going to create a new TO (CurrentOwner) between Lot and Transactions - using a match field "Status" to identify the current owner. In Lots, the "Status" field would be a calculation field with a global value = "Current".

                                 In Transactions, the Status field will a value of "Current" or "Previous." So -- a layout based on CurrentOwner TO would filter my view to the Owner table. Assuming I can find a good way to make sure the Status field in older transactions for the same Lot in Transactions are always changed to "Previous", I think this could work. Am I thinking about this correctly?

                                 Can you suggest a way to automatically set Transactions::Status to "Previous" when a transaction with a more recent date is created. (e.g. In Transactions, find all transactions with the same Lot ID. Set the Status field of the transaction with the most recent transaction date to "Current" and the rest to "Previous." Make sense or am I over-thinking this?

                                 Perhaps there is a better way? We just need a way to make sure that we can identify the current owner and address all communications (email, snail mail, phone, etc.) to the current owner.

                            • 11. Re: Help with a Join Table
                              sccardais

                                   FENTON JONES

                                   Thank you very much for your detailed replies. Because the scope of these projects tend (in my case) to get more complicated over time, I need to remind myself of the basic goal and hopefully find the simplest, most straight-forward and fool proof way to do it. In this case, I need to know the current owner of the Lot in order to send Invoices and Communications to the correct person.

                                     
                              •           The current owner cannot be flagged in the Owner table because they may be the Current owner of some Lots and the Previous Owner of others.

                                   In his latest reply, PhilModJunk suggested a portal with one row showing transactions on a Lot layout based on a sorted relationship between Lots and Transactions based on Transaction Date in descending order.

                                   I have not tried this yet but if this allows me to capture email, telephone and mailing addresses of the current owner, that sounds pretty simple.

                                   If that doesn't work, I'll try your suggestions - starting with Option B.

                                   Thanks, again.

                                    

                                    

                              • 12. Re: Help with a Join Table
                                philmodjunk

                                     Let's back up a second. Since you can use a one row portal to transactions to show and access the data for the current owner, why do you need to add this "status" at all? The reason that I haven't suggested anything along these lines is that I'm having trouble imagining why this complication to your basic design is really needed.

                                • 13. Re: Help with a Join Table
                                  sccardais

                                       Phil:

                                       If the Owners table is going to include records from Previous and Current owners, I want to make sure I address all communications to the Current Owner. I know you know this but - as you said - let's back up a second. I agree wholeheartedly!  The real goal here is to accurately identify the current owner and use information from the Owner record to generate invoices, correspondence, etc.

                                       I did a quick test using a one row portal on a layout based on your latest suggestion (one row portal). See screenshot. It worked in one way but I don't see a way to get the correct information from the portal into an Invoice document (for example) or a group email to all current owners.

                                  • 14. Re: Help with a Join Table
                                    FentonJones

                                          

                                         Well, I deleted my earlier posts. Because they simply were not needed. I will try and answer this last problem, as I don't think the portal is going to do well with that; remember, that "current" Owner ID showing in the 1 row portal is not the ONLY one in the portal.
                                          
                                         What would work to see only that "current" Owner ID would be a calculation field in the Lots table. The calc is:
                                         Transactions_JOin::OwnerID_fk
                                          
                                         A calc via a relationship will return (by default) only the first match. Since the relationship is sorted, if will return the "current" OwnerID; unstored. [ Same as the 1 row portal shows, but read on.]
                                          
                                         Now, you wanted to show ALL the "current" Owners, of all the Lots. That would require another relationship. From the Lots TO, using the above calc field to the Transactions table, to the OwnerID_fk field (which can be Indexed in Transactions).
                                          
                                         Using Go to Related Record [ above relationship; List_Owners; Show all related records; Match ALL records in current found set ]
                                          
                                         Show All Records in Lots and the above will take you to All Owners who are "current". [ Currently 3 of them; 4, 5, 6 ]
                                          
                                    1 2 Previous Next