6 Replies Latest reply on Jan 10, 2013 4:33 PM by david583

    Calculate yearly usage on layout

    david583

      Title

      Calculate yearly usage on layout

      Post

           FM Pro10 & 11, Server 11. Network shared Windows system, XP & Vista. 10 users

           I am trying to work out parts usage over the year for individual parts.

           I have a 'Parts_Inventory' table and a 'Job_Card' table, joined by a 'Parts_Usage' table. Part Number and Job Card Number are the foriegn keys in the usage table.

           What I would like to be able to do is display for each part number in my Inventory layout, the quantity used so far this year and in another field, the quantity used total last year.

           Can this be done with live calculations rather than having to produce a grouped report?

        • 1. Re: Calculate yearly usage on layout
          philmodjunk

               Yes. If you have date fields that record the date. Do you have such fields? defined in which table(s)?

               Note that you can define calculation fields that use the year function to extract the year to match in a relationship to a number field in which you enter/select a year to reference groups of records all dated for the specified year.

          • 2. Re: Calculate yearly usage on layout
            david583

                 Yes, I do have date fields. In the Parts_Usage table there is a date for each transaction. I have also got the calculated year field you describe in the same table taken from the transaction date. I was using it to group a report layout.

                 The Job_Card table has reported date, start date and end date for each job, and the Parts_Inventory has the date last updated.

                 What would the relationship look like? Ideally it would display on the inventory layout using data from the usage table.

            • 3. Re: Calculate yearly usage on layout
              philmodjunk

                   If you are going to display the yearly totals for usage records on a layout based on a usage layout, why not use a 'grouped report'?

                   I need to know why that won't work for you before I can suggest an alternative approach (and there are several options possible).

                   What do you need to see on the screen?

              • 4. Re: Calculate yearly usage on layout
                david583

                     I already have a grouped report (thanks to your help previously). What we were aiming at is on the parts inventory layout rather than the usage report.

                     We would not only like to see the actual quantity held, but also quantity used and yearly average for each item in it's detailed (form) view.

                     It may seem a little redundant, but I have a couple of users that wouldn't look at the whole report, too much info, they were only interested in one or two items usage at a time and couldn't understand why it wasn't provided live on their layout. My theory is to make this as user friendly as possible to encourage proper use, this is pandering to their 'wants' as well as their needs at times.

                • 5. Re: Calculate yearly usage on layout
                  philmodjunk

                       We would not only like to see the actual quantity held, but also quantity used and yearly average for each item in it's detailed (form) view.

                       This is what I was looking for. Correct me if I am wrong, but this means:

                       a) a layout based on the Parts_Inventory table

                       b) you want a yearly average for the current year. or

                       c) you want a yearly average for a year selected by the user....

                       Both b and c use nearly the same relationship, but the type of field you use in Parts_Inventory will differ depending on which option you want to implement.

                       Parts_Inventory::PartNumber = Parts_UsageByYear::PartNumber AND
                       Parts_Inventory::cYear = Parts_UsageByYear::cYear

                       set's up a relationship that matches to all records in Parts_Usage with the current Parts_Inventory record that have the same part number and the same value in cYear in both tables. Parts_UsageByYear is a new occurrence of your Parts_Usage table.

                       To match automatically to all usage records for the current year, define cYeas as an unstored calculation: Year ( Get ( CurrentDate ) ). This field must be Unstored or it will fail to change come the new year.

                       To match to all usage records for a specified year, replace Parts_Inventory::cYear with a simple number field which I would name Year. (I preface calculation fields with a lower case c...)

                       Please note that both options are not mutually exclusive. you can set up two different occurrences of PartsUsage to get both versions of this relationship.

                       Aggregate values from this set of related records can be computed two different ways:

                       1) aggregate function calculation fields can be defined in Parts_Inventory to compute the sum, count, average... of a field in the set of related usage records.

                       2) a summary field in the usage table can be referenced from the context of Parts Usage. You can put Parts_UsageByYear::SummaryField on your layout to show the total, average, count, etc based on that set of related records. A calculation field defined in Part Inventory can also refer to this same field.

                       Experience has shown that 2 usually reduces the number of fields that you need in your tables, but that 1 refreshes more smoothly when you need to show a calculated aggregate value on a layout where the user can edit data that will change the computed value. (Say you have a portal to the usage table and the user can add/create/change records. If that's the case, I prefer to use the aggregate functions instead of summary fields as they update more smoothly.)

                  • 6. Re: Calculate yearly usage on layout
                    david583

                         PhilModJunk, you are a legend!

                         I am going to try both 1 & 2, I think 1 will be perfect

                         And yes, you assumed correctly on a) & b) and I will also give them c) as that seems a logical progression.

                         Also want average for year previous but I can see how that can be done easily now by changing the cYear to cYear-1.

                         Once again, thank you for your invaluable help yes