6 Replies Latest reply on Apr 18, 2012 2:04 PM by SB_1

    Displaying a count of records that have a certain value in an unrelated table

    SB_1

      Title

      Displaying a count of records that have a certain value in an unrelated table

      Post

      Hi.

      I have a database with the main table "Subjects". This table contains all the information for the participants on our trial. This is linked to many other tables that contain the data for each subject at many visits.

      I have 4 unrelated tables that contain information about stock levels for 4 different allocations of product. 

      Each 4 unrelated table relates to canisters or boxes of product we have had manufactured. 

      Tables : CanType1, CanType0, BoxType1, BoxType0

      Our manufacturer is supplying us with cans and boxes as we go so we dont have a large stockpile. 


      From a layout based on the subjects table I need to see how many CanType1, CanType0, BoxType1 and BoxType 0 we have in stock. There is a field in each 4 tables that contain a 1 if that can/box is unavailabe (used or not yet recieved) or a 0 if it is in stock and available for allocation.

      All i need is a count of how many of each CanType1, CanType0, BoxType1, BoxType0 are in stock (have a 0 in the stock field) on a layout based on the Subjects table. 

      Any ideas?

      Please let me know if you require more clarification. It is rather complex to describe our situation.

      Thanks

        • 1. Re: Displaying a count of records that have a certain value in an unrelated table
          philmodjunk

          I would use one table with 4 types of records, not four separate tables. This approach requires that you add a new table each time you add a new product--that can get really unwieldy over time.

          The key phrase here is "unrelated". The tables need to be related so that you can access the data in them. You can use a relationship like this:

          YourMainTable::anyfield X CanType1::anyfield

          Then a calculation field in YourMainTable defined as Sum ( canType1::Qty ) will tell you the total of the Qty field for all the records in that table.

          • 2. Re: Displaying a count of records that have a certain value in an unrelated table
            SB_1

            Thanks for the response.

            I should of explained further that it is for a clinical trial so we only have 4 different things we give people (Can type 0, Can type 1, Box type 0, box type 1). 

            Each piece we have in stock has its own label. We have 720 different labels for can type 0 and can type 1. There are 7500 different labels for box type 0 and box type 1.

            Because of this we have the 4 tables CanType1(360 records), CanType0(360 records), BoxType1(3750 records), BoxType0(3750 records).

            Each record records if the label is in stock so the quantity is either a 1 (not in stock/allocated) or 0 (Available for allocation). I should really switch this arround.

            By relating the Subjects table and CanType0, would say record 5 from the subjects table and record 5 from the CanType0 table become related. Because person number 5 in the Subjects table most likely will not recieve can 5 from the CanType0 table.

            • 3. Re: Displaying a count of records that have a certain value in an unrelated table
              philmodjunk

              I'd still use a single table as it makes for a simpler, more flexible relational structure. (And though you can only give them 4 different things now, can you absolutely guarantee that won't change in the future?) Even if you always can work within those 4 types, reporting summary reports of all 4 types is easier with a single table. Please note that my suggestion assumes that you stick with the current setup. If you decide to change, a different approach is needed to produce your counts. Reversing the value in the Qty field would be an excellent idea.

              Replace Field Contents using the calculation option can do this very easily. You can use: Not Qty and it will change your 1's to 0's and vice versa.

              Using the relationship with the cartesian join operator, you are matching any record in Subjects to all records in the other table. This is what makes it possible to compute a summary of all records in the table no matter which subjects record is "current" on your layout.

              • 4. Re: Displaying a count of records that have a certain value in an unrelated table
                SB_1

                I have setup the join Subjects::CountType0 X CanType0::Qty

                CountType0 and Qty are both number fields.

                I set Subjects::CountType0 to a calculated value "Sum ( CanType0::Qty )".

                I un-checked "Do not replace existing value of field".

                 

                However, the calculation doesn't seem to be working. Subjects::CountType0 remains blank even when I go change quatities in the CanType0 table.

                 

                Any suggestions?

                • 5. Re: Displaying a count of records that have a certain value in an unrelated table
                  philmodjunk

                  I have setup the join Subjects::CountType0 X CanType0::Qty

                  CountType0 and Qty are both number fields.

                  it doesn't matter what fields you use in that relationship. You can even define "dummy fields" use them to create the relationship and then delete them and a relationship using the X operator still works.

                  You cannot use a number field with an auto-enter calculation for this. Auto-enter calculations do not automatically update when data in a field from a related table is updated.

                  Define a field of type calculation and put your sum funciton into that calculation. This unstored calculation will update each time you make changes to records in the related table.

                  • 6. Re: Displaying a count of records that have a certain value in an unrelated table
                    SB_1

                    Thanks for your help. That fixed it. It had been awhile since I have used filemaker and I had forgotten to make it a calculation field instead of a number field.