2 Replies Latest reply on Nov 13, 2014 8:29 PM by philmodjunk

    Creating a count based off two fields



      Creating a count based off two fields



      I am a complete novice at filemaker so forgive me if I am a little unclear. I am creating an office inventory system with individual records for each item. What I would like is a box showing the current number of that item we have in the office. Location is determined by a dropdown box which also contains the location name Office - Faulty. I would like only to count records which have the same name as the current record being browsed and have the location as Office omitting office -faulty - essentially creating a box which automatically updates to show current number of that item in stock which are usable.

      Is this possible? I don't really know where to start.

        • 1. Re: Creating a count based off two fields

          Would it maybe be easier to create a separate table with Each Item and its location and the number in stock then create a portal on the first layout so when that field is modified on one record it changes on all others for that item name?

          • 2. Re: Creating a count based off two fields

            Separate tables would not be a good idea. But you can set up a self join relationship that matches to all records with the same value in the item ID and location fields. Then a calculation field can use the Sum or count function to give you the total on hand in that location.

            A self join uses duplicate table occurrence boxes, Open manage|Database|relationships, select the box for your inventory table and click the duplicate button (two green plus signs) to make a duplicate table occurrence. You can then drag from the Item ID and location fields in one table occurrence box to the same field in the other.