3 Replies Latest reply on Jan 2, 2014 7:25 AM by philmodjunk

    returning null value fields



      returning null value fields



           I am a cabinetmaker. I created a database for the construction of cabinets my input fields are ID, Job Name, Type, Width, Height, Depth. all of my other fields are calculations. some fields do not return any data because of the differences in "type. my finds are always the same. I always use "Job" to return all of the necessary fields for each cabinet. I use several layouts in the form of reports to print different cutlists for each phase of construction. i.e. cabinet box const. or door size list, ect. my reports are showing all records from the specified job, whether or not there is any data. for some records, the only field with any data in it is the "job" field. I would like these to not show in my layout reports. can anybody help

        • 1. Re: returning null value fields

               Why would there be a record where you have a matching value in Job but all other fields are empty? That suggests an issue in how you create records in your database as the simplest solution is to not have such records in the first place.

               If you enter find mode and put an asterisk in a field, the resulting found set will only include records that have at least some data in that field, but from what you describe, I doubt that this would work for you as it appears that any one field might be blank while other fields may contain data that you need to see. To set up find criteria that omits a record if all fields except Job Name or ID are empty will be trickier to set up.

               One solution is to define a calculation field as: Field 1 & Field 2 & Field 3 & .... put in a reference to every field in this group where at least one must contain data. Then specify an * in this calculation field when performing your find. This can be scripted.

          • 2. Re: returning null value fields

                 My main table has many fields for each record. Each record represents one cabinet. I use layouts that are subsets of the main table. For example a "stack " cabinet does not have any doors as it  is a stack of drawers. Therefore on my layout report called "door sizes" there should no return at all for that cabinet. The layout is displayed after performing a find of the job name.  The return I get has empty lines for this type of cabinet except for the "job" field.  I would like to have no return at all for these records.

            • 3. Re: returning null value fields

                   It sounds like you are using individual fields where you should have a related table of records.

                   Here's an Example of what I think you should use:


                   Cabinets::__pkCabinetID = BOM::_fkCabinetID
                   Components::__pkComponentID = BOM::_fkComponentID

                   Where BOM stands for "bill of materials" and can list everything from "doors" to the finish to be used if you choose to set it up that way. If each cabinet is a custom design, then you may not need a components table.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   But that's a "long term" solution as it would take extensive re-working of your database design and carefully moving data from those fields into new, related BOM records--which can be scripted but still takes a bit of time to set up and test.

                   From your current design, I can think of two options, one is to perform a find where you place a lone = operator in each field of the set that will indicate that you want to exclude that record because all of the fields are blank. The other is to specify a series of requests--each specifying a different "cabinet type" in a field set up for that purpose so that your find only finds records of a "type" that should be part of your report.

                   Both options may be scripted. Here is a thread of scripted find examples that you may find helpful: Scripted Find Examples