1 2 Previous Next 20 Replies Latest reply on Jan 29, 2015 4:31 AM by areal

    projects and contacts - unrelated table setup

    areal

      Title

      projects and contacts - unrelated table setup

      Post

      I am using a project management setup, with the table Projects and the table Contacts, connect by the table project_contact. This is a many-to-many relationship, so a contact can have several projects and visa versa.

      Layout

      I'm making a layout that has a list of all contacts, not just the related ones, to the left. I've managed this by duplicating the table occurence and making a X-relationships to the other contacts table. While this duplicated table occurence (Contacts_list) sits to the left of the layout, to the right is the normal - single record - Contact file. When you click on one of the contacts on the left (go to related record) -> the record is loaded to the right. This works so far so good.

      Project filter

      I'm pretty stuck at the next phase, however. What I want to do next is put a value list of all project on the left on the interface, just below the list. What I want to do is when I choose a project from the list, the Contacts_list is then filtered by whether or not that contact is connected to that project. 

      Because the Contacts_list is not related to anything, I cannot seem to get the related project(s).

       

      I hope this is clear enough? If you have additional questions please ask :). Thanks in advance!

      A.png

        • 1. Re: projects and contacts - unrelated table setup
          philmodjunk

          Is this a problem of getting the value list to work or getting the list of contacts to filter once you've selected a project from the value list?

          • 2. Re: projects and contacts - unrelated table setup
            areal

            It's about getting the list of contacts to filter once I've selected the project.

            • 3. Re: projects and contacts - unrelated table setup
              philmodjunk

              It would seem that the portal uses this relationship:

              Contacts::anyfield X Contacts|all::any field

              with your layout based on contacts and your portal based on contacts|all.

              If you add another occurrence of the join table:

              Contacts|all::ContactID = Project_contact|all::ContactID

              then your value list can enter a projectID into the field and you can set up this portal filter expression to limit the portal to just those contacts that are linked to a particular project:

              IsEmpty ( Contacts::SelectedProjectID ) or Contacts::SelectedProjectID = Project_contact|all::projectID

              The "IsEmpty" term will show all records when you have not yet selected a project ID for SelectedProjectID using your value list.

              Note that SelectedProjectID should not be a global field and you'll need to commit records each time you select a new project before the portal will update. You can click the layout background to commit records or you can add a script trigger on your SelectedProjectID field to commit records each time you select a new project.

              Caulkins Consulting, Home of Adventures In FileMaking

              • 4. Re: projects and contacts - unrelated table setup
                areal

                Works perfect! Thanks!!

                • 5. Re: projects and contacts - unrelated table setup
                  areal

                  A small additional question.

                  I now have a browse mode layout with the contact details on the right, and the filtered list on the left, same as on the screenshot at the beginning of this topic. The filtered list works as described above. When I filter and click an item on the list, it goes to the related contact on the right. 

                  However, when I browse through contacts (by scrolling), it does not work as expected. The filter on the left is not applied to the contacts table on the right, and some contacts are shown on the right that are not on the list on the left. 

                  How can I make the same filter for the detailed contact list on the right? Or even better: how can I somehow pair the two table occurences without messing the abovementioned setup?

                   

                  Thanks! Lars

                  • 6. Re: projects and contacts - unrelated table setup
                    philmodjunk

                    Remember this sentence?

                    Note that SelectedProjectID should not be a global field

                    If you make it a global field, you'll see the portal on each record filter by the same criteria. But I made this statement because it creates "update" issues with a portal filter.

                    Change your relationship to be:

                    Contacts::SelectedProjectID X Contacts|all::any field

                    and specify global storage for SelectedProjectID. (I usually put a "g" at the start of the names of fields with global storage so I can more easily identify my global fields.)

                    If you don't include the global field as a match field and using the Cartesian join operator (X), the portal won't update when you change the value of the SelectedProjectID field unless you use a script with RefreshWindow [Flush cached Join Results], and this is a script step to use only when absolutely necessary as it can greatly delay the update of your layout each time that it is updated.

                    • 7. Re: projects and contacts - unrelated table setup
                      areal

                      It's not yet working. I've changed the relationship and made the SelectedProjectID global. Still no change.

                      This is the filter I now have on my portal:

                      (IsEmpty (Relaties voor scroll lijst::Snel zoeken filter)

                      or

                      PatternCount ( Relaties voor scroll lijst::volledige naam ; Relaties voor scroll lijst::Snel zoeken filter ) > 0) 

                      and 

                      (IsEmpty (Relaties voor scroll lijst::filter)

                      or

                      PatternCount ( Relaties voor scroll lijst::organisatie of persoon ; Relaties::filter ) > 0) 

                      and 

                      (IsEmpty (Relaties voor scroll lijst::filter alfabet)

                      or

                      Left ( Relaties voor scroll lijst::volledige naam; 1 ) = Relaties::filter alfabet)

                      and 

                      (IsEmpty ( Relaties voor scroll lijst::snel zoeken project globaal ) 

                      or 

                      Relaties voor scroll lijst::snel zoeken project globaal = Project_Relatie 2::_project_ID)

                      • 8. Re: projects and contacts - unrelated table setup
                        philmodjunk

                        How does the above filter expression vary from your original?

                        Is "Relaties voor scroll lijst" the name of the table occurrence specified for your layout?

                        Did you use field options to make snel zoeken project globaal a field with global storage?

                        If you perform this script:

                        Refresh Window [Flush cached join results]

                        Do you see a change in what appears in the portals?

                        • 9. Re: projects and contacts - unrelated table setup
                          areal

                          How does the above filter expression vary from your original?

                          I'm not sure.

                          Is "Relaties voor scroll lijst" the name of the table occurrence specified for your layout?

                          No. Relaties is the name of the TO for this layout.

                          Did you use field options to make snel zoeken project globaal a field with global storage?

                          Yes I did. Well actually via ctrl + shift + D.

                          If you perform this script:

                          Refresh Window [Flush cached join results]

                          Do you see a change in what appears in the portals?

                          No. Doesn't change anything.

                          • 10. Re: projects and contacts - unrelated table setup
                            areal

                            And there's another related (and slightly more urgent) question. I now have the following setup.

                             Relaties::snel zoeken project globaal X Relaties voor scroll lijst::__relatie_ID

                            The layout is based on Relaties,the portal based on Relaties voor scroll lijst. The portal filter is:

                            (IsEmpty (Relaties voor scroll lijst::Snel zoeken filter)
                            
                            or
                            
                            PatternCount ( Relaties voor scroll lijst::volledige naam ; Relaties voor scroll lijst::Snel zoeken filter ) > 0) 
                            
                            and 
                            
                            (IsEmpty (Relaties voor scroll lijst::filter)
                            
                            or
                            
                            PatternCount ( Relaties voor scroll lijst::organisatie of persoon ; Relaties::filter ) > 0) 
                            
                            and 
                            
                            (IsEmpty (Relaties voor scroll lijst::filter alfabet)
                            
                            or
                            
                            Left ( Relaties voor scroll lijst::volledige naam; 1 ) = Relaties::filter alfabet)
                            
                            and 
                            
                            (IsEmpty ( Relaties voor scroll lijst::snel zoeken project globaal ) or Relaties::snel zoeken project globaal = Projecten 3::__project_ID)

                             

                            Well, actually everything mentioned in the filter works perfectly, except the last two lines. I cannot get them to work.

                            There is a list of all contacts (Relaties voor scroll lijst). Normal contacts are connected to projects with a many-to-many join table contact_projects. This works correctly in 'normal' portals on several layouts. 

                            Above the list with all contacts is a selection field. Here, you can select a project and the portal gets filtered by project. The field is Relaties::snel zoeken project globaal. 

                            To be able to filter the portal by project I have created a duplicate of the Contacts_projects and projects tables on the 'other side' of Relaties voor scroll lijst. 

                            Relaties voor scroll lijst::_relatie_ID | project_relatie::_relatie_ID
                            
                            project_relatie::_project_ID | project::_project_ID

                             

                            Okay, so what DOES it do? It does give me a perfect list of all contacts connected to that particular project. However, it DOESN'T always give me contacts that have more than one project. If a contact has 4 projects, it is only mentioned in 1 of the project, not in all of them. My goal is to list ALL contacts connected to the selected project.

                            Is this at all possible? Thanks again!

                            • 11. Re: projects and contacts - unrelated table setup
                              philmodjunk

                              You've introduced a third table occurrence name. Note that the part of your filter that works refers to your Layout table's fields such as:

                              Relaties::filter alfabet

                              while in this new expression, you refer to

                              Projecten 3::__project_ID

                              That reference to Projecten 3 is probably the issue at fault here. If I remember how this works correctly, this reference will evaluate from the context of the current record in Relaties instead of from the context of each portal record.

                              • 12. Re: projects and contacts - unrelated table setup
                                areal

                                I've tried this:


                                (IsEmpty ( Relaties voor scroll lijst::snel zoeken project globaal ) 

                                or 

                                Relaties voor scroll lijst::snel zoeken project globaal = Project_Relatie::_project_ID)

                                 

                                and this

                                 

                                (IsEmpty ( Relaties voor scroll lijst::snel zoeken project globaal ) 

                                or 

                                Relaties voor scroll lijst::snel zoeken project globaal = Projecten::_project_ID)

                                One refers to the normal table for projects. The other refers to the join table of projects and contacts. Both don't show any related contacts in the Relaties voor scroll lijst table.

                                 

                                This is (part of my) setup.

                                 

                                I've added Relaties 5, Project_relatie 2, and Projecten 3 to make it work. But it doesn't :).

                                • 13. Re: projects and contacts - unrelated table setup
                                  areal

                                  It does not evaluatie from the context of the current record (I think) because of this relationship:

                                   

                                   Relaties::snel zoeken project globaal X Relaties voor scroll lijst::__relatie_ID

                                  • 14. Re: projects and contacts - unrelated table setup
                                    philmodjunk

                                    Just to confirm what I see and remember: Your layout specifies Relaties and your portal specifies Relaties voor Scroll Lijst?

                                    to start, Relaties voor scroll lijst::snel zoeken project globaal and Relaties::snel zoeken project globaal are not the same thing. You use Relaties voor scroll lijst::snel zoeken project globaal in your portal filter, but refer to Relaties::snel zoeken project globaal in your relationship.  And I don't see a table occurrence named "projecten" anywhere in your screen shot of your relationships. Nor do I see one named Project_Relatie.

                                    Do Relaties and Relaties voor scroll lijst have the same data source table?

                                    1 2 Previous Next