8 Replies Latest reply on Aug 2, 2012 8:36 AM by philmodjunk

    A list without blanks



      A list without blanks



      This question has probably been answered several times – however, I am unable to find it.

      On a separate layout, I am wanting to create a list of number fileds (ei: drawing fees, project duration, dumpster fees, etc…), and some of the fields will be blank (value of $0.00).  I am wanting to create a list of the items that have a value greater than 0.00.

      Any help would be greatly appreciated.

        • 1. Re: A list without blanks

          Having the fields set to auto enter 0.00 would be easiest. You want to list greater than 0, not greater than blank or empty.

          • 2. Re: A list without blanks

            To find a record where a number field is greater than zero, enter find mode and use >0 as the criteria.

            • 3. Re: A list without blanks

              Thank you both for your help.  However, I should have been more specific in my post.  I want to create a field containing this list.


              Field 1 contains $45.00

              Field 2 contains $0.00

              Field 3 contains $235.00

              And so on…

              When viewed in browse mode, only the items with an amount would be displayed (Filed 1 and Field 3).

              Again, I apologize for not being more specific.

              • 4. Re: A list without blanks

                Why do you need it in a field?

                A filtered portal would be easier to set up--though this assumes that instead of Field1, field2, field3, you have a table of related records with one record for each field--often a much more flexible structure for your data.

                List ( If ( Field1>0 ; Field1 ) ; If (Field2>0 ; Field2 ) ; If ( Field3>0 ; Field3 ) )

                Would put it all in one field with fields that are empty, 0 or negative omitted.

                • 5. Re: A list without blanks

                  I tried setting up the portal, but I am confused as to the layout of the fileds.

                  • 6. Re: A list without blanks

                    All of the individual fields in this list would be replaced by a single field in the related table. You'd then need a relationship that matches your current record in your current table to just these related records so that a portal lists them and no others. This is usually done by setting up a Number field as an auto-entered serial number field so that each record in your original table has it's own unique identifier. This field is then linked to a number field (one NOT set up to auto-enter a serial number) in the related table.

                    It might look like this, but with your table and field names substituted for mine:

                    YourTable::__pkYourTableID = RelatedTable::_fkYourTableID

                    Often, you double click the relationship line linking these two tables in Manage | Database | Relationships and enable "allow creation of records via this relationship" for the RelatedTable. This option enables you to add new related records in your portal simply by entering data into the blank "add" row of the portal.

                    Using a portal like this assumes a number of design details that may not apply to your database.

                    • 7. Re: A list without blanks

                      I created the portal/filter as you described and it appears to be what I needed.  However, I noticed the portal does not reduce in size to fit only the items listed.  How do I reduce the portal size?

                      • 8. Re: A list without blanks

                        If you want the portal to reduce down to just the portal rows needed to display the data when printed, previewed, or saved as a PDF, select the portal and use the sliding and visibility settings on the inspector's position tab to specify that it "slide up" and "resize enclosing part".

                        Key facts about sliding layout objects:

                        1. It's only visible in preview mode and when you print/save as PDF...
                        2. Sliding fields will shrink but not expand.
                        3. All layout objects below and in the same layout part as the slide/resize field need to also be set to slide up and resize.
                        4. Objects in headers and footers will not slide.
                        5. Portals will shrink/slide to fit the number of rows of records, but fields within the portal row will not shrink/slide.
                        6. Fields will slide up only if Top, alignment is specified for it and will slide left only if left alignment is specified.
                        7. Consistent side borders are difficult to achieve with sliding fields.