1 2 Previous Next 19 Replies Latest reply on Jun 12, 2014 10:57 AM by sjpockmire

    Sub Total and Grand Total in List

    sjpockmire

      Title

      Sub Total and Grand Total in List

      Post

           I am working on an inventory program for a historical association with five house-museums. There are three tables - Inventory, DateLocationValue, and Photographs. The parent table is Inventory. The child table is DateLocationValue. The date, location and value are updated every couple of years but not all inventory items are updated. My question is, when I do a list I can't seem to get it to total the value of the items for each house. If I use a layout with the Inventory table, the most recent record shows in the list but I can't get it to total. If I use a layout based on the DateLocationValue table, it shows all the records, not just the most recent and totals all the past and present records. 
            
           How can I have it pull the most recent date/location/value record and total the value?
            
           Thanks for any help you can give.

        • 1. Re: Sub Total and Grand Total in List
          philmodjunk

               It would appear that you have this relationship:

               Inventory----<DateLocationValue

               Inventory::__pkInventoryID = DateLocationValue::_fkInventoryID

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

               Your field names are likely different from mine, but the one to many relationship should still hold.

               I would gather that you want a count of how many inventory items are currently at each location with a grand total count of all items in inventory in all locations. Correct?

               If you have not already done so, go to Manage | Database | Relationships and double click the line connecting Inventory to DateLocationValue. Click the Sort button on the DateLocationValue side and specify that the records in DateLocationValue be sorted in descending order. This makes the most recent location for a given Inventory record the first related record linked to it.

               In your inventory table, define a field, sItemCount of type Summary and set it up to "count" the __pkInventoryID field. (Any field that is never empty can be used.)

               Set up a list view layout based on the Inventory layout, but add a sub summary layout part and specify "when sorted by" the location field in LocationDateValue. put sItemCount into this sub summary layout part. This field will show the count of all items in a given location.

               Put this same sItemCount field into the header, footer or a Grand Summary layout part and it will compute a grand total count of all inventory items in your current found set.

               Note: You must sort your records by the "when sorted by" field specified for the sub summary layout part or this layout part will not be visible on your layout.

               For more on summary reports, see: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Sub Total and Grand Total in List
            sjpockmire

                 Thank you for your help! I understand what you are saying but instead of a count, I need a total value for all the items in each house which is used for insurance purposes. Every few years, they take an inventory of some of the items in the houses, check their location, and give an updated value to the item. Not all items are re-inventoried. Some items move from one house-museum to another. Also some items might have a value dated this year, and others valued in past years. What I need to do is pull the most recent inventory date and do a list of all the inventory in a house with a total value of all the items. I understand sub-summaries and grand totals, but can't seem to figure out how to pull the most recent record, include it in the list and total it. There must be some calculation or script I need to do? Again, thank you so much for any help.

            • 3. Re: Sub Total and Grand Total in List
              philmodjunk

                   Define a calculation field in Inventory, cCurrentValue and define it as:

                   LocationValueDate::Value

                   Now you can set up a summary field to total and sub total it.

              • 4. Re: Sub Total and Grand Total in List
                sjpockmire

                     Yay! Thank you so much! You know, I've been using Filemaker since it was made by Claris and that has been a long time. Unfortunately I stopped with FM 5 and FM 6 so I'm having to learn this all over again using tables and portals. I now use FM 13 and diligently go on Lynda.com for a couple hours each day watching their videos. One upcoming video deals with only scripts and calculations so I'm looking forward to studying it. As a volunteer, doing this database is great way to help the local historical association get a digital list of their inventory, which up until now is on thousands of little accession sheets. So I can't thank you enough for your help and advice. This calculation will help with many future layouts!

                • 5. Re: Sub Total and Grand Total in List
                  sjpockmire

                       Uh oh. I set up a new table and accidentally deleted the records with the most current inventory date. I have two dates… one from 2000 and the other 2011. Now, the same layout that worked yesterday no longer is pulling the most recent inventory date and value. Any ideas?

                  • 6. Re: Sub Total and Grand Total in List
                    philmodjunk

                         Do you have a back up copy? One should always make and keep multiple back up copies of your file. If you have a back up, you can import the deleted data from your back up copy.

                    • 7. Re: Sub Total and Grand Total in List
                      sjpockmire

                           Hi Phil, yes I have the program set to ask to back up. I imported the files and it works but what is the clincher is that I have the date that the inventory was taken next to the value. The most current value is listed but not the corresponding date. I tried the same calculation but with calculation result as "date"  but it isn't pulling in the most recent date. Perhaps there is a different calculation?

                      • 8. Re: Sub Total and Grand Total in List
                        sjpockmire

                             What started all this is I created another table that lists the details of the inventory date -

                             PKInventoryID, CatalogedBy, CatalogContact, and CatalogDate

                             I created a FKInventoryID in the Location Table that pulls the CatalogDate

                             Hmm, I'm thinking that I need to use the FKInventoryID in that calculation next to the value… instead of the CatalogDate from another file. If that is the case, if I have a number, 101811 which represents 10/18/2011, how do I make that show as a date? Any suggestions?

                        • 9. Re: Sub Total and Grand Total in List
                          philmodjunk

                               This shouldn't be the case. Are the fields that store the dates of type date. Did you specify a sort order for the relationship?

                          • 10. Re: Sub Total and Grand Total in List
                            sjpockmire

                                 Hi, by writing this down in this post and thinking it through, it did work. I pulled the FKInventoryID from the Location Table which does make sense. However I need to convert the ID to a date since that is how the ID number is set - i.e. 010100 is 01/01/2000 and 101811 is 10/18/2011. I've tried some of the calculations but haven't nailed it yet.

                            • 11. Re: Sub Total and Grand Total in List
                              philmodjunk

                                   You last post popped up when I posted mine. Is the issue that you are seeing in an original copy of the file or just this new version?

                              • 12. Re: Sub Total and Grand Total in List
                                sjpockmire

                                     I didn't have the date showing in the previous version - just the value. I thought it would be important to see when the last inventory was taken and the value placed on the item.

                                • 13. Re: Sub Total and Grand Total in List
                                  philmodjunk

                                       But, assuming that the date is in the same table as the value field, can you take that previous design and just add the date field to the layout and see the correct date?

                                       I'm trying to distinguish between issues created by accidentally deleting and restoring your data from issues created by the design changes you want to make to your database.

                                  • 14. Re: Sub Total and Grand Total in List
                                    sjpockmire

                                         Hi, the date is only in the Inventory Table and only shows in the Location Table when the PK and FK InventoryID matches.

                                    1 2 Previous Next