1 2 Previous Next 15 Replies Latest reply on Mar 14, 2014 8:12 AM by philmodjunk

    Combining Fields

    KM

      Title

      Combining Fields

      Post

           Hello,

            

           I am working in FM Advanced on a chemical database but and finding that I have so many fields.  Most of them are text fields or are calculation fields.  Is there another way for me to achieve the same calculations without having so many fields?

        • 1. Re: Combining Fields
          philmodjunk

               That would depend on what you need to do with your database. And how you need to use those calculations.

          • 2. Re: Combining Fields
            KM

                 I am tracking the amount of chemicals applied to each field. My calculations are calculating the amount applied per acre, the cost of application per acre per field.

            • 3. Re: Combining Fields
              philmodjunk

                   That's the tip of what I suspect is a very large iceberg.

                   Do you have a table with one record for each field?

                   Do you have a related record that records each chemical application for one such field linked to the first field?

              • 4. Re: Combining Fields
                KM

                     I have 1 table and one record for each field

                • 5. Re: Combining Fields
                  philmodjunk

                       But what do you use to record the chemical applications to those fields? If you are using more and more fields to record each application of pesticides, all in one record, replace them with the related table that I described where you have one record for each chemical application and the number of your fields needed should become a lot less. (as in 10's or even 100's less...)

                  • 6. Re: Combining Fields
                    KM

                         Maybe I got that backwards, each field has one record where all applications of chemicals are recorded.

                    • 7. Re: Combining Fields
                      philmodjunk

                           That reads to me as saying the same thing as what you said before. What I am recommending is to use two tables. One table where you have a record for each field and one where you have a record for each chemical application with a relationship linking those records to specific records in the Fields table:

                           Fields-----<Applications

                           Fields::__pkFieldID = Applications::_fkFieldID

                           One way to record each time you apply chemicals to a particular field is to place a portal to Applications on your Fields layout.

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

                      • 8. Re: Combining Fields
                        KM

                             If I had the 2 tables, wouldn't I still need the same amount of fields for my database since each field (ground worked on) has a different amount of acres?

                        • 9. Re: Combining Fields
                          philmodjunk

                               I don't know exactly how you currently have this set up. But if my guesses are correct, no this would not be the case.

                               The number of acres would be a value entered into a field of the Fields table. A single calculation field in the Applications table can use the value in that field to compute, for example, pounds of chemical applied per acre.

                               Say you have two fields, one of 5 acres and another of 10.

                               In the Applications table, a record related to the first table can have a calculation field that divides by 5. In a record related to the second table, the same exact calculation field will divide by 10.

                          • 10. Re: Combining Fields
                            KM

                                 Thank you for your help!! :)

                                  

                                 Is it possible to create an IF statement for the units.  For example, IF Chemical = ABC then report rate as LBS.  IF chemical = XYZ then report rate as OZ.

                                  

                                  

                            • 11. Re: Combining Fields
                              KM

                                   I figured out how to calculate my last question, but now, how to I move the tab to the beginning of the text field? for example:  currently I have an IF statement that auto populates the unit of measurement in my rate field, but when I tab into the rate field the curser moves to the end of the text and I want it to be at the beginning. 

                                    

                                   Thanks!!

                              • 12. Re: Combining Fields
                                philmodjunk

                                     It sounds like you want the amount and the units in the same field. I would use two fields, one for the amount and one for the units. Keep numbers in number fields and text in text fields.

                                     But an OnObjectEnter performed script could be used to move the cursor to the beginning of the field. The Set Selection script step can be set up to do that.

                                • 13. Re: Combining Fields
                                  philmodjunk

                                       Ultimately, you will probably need a third table with one record for each type of chemical. Then, when you select or enter a chemical name/ID in a field in the Applications table, info about that chemical, such as the units, can be looked up from the Chemicals table. That saves entering the same data over and over and you can change what values are looked up for a given table without having to redefine your calculation.

                                  • 14. Re: Combining Fields
                                    KM

                                         I am not too familiar with scripts, is there a pdf I could reference or a section of the forum that would help me with that?

                                    1 2 Previous Next