1 2 Previous Next 20 Replies Latest reply on Jun 3, 2014 10:24 AM by philmodjunk

    Group select calculation

    HarryFisher

      Title

      Group select calculation

      Post

           I am new to Filemaker

           I have a database in progress as follows

           the goal is to record chemical spray applications on my farm fields.

           I have a table to farm fields , a table of chemical tank mixes, a products table  and a table of application dates and details.

           I have set up the mix table to give a name (eg BL1) to a mix of multiple products which each have a given rate of application eg 3Lts/Ha.

           On the application table I want to select a farm field (no problem) and a mix (BL1) and enter date of application and Ha sprayed (no problems)

           The problem is when calculating units used of each product how do I get a result for each line of the mix as each has a different application rate.

           What I want is Zinam 0.4 x 3.8=1.52

                                  Plenum  0.3 x 3.8= 1.14

                                  Bittesaltz 3 x 3.8= 11.4

            

           but as you see from the screen shots I don't as only first record is correct.

            

           If you can help thank you

            

           Harry

            

            

      Screenshot_%2813%29.png

        • 1. Re: Group select calculation
          HarryFisher
          /files/a6931cadcd/Screenshot_%2814%29.png 1280x1024
          • 2. Re: Group select calculation
            HarryFisher
            /files/391e94f61e/Screenshot_%2815%29.png 1280x1024
            • 3. Re: Group select calculation
              philmodjunk

                   This looks familiar, but please do not assume that I remember any design details from previous threads--I respond to way too many questions to rely on my memory for that.

                   If I understand the problem, in the last screen shot, the value in the units used column should be produced by the product of Ha (Hectares?) times Rate.

                   I can see by the leading :: characters,  that the values used in this calculation do not all come from the same table occurrence. Thus, I suspect the relationship linking these occurrences in relationships is producing your unexpected result.

                   To see if that is the case, I need to see:

                   a) the Tutorial: What are Table Occurrences? and relationships involved (a cropped screen shot of Manage | Database | Relationships may serve for that.)

                   b) the "context" selected in the top of the Specify Calculation dialog for each relevant calculation field (It looks like "units used" is the only such calculation field.)

              • 4. Re: Group select calculation
                HarryFisher

                     Hello again Phil

                     I enclose a screen shot of the Relationships

                      

                • 5. Re: Group select calculation
                  HarryFisher

                       And here is calculation for units used

                       the problem is that a mix may have many products all at different application rates.

                  • 6. Re: Group select calculation
                    philmodjunk

                         As a farm kid, I've helped apply chemicals once or twice.

                         Since the Mix will be applied at a constant rate, I must assume that "different rates for each chemical in the mix" is due to the fact that each chemical is mixed in a different concentration in that same mix of water and chemicals typically sprayed on a field.

                         I'm having trouble spotting which set of relationships document the chemicals that make up a specific mix. If I understand the process correctly, the "rate" for that chemical in that mix should be calculated in a specific table in that relationship, but I can't pick out what I'm looking for which would look like this:

                         Chemicals (Product?)------<Chemical_Mix>--------Mixes

                         This is a many to many relationship (A chemical can be used in many mixes a mix can combine many chemicals) and the rate calculation would be defined in the Chemical_Mix table--the join table between the table of chemicals and the table of mixes.

                    • 7. Re: Group select calculation
                      HarryFisher

                           Phil

                           You are correct in that the mix (water plus all chemicals added) will be applied at a constant rate in this case 200lts/Ha.

                           Each chemical in the mix will have a different concentration rate expressed as units/ha (eg 0.4lts/Ha)

                           rather that select each chemical every time I would like to choose a mix with pre determined chemicals and associated units/Ha. 

                           After applying this mix to specific farm fields I need to calculate how much of each chemical has been used for stock control and costing.

                           The Rec table is where the mix is named and defined by using a portal to the Mix table.

                           The relationships are shown in blue on screenshot

                           H

                      • 8. Re: Group select calculation
                        HarryFisher

                             This is the layout design for Rec table and Mix Portal

                        • 9. Re: Group select calculation
                          philmodjunk

                               Correction on what I last posted, the rate would be documented in the join table used to list the chemicals that make up a particular tank mix.

                               In your relationships that you've highlighted for me, in which table does a single record represent one "mix"?

                               Am I correct that "Product" = "Chemical"?

                               And the match fields don't look correct for what should be used for such a set of relationships. I don't see any reason why a field named "QuarterUnitPrice" should be used as a match field here.

                          • 10. Re: Group select calculation
                            HarryFisher

                                 You are correct Product= Chemical

                                 the single record representing one mix would be the field KP_RecID from the Rec Table (Rec short for Recommendation) with a more user friendly "Description" field also on the layout.

                                 You are also correct Quarter unit price was to be used for costing but I am probably jumping ahead so ignor that for now

                                 Thanks

                                 H

                                  

                            • 11. Re: Group select calculation
                              philmodjunk

                                   Hmm, so is each "mix" record a one time use record? (You start a new record for each application of chemicals?) or will you link different fields to the same "mix" record where the only difference might be in the volume of the mix produced? Or you fill the Tank and then apply it to several fields from one tank?

                                   The difference can be in what kind of relationship results when we link a "mix" to a field in order to use the area in hectares for that field size to compute the desired application quantities for each chemical used in the mix.

                                   The reason that I'm wondering about whether this Mix record describes an application for one field or several fields is the "task no" and "job no" fields I see listed in it.

                                   

                                        You are also correct Quarter unit price was to be used for costing but I am probably jumping ahead so ignore that for now

                                   More importantly, it should not be a match field in this relationship. You can use an auto-enter setting on the Mix_Tbl::Unit Price field top copy over the current price from Products without trying to use it as a match field in the relationship.

                              • 12. Re: Group select calculation
                                HarryFisher

                                     I will want to use each mix on several different farm fields from one tank.

                                     Each "application" will  record Ha of farm field, date of application etc.

                                     The Agronomist gives me a recommendation sheet which has a unique "task number" pre printed at the top, this sheet may have 2 or 3 "jobs" listed on it by using the Task number and Job number I can trace the application back to his recommendation.

                                     I need a record of each product applied to each farm field traced back to a recommendation.

                                     I take your point about the price and think I can do this as you have suggested.

                                     Many Thanks

                                     H

                                • 13. Re: Group select calculation
                                  philmodjunk
                                       

                                            Each "application" will  record Ha of farm field, date of application etc.

                                       Since each application record links to a Fields table where you have already recorded the Ha, there is no need to make a duplicate copy of this value in the application record.

                                       The solution towards which I am groping is to link each record in your mix's join table to the correct Ha in fields. You do not currently have the needed relationships to support that calculation and crafting the needed relationship to do so will not be simple and may even need a script to pull off here. (I'm still mulling over different options.)

                                       Are you using FileMaker 12 or newer? This is a case where ExecuteSQL might serve to greatly simplify the needed design changes to support this calculation.

                                  • 14. Re: Group select calculation
                                    HarryFisher

                                         Phil

                                         Sorry to be a pain but application Ha may well be different to Field Ha. Part fields are often sprayed. If for example a field has 2 different varieties of potatoes then a mix could be applied to just the one variety and therefore a smaller Ha than the total Ha. Or it may be that only half the field is sprayed on one day as the tank runs out and the rest a few days later after rain stops the operation.

                                         I can achieve the desired results with MS Access but have to enter each product(Chemical) together with its rate\ha, which on a 6 way mix on 10 different farm fields means 60 records !

                                         I am using FileMaker 12                           Thanks H

                                          

                                    1 2 Previous Next