8 Replies Latest reply on Jul 21, 2017 6:41 AM by philmodjunk

    Search for specific text field information in related table

    BenjaminMaurer

      Hi Everyone,

       

      I stumbled upon the following issue which needs a formula for conditional formatting in order to provide me visual feedback in my database.

       

      One of my portals lists projects, another table has documents.

      The portal should give visual feedback if a document, defined as a specific class is created in the Document table.

      (Represented by a o if a document exists, if no file with that class is available it should be shown as o.

       

      Each o should check: Is there a document with the Class "Overview" / "Closeout" connected to "Current Project" in the Document table.

      (see below)

       

      My mind is currently not capable of finding the right solution to it.

      Any ideas?

       

      Thanks!!

       

      Project portal:

                      Overview    Closeout

      --------------------------------------------

      Project 1 |        o       |           |

      --------------------------------------------

      Project 2 |             |       o      |

      --------------------------------------------

      Project 3 |        o       |       o       |

      --------------------------------------------

      Document table:

      Document 1

           Project: Project 1

           Class: Overview

      Document 2

           Project: Project 2

           Class: Closeout

      Document 3

           Project: Project 3

           Class: Overview

      Document 4

           Project: Project 3

           Class: Closeout

        • 1. Re: Search for specific text field information in related table
          philmodjunk

          You'll need to describe your tables, data and relationships in quite a bit more detail. For example, you describe two portals, one to projects and one to documents. That implies a third table related to projects and also to documents. What is that third table? How does it link to documents and how does it link to projects?

           

          Documents 1 and 3 have the same class. Does that mean that these are two different documents in the same category or are these two references to the same exact document?

          • 2. Re: Search for specific text field information in related table
            BenjaminMaurer

            The layout is a little more complex, I wanted to explain it on a simplified example but I believe I failed:

            Here is the full approach:

             

            Project table

                 Fields:

            •      Project Name
            •      Portal to Material connected to Project (via Project Name)

                                this Portal lists the Material connected to the Project

             

            Document Table

            Fields:

            •      Document ID
            •      Project Name (list for Project Name table)
            •      Class (Overview, Closeout, Other)
            •      Connected to Material (this is a drop down where the material can be selected)

             

            Material Table

            Fields:

            •      Material Name
            •      Project Name (list for Project Name table)

             

            all tables are connected via Project Name.

             

            What the conditional formatting for the Material Portal should do is the following:

             

            Check if any of the documents has a connection to this Material via the "Connected to Material" field and has the Class defined as "Overview", if yes highlight (The project is automatically checked via the table connection).

             

            The documents will be individual documents, and there can even be more than one document with the same class per project (but other ID). If at least one is present with the desired class the conditions for highlighting are met.

             

            Is this a little clearer?

             

            Thanks!!

            • 3. Re: Search for specific text field information in related table
              philmodjunk

              Somewhat Clearer.

               

              Your layout is based on Projects?

               

              with one portal to documents and one to Material?

               

              You state:

              all tables are connected via Project Name.

               

              but then you say:

              Connected to Material (this is a drop down where the material can be selected)

              That leaves me uncertain as to how Documents is linked to the other tables. By Project Name? By Material? Both?

               

              PS. as a side note, linking these records by name is not the best way to link as it can create issues should you find that you have to change a project name--which can happen for a number of reasons after you have already linked records by project name. better to use a unique ID generated in the Projects table. But this is secondary to figuring out your original question.

              • 4. Re: Search for specific text field information in related table
                philmodjunk

                A screen shot of your relationships graph is probably the simplest way to clarify this. You can use the picture icon in the tool bar of the reply box to select a screen shot and embed it into your reply.

                 

                Please note that this still leaves other questions that I asked unanswered.

                • 5. Re: Search for specific text field information in related table
                  BenjaminMaurer

                  databaseconnection.png

                   

                  Linkage is all via Project Name in that case.

                  The drop down only presents a list of possible material to ensure that a material is selected that exists in the database.

                   

                  I decided to use the Project Name as linkage, because the documents are created independently and then assigned a specific Project.

                   

                  The Project Layout has two portals yes, one lists ALL documents assigned to the Project and the other ALL material assigned to the Project. But the highlighting should be in the material portal to say: THIS specific document for the material exist e.g. "overview".

                   

                  Maybe my structure is completely messed up...

                  • 6. Re: Search for specific text field information in related table
                    philmodjunk

                    "I decided to use the Project Name as linkage, because the documents are created independently and then assigned a specific Project."

                     

                    That would not cause me to use a project name. I'd still use a project ID.

                     

                    But to answer the original question, there are several possible approaches that all match by project AND by class. You can set up additional match fields with constant values such as "overview" that you match to an occurrence of documents along with the project name.

                     

                    You can use ExecuteSQL to query documents with a WHERE clause that matches by project and class.

                     

                    A portal to documents with a portal filter that specifies the class might also be used.

                    1 of 1 people found this helpful
                    • 7. Re: Search for specific text field information in related table
                      BenjaminMaurer

                      Thank you! ExecuteSQL was the function I was looking for. Excellent! Thanks so much!

                       

                      This is a very nice universal function that opens a lot of possibilities!

                       

                      (The portal works as well but does not help with the conditional formatting.)

                      • 8. Re: Search for specific text field information in related table
                        philmodjunk

                        "The portal works as well but does not help with the conditional formatting.)"

                         

                        Therer are ways that can work. The Isempty function can be used to determine whether or not to apply the conditional format.