4 Replies Latest reply on Sep 15, 2012 1:21 AM by crtopher

    Identifying currently selected record in a Universally related table.



      Identifying currently selected record in a Universally related table.


           Table named Phoners has three fields Phoner and ESTOkay and Batch. Members has three fields, Member, EST, and Batch.

           I need to create a Batch of members for the Phoner to phone, however if the Members is EST, the Member can only be assigned to the Batch if the Phoner's ESTOkay flag is ESTOkay (this is for daytime phonong).

           Currently I relate them through the Batch field. So the first Phoner is Batch "A" and when I go to the first Member to check it, it has no Batch therefore it is unrelated to Phoner and therefore Phoners::Phoner is null.

           I made relationship Member X Phoner, hoping that if i selected the second record in Phoners, then in Members, Phoners::Phoner would be the second record, however it appears the related record for the X relationship is always the first record.

           Suggestions how to accomplish this? I find that using scriptparameter allows me to transfer the data and get some of the work done, but it does not allow me to view on the layout whether or not this EST Member is Phoneable by the record selected in Phoners or in MembersXPhoners. Idears? I guess one other way would be to create a global at Members and fill it with the script parameter. Any better ideas than that? Get(CurrentFieldContents) did not seem to work.

           Regarding Universal relationships, does it matter which fields are related, and whether there is any data in them?

        • 1. Re: Identifying currently selected record in a Universally related table.

               Phoners has many People who  Phone

               Members has many People who are phoned

               This seems to be a Many to Many relationship, sometimes difficult to manage the data.

               Phoners >-- Calls --< Members  would be a Many to One to Many relationship.   This is called normalization in database speak.

               Thus a new table "Calls" would be related to Phoners and Members both.

          Home > Designing and creating databases > Creating a database > About planning a database

          About planning a database
               A well-designed database promotes consistent data entry and retrieval, and reduces the existence of duplicate data among the database tables. Relational database tables work together to ensure that the correct data is available when you need it. It’s a good idea to plan a database on paper first.
               Follow these general steps to plan a database:

          Relational Database Design 101 (part 1 of 3): Designing a Flat File Database

          Relational Database Design 101 (part 2 of 3)

          Relational Database Design 101 (part 3 of 3)

          The White Paper for FMP Novices is useful  - 

          • 2. Re: Identifying currently selected record in a Universally related table.

                 I suspect we'll need a lot more info about how you want to set it up. I'm sure if by "batch" you just need a group of members that can or can't be called by a given phoner. If I understand correctly, a phoner is either EST Ok, or not. If she is Ok, she can call any member, regardless of their EST status. If not, then she can only call members who are not EST. You could do this by having the respective EST fields as containing "1" if they are ESTOK, or EST, OR "0" if not. Then relate the EST fields thus - Phoner::ESTOK >= Member::EST. Then you can have a portal on your Phoner layout based on the members table - it will show only the members appropriate to the EST status of the phoner.

            • 3. Re: Identifying currently selected record in a Universally related table.

                   Chris Thank you and I think that works well for the way you describe it. However There are several phoners at once, and would not want two phoners phoning the same record. For that and other reasons (such as allocating certain amounts of certain types of members, such as been phoned or not yet) we want to create "batches" of assigned members that a phoner then has access to. So I need to stamp a bunch of Member records based on some criteria in the Phoner record.

                   For these and other reasons I was hoping to be able to "see" the currently selected record in a "X"related table. But apparently it only always points to the first record, regardless of which record is actually selected in that table.

              • 4. Re: Identifying currently selected record in a Universally related table.

                     If you think about it, a cross-linked relationship relates all records in one table to all records in another. If you ask filemaker to display a related record, it doesn't know which one you want, since it could pick any of the related records. So it just defaults to the first matching record. Similarly if you have a one to many relationship between phoner and members, and you ask Filemaker to display just one of the related records (say in a field on the phoners layout), it will again only display the first related member for that phoner. What you need to display the "batch" of related members is a portal based on the members table. You need an "equals" relationship between the batch fields. With the aforementioned relationships in place, that should show you the correct information. (I haven't had a chance to actually test adding that second relationship but i think it should work)