3 Replies Latest reply on Jul 15, 2014 8:11 AM by philmodjunk

    Calculate Field Name

    cmskatz

      Title

      Calculate Field Name

      Post

           I am looking for a simple way to calculate which field to use in a calculation based on the name of the field being used. Let me give an example.

           I have multiple fields spanning a time span of 36 months covering Budget, forecast, orders invoiced etc. These are numbered sequentially with the last 2 digits referring to the month in question. i.e. FC_period_01, FC period_02 etc. I want to do some calculations on these fields without having to create individual calculations manually for each month - just 1 formulae that can be pasted into each calculation. 

           Say I wanted to measure sales against FC as a percentage and have the fields created as FC_percentage_period_01, FC_percentage_period_02 etc. I was thinking I can use 'right(getfieldname(self),2) to identify which month the calculation is for but can't figure out if its possible to use this number to tell it which other fields to then reference without using complicated case or if statements for every possibility.

           This way if the calculation was pasted into a calculation field that ended 01 it would reference all the other relevant fields ending 01, if pasted into a calculation filed that ended 02 would reference all the other relevant fields ending 02 with no change to the formula and so on.

           The percentage example above is simple and admittedly could be typed reasonably quickly anyway but some other calculations I have use multiple embedded ifs referring to dozens of fields each time and take substantially longer to create or amend.

           I'm hoping this is possible and that someone can point me in the right direction!

           Chris

            

        • 1. Re: Calculate Field Name
          philmodjunk
               

                    I have multiple fields spanning a time span of 36 months covering Budget, forecast, orders invoiced etc. These are numbered sequentially with the last 2 digits referring to the month in question. i.e. FC_period_01, FC period_02 etc

               This sounds like you have used multiple fields where a better design would use multiple records--probably multiple records in a related table. This can be a much more flexible design and can also eliminate the problem with trying to reference the correct field. And a single calculation can be used that computes a different total for each month by using a different record for each month.

               But when one does choose to use indirect field referencing, you can use:

               GetField ( "table::field" ) to reference the contents of Table::Field, any text calculation that evaluates to the correct combination of a table occurrence name, two colons and the field name can be used in place of the quoted text in my example.

               That's the "read" capability.

               Then Set field by name can use the same type of calculation as the first parameter to "write" to a field by evaluating the text calculation to determine the table::Field reference needed to modify a field.

               Other useful functions used in combination with these:

               Get ( ScriptParameter )

               GetFieldName ( Table::Field )

               Get ( activeFieldName )

               Get ( ActiveFieldTableName )

               Get ( LayoutTableName )

          • 2. Re: Calculate Field Name
            cmskatz

                 Thank you!

                 It was the getfield step I hadn't seen so this has put me back on track. In combination with the let function too this works perfectly and will save considerable time in some areas.

                 I note your point on the multiple fields versus multiple records in a related table. Wouldn't it make a relatively simple layout like the one attached quite complex to join and map? Maybe I need to experiment to see the benefits...

                 Thanks again

                  

                  

            • 3. Re: Calculate Field Name
              philmodjunk

                   What you show is often referred to as a "cross tab" report. Yes, the layout will be a bit more complex as you'd need a row of one row portals to show the data form different records in columns in this report. But many other parts of your database design becomes simpler and more flexible so you usually come out ahead in the long run by using a set of individual records in a related table instead of a group of fields all in the same record for this type of situation.