9 Replies Latest reply on Jun 19, 2015 9:35 AM by philmodjunk

    Count of Related Record

    FM_DM

      Title

      Count of Related Record

      Post

      Hi

      I am very new at filemaker and would like some help with my database. I have attached an image of the basic Item record detail that most of the work is done to this database. This is the 'home' screen of the database.

      When a New item is added to the database the item information represented via TEST is this image is added via a the Item ID. The information is held in a separate Items database and the tables are related by Item ID. Location is determined via  dropdown list and is individual to each record. 

      What I would like to do is have the Amount in office field to be automatically updated as I change the location on individual records. 

      For instance if I create a new record for a specific type pf latch I would like the amount in office field to automatically update to add one to the current total.  If I were to give out a latch to an engineer and change the location to offsite I would like the amount to go down by one. 

      How would I be able to achieve this. I have attempted a number of different ways but I think I may be stumbling blindly in the dark here .

      Thanks

      databae.PNG

        • 1. Re: Count of Related Record
          philmodjunk

          I'm a bit in the dark about what you want. What I see looks like  record for a table where each item is entered into a separate record. Then I see a portal used for unknown purposes.

          You want some kind of count to appear and automatically update in the "In office" field. But are you counting all records with the same item ID but with "Office" as the location? Are you counting all records in the same category with "office" as the location? Or all records of all types with "Office" as the location?

          • 2. Re: Count of Related Record
            FM_DM

            Hi

            sorry the portal shows the history of each item e.g. when and who booked it out or in. This is unrelated to the count. I want to count all items with the same Item Id with the location set as office. essentially I would like to know how many of each item there is in the office. So If I looked at one type of latch the field would show how many of that particular type of latch I have in the location Office.

             

            I have attached a capture of the relationships in for this table currently. As you can see it's a little confused!

            • 3. Re: Count of Related Record
              philmodjunk

              There are some problems in your set up and how you've named a field, but they aren't central to the current problem. I must assume that the layout shown is based on Inventory.

              An unstored calculation with this expression can return the desired count of items of the same id that are "in office".

              ExecuteSQL ( "SELECT Count ( k_1 ) FROM Inventory WHERE location = 'office' AND /"_kp_item_id/" = ?" ; "" ; "" ; Inventory::_kp_item_id )

              This requires FileMaker 12 or newer.

              If you don't want to or can't use SQL, you can set up a self join relationship and a filtered portal to get the same count:

              Add a second occurrence of Inventory and link it to the first occurrence of Inventory with these match fields:

              Inventory::_kp_item_id = Inventory 2::_kp_item_id

              Define a "count of " summary field in Inventory that counts any never empty field in your table. Put a one row portal to Inventory 2 on your layout. Give it this portal filter expression: Inventory 2::Location = "office". Put the summary field just described here into the portal row and it will show the total items in office if any records of the same id as the current record are logged with "office" as the location.

              Other methods are also possible.

              Other observations:

              The relationship between Inventory and count test makes no sense as it matches completely different fields. An item id and a location are unlikely to ever match.

              At least one relationship to inventory from the right hand side has too many match fields. Both Items and InventoryByCatalog should only match to records in Inventory by _kp_item_id. I can see other match fields in your screen shot to one or both of these table occurrence boxes that should not be there.

              "kp" is a fairly standard labeling convention for something called a "primary key". A primary key uniquely identifies each record in a table. Items::_kp_item_id is such a primary key judging by your screen shot and thus is correctly named. Inventory::_kp_Item_Id does NOT uniquely identify records in the Inventory name and thus this name can cause confusion. Such a field would more typically be named: _kf_item_id where the "f" refers to "foreign key". A foreign key is a field that stores the value of the primary key from another table as appears to be the case here.

              • 4. Re: Count of Related Record
                FM_DM

                Thanks for your help, The database was originally constructed using a starter solution and I was dropped into the deep end a little and had very little Idea what Tables related to what which is the reason for the confusing relationships.

                I am running Filemaker Pro 11 so sadly no use of ExecuteSQL

                I have attempted the way that you have suggested but it keeps throwing out very large seemingly random numbers? I have defined the count of summery field in inventory to count the K_ID_Inventory number which is present on all records as an individual identifier for each. I have used the filer expression suggested also.

                I understand what you have said regarding my relationships. I have streamlined a little and renamed the kp_Item_Id in the inventory to kf to avoid confusion later on. Count test was originally a second of inventory which I was using on one of my attempts to figure this out myself. Here is a picture of my new relationships. 

                As you can probably tell my knowledge is extremely limited and I am learning as I go. Your help is greatly appreciated! 

                • 5. Re: Count of Related Record
                  philmodjunk

                  And what did you set up as your portal? What portal filter expression did you use?

                  The nonSQL method that I describe requires placing a summary field inside a filtered portal. The portal can be a single row portal and have alls borders, fill colors set to none so that all that is visible is the summary field.

                  • 6. Re: Count of Related Record
                    FM_DM

                    I set up the portal to 'Inventory 2'. The only field in the portal is the summery field from 'Inventory' named CountOf. I filtered the portal with the expression Inventory 2::Location - "office"

                     

                    • 7. Re: Count of Related Record
                      philmodjunk

                      The summary field needs to be from Inventory 2 not Inventory.

                      Perhaps it's a typo in your post, but the portal filter expression needs to be:

                      Inventory 2::Location = "office"

                      Make sure that location is a field of type text and that "office" is the exact text (but not case sensitive) in your field when an office location is specified.

                      • 8. Re: Count of Related Record
                        FM_DM

                        Yes I had put the wrong summery field in :) works perfectly now thank you for your help. Is there a way I can get a 0 to show when no records are found in office?

                        • 9. Re: Count of Related Record
                          philmodjunk

                          Not easily with this method. You can try putting a 0 in the portal row on top of the summary field. Then use a conditional format expression to set the font size to 120+ points (for text with with a normal font size of 10-12...) when the summary field is empty.

                          This works well under other circumstances, but I'm not sure that it will work within the context of a filtered portal.

                          We could modify the relationship to not need the portal filter by adding a calculation field to the inventory table: constOffice with just "Office" in quotes as its expression.

                          Then the relationship becomes:

                          Inventory::ItemID = Inventory|SameItem::ItemID AND
                          Inventory::constOffice = Inventory|SameItem::Location

                          (I didn't try to exactly reproduce your field and table occurrence names here, but I think you'll get the idea.)

                          Then you can add a calculation field to Inventory defined as Count ( Inventory|SameItem::ItemID ) + 0 with "do not evaluate if all referenced fields" cleared to get a field that will show a zero when there are no related records to count.