4 Replies Latest reply on Apr 21, 2017 7:08 PM by laguna92651

    Extracting values from list based on values in second list

    laguna92651

      I have two fields with a list in each field, the fields are related, Month and value. Something like this. The months and values are for the last 12 months.

       

      Month Names

      Feburary 2017¶March¶2017¶April 2017

       

      Month Totals

      1000¶2000¶1500

       

      I would like to extract the Month Totals values based on the corresponding Month Name position and put the values in the correct global monthly bucket, 12 months.

      Jan

      Feb 1000

      Mar 2000

      Apr 1500

      May

      Jun

      Jul

      Aug

      Sep

      Oct

      Nov

      Dec

        • 1. Re: Extracting values from list based on values in second list
          philmodjunk

          Did you really mean to post this?

           

          Feburary 2017¶March¶2017¶April 2017

           

          or should it actually be this?

           

          Feburary 2017¶March 2017¶April 2017

           

          A looping script or recursive algorithm can be constructed to do what you want, but putting each of these listed values in separate records of a related table would make associating a month/year value with a numeric value much easier. They could even be two fields in the same record.

           

          If presented with such data, I'd definitely consider moving the data into such a related table. If forced parse these lists, there are custom functions found on places such as the Brian Dunning site that give you a value's position in a list of such values. But again, I wouldn't put them into a set of separate global fields. A set of related records solves the problem from the beginning.

          • 2. Re: Extracting values from list based on values in second list
            laguna92651

            My mistake on the date. Can you give me a little more info on doing the related table. In case it makes a difference, the end result is to plot the data with google charts. Can't use the native charting because I will be using the FM runtime. Do you know if google charts can plot the list fields directly, I couldn't find anything on the google charts documentation?

            • 3. Re: Extracting values from list based on values in second list
              philmodjunk

              You shouldn't have any problem pulling data from the related table into a format that can be used with google charts.

               

              Both List and ExecuteSQL, can pull the data from this table into a list format. ExecuteSQL can be used to specify any delimiting character you might need to separate the values and Substitute can be combined with List to do the same. That should then enable you to submit this data to any charting set up that you choose to implement.

               

              I don't know the context behind your two lists of values so I will event a table called ParentTable to represent the table where you currently have such lists of values stored in text fields. I will also invent a field named __pkParentID as that table's primary key field. It should be defined to auto-enter either a serial number or the text returned by Get (UUID). If you do not have such a field, you will need to add one and use Replace Field contents to assign unique values to this field in all existing records as the auto-enter options I have mentioned only add a value to new records at the time that they are created.

               

              That then allows you to define a table, MonthlyValues, with three fields:

              MonthYear

              MonthTotals

              _fkParentID

               

              A Relationship can then be defined as:

               

              ParentTable-----<MonthlyValues

              ParentTable::__pkParentID = MonthlyValues::_fkParentID

               

              This is a very rudimentary description as I know nothing at all about your system as a whole. What I recommend here could be very different should you share that information. This information, for example, might well be summarizing information from a table of transactions where there are many records for each month of the year. The info you want could be pulled from such a table without using such a table of MonthlyValues should that be the case.

              • 4. Re: Extracting values from list based on values in second list
                laguna92651

                Okay thank you for the direction, I will give it a try.