12 Replies Latest reply on Nov 9, 2011 12:06 PM by philmodjunk

    Pop-up field for all records from table having "blank" for one field

    boydcrow

      Title

      Pop-up field for all records from table having "blank" for one field

      Post

      I'm trying to make an entry form for computers at our installation in a table called computers. I have a complete list of all accessible IP addresses in a related table. The tables are related by indexed fields called "computer".

      I want to have a field where the user can look up all unused IPs by having them displayed in a pop-up field.  "Unused" IPs are defined as having a blank entry in the computer field. 

      I can't use a calculated field because I want fields from multiple records returned which doesn't seem to work.

      I can't use a portal because there is no current record value to relate to the table of the portal.  I want everything that has blank computer, regardless of the current fields in the current layout.

      Basically, I want to display a filtered table in a pop-up field on a layout based on a different table.

      Any ideas?

        • 1. Re: Pop-up field for all records from table having "blank" for one field
          philmodjunk

          What you describe is a conditional value list, but the blank field criteria provides a bit of a twist to the standard setup.

          Add a calculation field, cBlankFlag to Your IP Address table defined as:

          IsEmpty ( Computer )

          and clear the "do not evaluate if all referenced fields are empty" check box.

          Now you have a field that contains a 1 only if the computer field is empty. You can use this in a relationship for your portal or for a conditional value list.

          There's also another option that will work in FileMaker 11 if you only want a portal showing these records and not a conditional value list:

          Define this relationship:

          Computers::anyfield X BlankIPAddresses::AnyField

          BlankIPAddresses would be a new occurrence of your IPAddresses table so that you can keep any existing relationships between these two tables unmodified.

          Now put a portal to BlankIPAddressses on a layout based on Computers and include this portal filter:

          IsEmpty ( BlankIPAddresses::Computer )

          • 2. Re: Pop-up field for all records from table having "blank" for one field
            boydcrow

            The first technique does make sense to the extent that I would be filtering for "1" instead of blank.  However, I do not understand where I get the second table required to create a conditional value list. All the values are in the IP table which is related to the computers table.  At the time the user sees this entry layout, the computer name field is empty, since this is a new computer table record.  

            The same problem holds for the second technique.  My understanding is that a portal only show records related in some way to a field in the layout "host" table.  There is nothing to relate to since the user is creating a new record.

            Could you explain a way around this dilemma?

            • 3. Re: Pop-up field for all records from table having "blank" for one field
              philmodjunk

              I may have misunderstood this sentence: The tables are related by indexed fields called "computer".

              I took that to mean that a field named "computer" in the IPAddress table stored the ID of the related computers record and thus was an empty field in all records of IPAddress that are not currently linked to a record in Computers.

              The relationship would resemble:

              Computers::Computer = IPAddresses::Computer

              Is this not the case?

              • 4. Re: Pop-up field for all records from table having "blank" for one field
                boydcrow

                The layout is for New Record.  How does one relate an empty, non-unique field in a record not yet created, to a field not yet filled in?

                • 5. Re: Pop-up field for all records from table having "blank" for one field
                  boydcrow

                  The relationship between the tables is as described but there is no entry in the field computer for a record to use to find the related records in the IP address table.

                  • 6. Re: Pop-up field for all records from table having "blank" for one field
                    philmodjunk

                    A calculation field can be used to link to records in the first case and it does not need to be unique. It should, in fact always return the value 1 so that it matches to all records in IPAddress where the Computer field is empty.

                    The relationship would look like this:

                    Computers::constOne = IPAddresses::cBlankFlag

                    When using the second example with the cartesian join relationship (X operator), the values in the fields used to define the relationship do not matter. You can actually set up a cartesian join relationship and then delete the fields specified as match fields for the relationship and the relationship still works. Try it and see.

                    Please note that this does not select an unassigned IPAddress record for the user, it only provides a list of all currently unassigned IP Addresses from which to choose.

                    Since it is the link between these two records requires entering the ID of the computer into the Computer field of the selected IPAddress table, you then have to script the process to find and update the selected IPAddress record with the new record's ID.

                    All of this assumes a design you may or may not have. That's why I asked a question in my last post which you still have not answered.

                    • 7. Re: Pop-up field for all records from table having "blank" for one field
                      boydcrow

                      Perhaps it was not clear when I said the "relationship between the tables is as described" referred to your description of the relationship and was the answer to your question.

                       

                      I did set up a second table occurrence of Computers and created a calculated field set to 1.  I linked this field to the cBlankFlag field in IP addresses.

                      However, when I set up the value list, I set it to use values from first field IPAddresses:IP Address and to display Computer as the second field.

                      I also specified it should "Include only related values starting from" [second table occurrence of Computers]

                      My pop-up field on the entry form uses "values from" that value list.

                      The problem is that the pop-up shows all the IP addresses, not just the flagged blank ones.  I can see records with computer names as well as those without in the display field.

                      I'm not seeing where only the value 1 for the constOne field is specified.

                      I've done extensive design and implementation of complex databases in MS Access but this is my first database design in FileMaker Pro. I have worked my way through the first half of "FileMaker Pro 11- the missing manual".

                      • 8. Re: Pop-up field for all records from table having "blank" for one field
                        philmodjunk

                        Perhaps it was not clear when I said the "relationship between the tables is as described" referred to your description of the relationship and was the answer to your question.

                        That post did not appear on my screen until after I posted mine...

                        You need a new occurence of IP addresses, not computers.

                        constOne would be a calculation field where you enter the constant 1 as its sole calculation term.

                        To be more clear, I should have used this example relationship:

                        Computers::constOne = BlankIPAddresses::cBlankFlag

                        The value list would list values from BlankIPAddresses, include only related records starting from Computers.

                        Since the computer field in IPAddresses is empty, there's no point in using the Computer field from this table as the second field for this value list.

                        Keep in mind that we have a situation that is inverted from the more typical setup. As I understand it, we are creating a new record in Computers and selecting an unused IPAddress for that new record, but instead of putting an ID number from the IPAddresses table in a field in Computers, we must find and update the selected IPAddress record with the ID from the new record in Computers.

                        That unusual situation that I've inferred from the fact that the match fields are named "computer" is why I asked for confirmation that this was indeed what you had in place. I see the logic to it as it makes it easy to see which IP Addresses are unused.

                        Updating the selected IPAddress record will require a script that would be performed after selecting an IP Address for the new record in computeres.

                        • 9. Re: Pop-up field for all records from table having "blank" for one field
                          boydcrow

                          Thank you.  I now have a pop-up listing only the unused IP addresses.

                          You have already been very helpful.  I was hoping you could explain why a second occurrence of the IP Addresses table was required rather than Computers.  My limited knowledge suggested additional table occurrences were only necessary to prevent more than one relationship being described between two tables.  I don't understand why it makes a difference which table is duplicated.  It is obviously an important distinction.

                          • 10. Re: Pop-up field for all records from table having "blank" for one field
                            philmodjunk

                            In FileMaker, nearly every reference to the data in a table is by reference to a layout. The layout, in turn, refers to a specific table occurrence in Show Records From in Layout setup. Since you are creating a new record on a layout based on an occurrence named "Computers", the computers table occurrence provides our initial frame of refrence, the "starting from" table occurrence for our conditional value list. In order for Filemaker to know what chain of table occurrences to evaluate in response to choosing "include only related values...", it starts from that occurrence and finds the occurrence specified as the source of values for the value list (BlankIPAddresses). The intervening table occurrences (if any), the match fields and their operators then determine what values appear in the value list.

                            It may help to think of it this way: If you can set up a portal that lists the values in the conditional value list correctly filtered by the relationship, then you have a relationship that will work for the conditional value list. The layout's table occurrence in Show Records From is the "starting from" table occurrence and the portal's table occurrence in Show Related Records From is the occurrence to use as the source of values for the value list.

                            If you haven't already read it, this thread may help shed more light on the subject of how table occurrences control the function of a Filemaker Pro database:  Tutorial: What are Table Occurrences?

                            • 11. Re: Pop-up field for all records from table having "blank" for one field
                              boydcrow

                              Thank you.  I do understand the purpose of table occurrences as described in the link you provided.  The point I had missed was that my layout was based on a table occurrence rather than a table.  Again, you have been most helpful.

                              • 12. Re: Pop-up field for all records from table having "blank" for one field
                                philmodjunk

                                FileMaker makes it easy for newbies to make that mistake. When you add a new table to your database file, FileMaker automatically creates a table occurrence and a layout with exactly the same name. While the names are identical, a layout, a table and a table occurrence are three distinctly different things and learning the differences and how they each function is key to getting the most out of FileMaker.