13 Replies Latest reply on May 23, 2013 9:23 AM by carolyn

    Relationship issue: How do I get the current records ID instead of the first ID with containing the...

    carolyn

      Title

      Relationship issue: How do I get the current records ID instead of the first ID with containing the foreigh key match

      Post

           Hi all,

           I am trying to get the active fund ID through the following complex relationship.  Instead I am getting the fund ID of the FIRST fund record where the panel foreign key in the fund table matches the Panel ID.  Here are the revelent tables and feilds:

            

           FUND TABLE

           Fund_ID_Key

           Panel_ID_Foreign Key

            

           PANEL TABLE (Related to fund table by Panel_ID_Foreign Key = Panel_ID_Key)

           Panel_ID_Key

            

           PANEL PEOPLE TYPE ONE (Related to Panel Table by Panel_ID_Foreign Key = Panel_ID_Key)

           Panel_ID_FK

           Person_ID_FK

           Fund_ID_FK (calc field)

           Person start date with Panel

           Person start date exception by fund (related feild from table Person Start Date Exception by fund based on Person ID and Fund_ID_FK)

           Person start date use

            

           PERSON INFO TABLE

           Person_ID_Key

            

           PERSON START DATE EXCEPTION BY FUND (related to Person Info Table by matching Person IDs and related to PANEL PEOPLE TYPE ONE by person ID and Fund Id fk)

           Person_ID_FK

           FUND_ID_FK (enter valid IDs through drop down)

           Start Date Exception by fund (text field)

            

           Problem: the fund_ID foreign key calc field in  PANEL PEOPLE TYPE ONE will not pick up theFund ID for the current fund in the fund table.  It picks up the fund ID of the first fund showing where the panel ids match.  I tried using a related field and using the get function.  I added a test cald field in the fund table and entered an exact value that value was successfully pulled into the People Type One table.

           The rest of this working great.  Thanks for any help.  I'd love to be able to solve this one without restructuring.

           Carolyn

            

        • 1. Re: Relationship issue: How do I get the current records ID instead of the first ID with containing the...
          philmodjunk

               What I can deduce about your relationships:

               Person-----<Panel People... >-----Panel-----<Fund

               Relationships as described for PERSON START DATE EXCEPTION BY FUND are not possible as they would create a "cycle" in your relationship graph so I would guess that you have some other detail missing here. (Can't link it to both Person and Panel People... when Person and Panel People are also linked to each other.)

               What makes a  particular Fund record the "current" fund? The oldest date in a date field? The most recently created Fund record of those that are related to Panel? The current record on a Fund layout? (That one's problematic as the current record may not even be related to the current record in Panel Peoplel...)

          • 2. Re: Relationship issue: How do I get the current records ID instead of the first ID with containing the...
            carolyn

                 Hi Phil,

                 Thank you for the quick response.  I did make mini database illustrating the relationship but I don't see a way to submit it.  So here are some screen shots.  By the way I am working in FM Pro 11 and Mac OS 10.6.8.

                  

                  

                  

            • 4. Re: Relationship issue: How do I get the current records ID instead of the first ID with containing the...
              carolyn

                   Fund date exception entered through a portal on person info layout

              • 6. Re: Relationship issue: How do I get the current records ID instead of the first ID with containing the...
                carolyn

                     If you have the time to recreate this database sample, you'll see that the fund ID in the portal in the fund Table layout will show the table ID of the first fund to which a panel is attached instead of showing the Fund ID of the current fund.

                     Thanks so much for any help!!

                     Carolyn 

                • 7. Re: Relationship issue: How do I get the current records ID instead of the first ID with containing the...
                  philmodjunk

                       I have more quesitons than answers at this point.

                       

                            Fund Table layout with portal

                       Portal to WHAT table (Occurrence)?

                       

                            Fund date exception entered through a portal on person info layout

                       Same question: What table occurrence? (And note that you have two occurrences of Person date Exception by Fund that might be referenced by your portal...

                       And I repeat this question:

                       What makes a particular Fund record the CURRENT fund?

                  • 8. Re: Relationship issue: How do I get the current records ID instead of the first ID with containing the...
                    carolyn

                         Layout Fund Table Portal

                         Using related records from People Type One.  No new records are allowed through this relationship.

                         Portal Fields are:

                         People Type One:: Panel_ID_Fk 

                         People Type One:: Person_ID_Fk

                         People Type One:: Fund_ID_Fk

                         People Type One:: Start Date with Panel

                         People Type One:: Person Start Date Fund Exception

                          

                         Panel Table Layout Portal

                         Using related records from People Type One.  Records for People Type One are only created through this portal.

                         Fields are:

                          

                         People Type One:: Panel_ID_Fk  (value entered automatically via the relationship)

                         People Type One:: Person_ID_Fk (entered by a drop list which is a value list of Person Ids)

                          

                         People Type One:: Start Date with Panel (text field (may change to number) in which year is entered)

                          

                         Person Info Table Portal

                         Records are from Person Date Exception By Fund.  Records for the Person Date Exception table are created through this portal only.

                         The fields are:

                         Person Date Excetion By Fund::Person_ID_FK (automatically entered via the relationship)

                          

                         Person Date Excetion By Fund::Fund_ID_FK (users adds by using a drop down list of fund IDs)

                          

                         Person Date Excetion By Fund::Date_Exception_By_Fund (entered text field which may ne changed to a number)

                          

                         The current fund ID should be determined by the fund record that you are on when on the FUND TABLE LAYOUT.

                         The goal is that once the panels and exceptions have been entered.  A user or script can go to aparticular fund record from the FUND TABLE LAYOUT and see if any date exceptions apply for a particular person for that fund.  Not all will have date exceptions.  Then the goal is to use the panel date unless there is a date exception for that person for that fund.

                          

                         The reason for the panels is that there are maybe 100 funds but only 3 panels.

                          

                         I hope this helps.  Thanks again for looking into this.  Please let me know if you have anymore questions.

                          

                         Thanks,

                          

                         Carolyn

                          
                          

                          

                          

                          

                    • 9. Re: Relationship issue: How do I get the current records ID instead of the first ID with containing the...
                      philmodjunk
                           

                                Layout Fund Table Portal
                                Using related records from People Type One.

                           Which means that your portal will list all records from People Type One that are linked to the one record in Panel Table that is linked to your current record in Fund Table.

                           

                                Panel table Layout portal

                           Not a big deal, but not shown in your original screen shots so the fact that you have such a layout and portal is new info. It confirms that People Type One is set up to serve as a join table between Panel and People...

                           

                                A user or script can go to aparticular fund record from the FUND TABLE LAYOUT and see if any date exceptions apply for a particular person for that fund.

                           Ok, se we know which fund is current, but then how will you specify the "paricular person"? From Fund, we can get a list of all members of the related panel, but as you have discovered, given your current relationships, a portal to exceptions will list all the exception dates for the people on the panel for all funds.

                           There are several approaches possible that can resolve this issue:

                           One is to use ExecuteSQL with a WHERE clause that limits any exception dates returned to the current fund record's ID.

                           Another option is to take the People Type One::Fund_ID_Fk field and define it as a number field with global storage if it is not already so defined. Then, you can set up the OnRecordLoad script trigger on your Fund Layout to perform a script that does this:

                           Set Field [People Type One::Fund_ID_fk ; Fund Table::Fund_ID_Key ]
                           Commit Records[]
                            

                           Then a portal to People Type One can list each member of your panel and the Date Exception by Fund field from Date Exception by Fund 2 can be used to display any exception dates.

                           PS. I am assuming that all of your ID fields are number fields.

                      • 10. Re: Relationship issue: How do I get the current records ID instead of the first ID with containing the...
                        carolyn

                             Thanks Phil,

                              

                             Sorry about not showing the Panel Layout with the portal.

                             It will take a bit of time to digest everything you stated; however, I"m unclear about your statement about the portal on the table fund layout.

                             1) In my prototype my portal on the fund table only shows the person type one people that are assigned to the panel which is in turn assigned to that fund.  Either I misunderstood your statement or we may be set up slightly differently.  If I can determine the Fund ID in that portal my problem is solved.

                             2) Love the trigger Idea but we publish on web with wip.  I haven't looked into this recently; however, to my knowledge trigers don't work via the web.  

                             I have developed my own navigation buttons. I hide the FM maker buttons, but some of our users with high security clearence try to get around using them. However, Maybe I could make special scripts for the fund Table layout nav buttons and and put this code there? We extract our data using Apple Script.  So we would have to use another approach for data extraction since the scripts don't use buttons.  I guess once the fund record is found set Person_Type One::Fund_ID_fk before the data is extracted.  I would change this field to a global as you suggest.

                             I see how the user script only would work.  We use apple scripts for extraction scripts.  However, I was hoping to do this within the database for 2 reasons:

                             1) I would like this information to be veiwed  collectively on the Fund Layout table through the database.

                             2) This is part of a bigger picture.  Right now the full panel output is built within Filemaker so everyone can see it.  It includes the Names, Born Dates, Start Dates, Biography and other person panel info.  Then the complied feild is extracted on one lump. So if I use the set field solution, I'll have to put it in the buttons and equililent in the extraction scripts.

                             I'm not that familiar with ExecuteSQL.  Could you point me to where I could get more information.

                             What about apple scripts in fields.  I haven't worked with those.  Do you see an answer there?

                             Just so I am clear you don't see a way where I could pick up the Fund_ID in PeopleTypeOne:Fund_ID solely through a relationship and field definitions?  

                             Thank you so much for working this out with me.

                             Carolyn

                        • 11. Re: Relationship issue: How do I get the current records ID instead of the first ID with containing the...
                          carolyn

                               Oh I forgot to answer.  Yes all of the ID fields are number fields.

                          • 12. Re: Relationship issue: How do I get the current records ID instead of the first ID with containing the...
                            philmodjunk

                                 1) What you just said is what I said in different words. The portal lists the people from the assigned panel.

                                 2) Correct, triggers cannot be directly tripped by user actions via IWP. BUT scripts that change layouts, change records, etc can trip scirpt triggers. If use a script to bring up a specific fund record on that layout and/or you use a script to take the user to that layout, those scripts can set the global field just like the script trigger that I originally suggested.

                                 I don't currently own a Mac system and haven't played with AppleScript in years. I don't see a use for it, but I don't really follow all that you are trying to do with this data, my focus has been on successfully accessing the correct exception date for a given fund--which should work once you get the correct value into the global field, but there could be a "refresh" issue with IWP where you have to commit records after setting the global field to a global value.

                                 ExecuteSQL requires an understanding of the SQL query language. There are numerous sources of info on SQL via the web and you can see actual syntax examples in the JDBC ODBC Guide you can access via FileMaker's Help menu. SeedCode's SQL Explorer is also helpful in learning how to work with this function.

                            • 13. Re: Relationship issue: How do I get the current records ID instead of the first ID with containing the...
                              carolyn

                                   Phil,

                                   Thank you for all of your help!!!

                                   Carolyn