5 Replies Latest reply on Jan 25, 2010 8:30 AM by philmodjunk

    Finding field values



      Finding field values


      I have a database that lists Air Handler air filter sizes and their quantities. The fields are Filter size 1, Filter size 2 and Filter size 3. The layout is arranged in list fashion so you have from left to right, Unit #, Location, Filter #1, Filter #1 Quantity, Filter #2, Filter #2 Quantity, Filter #3, Filter #3 Quantity.


      The values in the Filter fields are all sizes of the various air filters used and the number fields are for the quantities used by a particular air handler, for instance, Air Handler Unit #1 uses 2 20X80 Link filters, so the Filter #1 value is 20X80 and the Filter #1 quantity is 2. Air Handler Units can have up to 3 different filter sizes, hence the Filter Size 1, 2 and 3.


      What I'd like to do is generate a list of unique filter sizes and then the total quantity needed for each size in the database. It seems like a simple problem, but I've been unsuccessful finding a satisfactory solution yet.

        • 1. Re: Finding field values
             Qty of 1 10X80 Link filters ( ProductID 1),

          You should really have the filter information in a seperate related table each with its own record like a line item table.



          For example:

          Lets say that you have a table for the unit information, a table for the parts, and a table that stores what filters each unit is assigned.

          You would store the record ID number for whichever unit and then the part ID in each seperate record.


          Table: Unit

          pkUnitID             Name             

          1                     Airhandler 1

          2                     Airhandler Whatever

          3                     Airhandler Blah


          Table: Parts

          pkPartID             Name             

          1                     10X80 Link filters

          2                     20X80 Link filters

          3                     30X50 Link filters

          4                     45X45 Link filters



          Table: Assigned

          fkUnitID             fkProductID              Qty

          1                           1                       2

          2                           1                       1

          2                           2                       1

          2                           4                       2



          So for this example,  Airhandler 1 ( UnitID 1 ) has a Qty of 2 10X80 Link filters ( ProductID 1) and Airhandler 2 ( UnitID 2 ) has three filters with a Qty of 1 10X80 Link filters ( ProductID 1), Qty of 1 20X80 Link filters ( ProductID 2), Qty of 2 45X45 Link filters ( ProductID 4).


          You can use a portal to display the line items and even enter items in ( turn on allow creation or record in the relationship ).



          Finally, if you still get lost, look for an invoicing demo referred on these forums produced by the user: comment

          • 2. Re: Finding field values

            Oh, how I wish that were so. Unfortunately I 'inherited' this database and such relationships don't exist, in fact, I think it started life as a FileMaker Pro 4 or 5 file. I may decide to spend some time on it and lay it out as you have suggested... I just have to weigh whether it's worth the time or not.


            Thank you very much for your advice! 

            • 3. Re: Finding field values

              To bad you can't climb into time machine and give your system's creator a dozen lashes with a wet noodle! :smileywink:


              Even back in FMP 4 or 5 days Mr. Vodka's suggested solution would have been the best way to go.

              • 4. Re: Finding field values



                Although I concur regarding the "wet noodle" approach and with Mr Vodka's solution it must be said that if the data is intact in some fashion, it can be manipulated, relationships reassigned, imports to tables executed . . . etc. That said, I'm not the guy staying up all night to make this happen.



                • 5. Re: Finding field values

                  "...if the data is intact in some fashion, it can be manipulated, relationships reassigned, imports to tables executed . . . etc. That said, I'm not the guy staying up all night to make this happen."

                  You certainly can manipulate the data to get what you want, but doing it without a significant investment in a complex combination of scripts and a new table or two? Probably not, and if you're going to have to put that much effort into it, a importing your existing data into a new table structure might not require that much more time/effort anyway and leaves you with a much better database structure.


                  Using the existing structure, I think you're looking at a script that walks through each field and computes counts of each type of filter. That suggests using the script to load a table like Mr. Vodka has described and that both gives you your counts and completes a key part of restructuring your tables.