10 Replies Latest reply on Feb 14, 2013 8:44 AM by philmodjunk

    Totals for a field that is a list (totals for each item) - not using summary

    BenyPitrowskyJunior

      Title

      Totals for a field that is a list (totals for each item) - not using summary

      Post

           Gentlemen,

           I want to have a calculated field that will give me a total time for each vessel on downtime (Accumulated DT). This must be kept for each downtime separatelly and must be shown when searching for specific data range. This value will be used to calculate the operational period. So far, to have it working, I have the field set as summary.

           Please see attached pictures of the records for a better overview.

           Thanks,

           Beny.

            

      Screen_Shot_2013-02-13_at_4.26.40_PM.png

        • 1. Re: Totals for a field that is a list (totals for each item) - not using summary
          philmodjunk
               

                    So far, to have it working, I have the field set as summary.

               And what is the problem with using a summary field for this? (Telling us the reason helps us suggest an alternative that will work.)

               What version of FileMaker are you using?

          • 3. Re: Totals for a field that is a list (totals for each item) - not using summary
            BenyPitrowskyJunior

                 I'm using FM 11.

                 The problem is when I use find for opened records, for instance, and the vessel already had downtimes closed that will not show up on results, the records shows up not considering on the calculation the other records "closed" and the summary result is just for the ones "on the screen"... Did you understand what I'm trying to say? 

                 Thanks!

                  

            • 4. Re: Totals for a field that is a list (totals for each item) - not using summary
              philmodjunk

                   Yes, summary fields evalulate only from the records currenlty part of the found set when used in this fashion.

                   But if you set up a relationship that matches records by Vessel, (Do you have a Vessel ID field?), you can use the relationship to get a total based on all records for the same vessel and this will not require having all the records in the current found set.

                   I do not recommend that you use the vessel's name in place of a vessel ID field.

                   There are two ways you can set up that relationship:

                   1) Use a table of Vessels where you have just one record for each Vessel.

                   2) Set up a "self join" relationship that matches any given record in your existing table to all records in the same table with the same ID.

                   Option 1 gives you a good way to set up an auto-entered serial number field to use for your Vessel IDs.

              • 5. Re: Totals for a field that is a list (totals for each item) - not using summary
                BenyPitrowskyJunior

                     Sorry I'm really rookie on FM...

                     Please I'll need a breakdown explanation, if possible, or a link that could explain it...

                     And I didn't understand the option 1... 

                     Thanks!

                • 6. Re: Totals for a field that is a list (totals for each item) - not using summary
                  philmodjunk

                       My answer was very general because I do not know what tables you have defined in your database.

                       What tables do you have?

                       How are they related?

                  • 7. Re: Totals for a field that is a list (totals for each item) - not using summary
                    BenyPitrowskyJunior

                         I have only one table... "downtime database" with all the required information on it (see picture). 

                         No relationships... I never used it and I don't actually know how it works. As I told you, I'm rookie... =/

                          

                    • 8. Re: Totals for a field that is a list (totals for each item) - not using summary
                      philmodjunk

                           I know your new to it, but "how new" is an important factor to consider.

                           Start on the screen shown in your last post. Enter "Vessels" as the table name and click Create.

                           Click the Fields button an add two fields:

                           __pkVesselID

                           make ths a number field and click Create. While still the selected field definition, click the options button and use the auto-enter section of field options to specfiy that this field be an auto-entered serial number.

                           Add a text fields: VesselName. Use options to specify in the Validation section that this field is set for "unique values"; "validate always".

                           If you click the relationship button in the Manage | Database window, you'll find that a new Table Occurrence box has been added with Vessels as its name.  Drag from VesselName in vessels to the field in "downtime database" that stores the vessel's name. You have just created your first relationship in FileMaker.

                           Click Ok to exit Manage | Database. You'll find that FIleMaker has also added a new layout named "Vessels". If it is not already the current layout, select it from the drop down list of layout names in the status tool bar.

                           Select Import Records | File to start the import records process. Use the Dialogs that appear to select the very same file you already have open and select the "Downtime Database" table as the source table from which you will import records. Map the Vessel name fields in the two tables to each other in this same dialog and click Import.

                           A small dialog will pop up. In this dialog select the check box that enables auto-enter options and import your data. This copies all the vessel names fromm your existing table into the new Vessels table but filters out duplications so that you get one record for each Vessel.

                           You can now put the VesselName field on your new Vessels layout and add the summary field from Downtime Database to this same layout and it will show the total downtime hours, but as a subtotal for each vessel.

                           Each time you add a new vessel to your database, you will first add a new record to your Vessels table. And each new record in DownTimeDatabase will need to link to an existing record in Vessels--at this point, that's by name, but you want to change this to match by VesselID eventually.

                           Once you have this working, you can add a number field to your downtime database table, _fkVesselID and use Replace field contents to copy the value of __pkVesselID into it. THen you can update your relationship to match by these two ID fields instead of by vessel name. You can then remove the vessel name field from your downtime database layout and replace it with the name field from vessels. _fkVesselID can be formatted to be a drop down list for linking records in Downtime Database to Vessels or you can create new related records in Downtime Database from a portal to that table located on a Vessels layout.

                      • 9. Re: Totals for a field that is a list (totals for each item) - not using summary
                        BenyPitrowskyJunior

                             Thanks Phil!

                             I tried it and the problem is that it is importing only the first record from the other spreadsheet and only the vessel name. The important is to have it tracking as I showed in the very first picture on the field Accumulated downtime and the important is to show for each downtime generated, what was the accumulated at the time.

                             BRgds,

                              

                        • 10. Re: Totals for a field that is a list (totals for each item) - not using summary
                          philmodjunk
                               

                                    I tried it and the problem is that it is importing only the first record from the other spreadsheet and only the vessel name.

                               Make sure to do a Show All records on your original layout before importing. Make sure that your new layout is in list or table view when inspecting the results of your import.

                               This should import one record for each vessel name.

                               The vessel name is the only data that should be imported. The rest of the data will remain in your original table. But if you add the summary field you already have in the original table to this new layout, it will show the totals for each vessel.