7 Replies Latest reply on Oct 21, 2011 10:20 AM by philmodjunk

    Calculation to Increase Numbers in Fields and Concatenate

    kingsley47

      Title

      Calculation to Increase Numbers in Fields and Concatenate

      Post

      So I'm trying to make a set of calculation cells that imitate a pretty useful function in excel. Here is the property from excel I want to imitate: if I wrote 1 in the first cell, then 2 in the cell right below it, I could then drag down and have the following cells increase by 1 until I stopped dragging. After making that list, I could program a row of cells to take a single value from another cell, lock it, then concatenate it with a number from the number list I made. If I dragged down then I'd get a list starting with the same values and always ending with the number in the corresponding cell from the number list.

       

       When I started writing this question, I thought I was close to a solution. But I just thought of a show stopper to my solution. Any idea how I could go about doing this without programming every single field? I have about 500 fields that I would like to serialize and concatenate with a value from another field. Figuring out a way to automate this would obviously be the best solution. 

       

      One last thing, I am in record view. I know this sounds like a job for list view but this is a test report that goes to our customer.

        • 1. Re: Calculation to Increase Numbers in Fields and Concatenate
          philmodjunk

          Please describe what you are trying to do with an actual example. That may suggest alternate approaches.

          The equivalent set up in FileMaker would use a record in place of each Spreadsheet row.

          With such a structure an auto-entered serial number field can auto enter serial numbers into each new records and you can specify the next serial number value. And a script can modify the "next serial value" setting on such a field if that is needed.

          Then calculation fields, if truly necessary, can concatentate that value with values in other fields. I said "if truly necessary" as you can set up merge text on a layout to combine the data from several fields without defining an extra calculation field to do this. If the only reason you need this value combined is for display purposes, that may be sufficient for you here.

          • 2. Re: Calculation to Increase Numbers in Fields and Concatenate
            raybaudi

             Maybe that you could solve this problem using a repeating calculation field, but I need a more realistic example of what you want to obtain.

            • 3. Re: Calculation to Increase Numbers in Fields and Concatenate
              kingsley47

              @Phildmodjunk: I could use your way to concatenate everything but since I'm trying to do this all in one record, I couldnt get away with using the each record in place of a cell. Each record is it's own test report containing anywhere from 30 hardness values to 300 hardness values all of which need to be serialized.

              How could I get away with serializing the serial number fields more easily than giving each one a distinct numeric value?

              @Raydaubi: Basically what I'm doing is creating a record that can be used as a hardness report. All of the hardness values are input manually but I'm trying to get other values, including a serial number for each hardness, to autopopulate. If i can figure out a trick to easily make serial numbers in one record than I should be able to make my idea work from there. Or at least be really close.

               

               

               

              • 4. Re: Calculation to Increase Numbers in Fields and Concatenate
                raybaudi

                 I am giving you an easy example.

                Say that you have a field ( start ) as a staring point.
                You could than create a calculation field with 10 reps and a calc like this:

                Extend ( start ) * Get ( CalculationRepeatitionNumber )

                If start contains 2, the repeating field will contain:

                2
                4
                6
                8
                10
                12
                14
                16
                18
                20

                • 5. Re: Calculation to Increase Numbers in Fields and Concatenate
                  philmodjunk

                  Personally, I'd use a related table for this instead of putting it all in one record. That allows you to have complete flexibility with regards to the number of hardness values associated with a given report record.

                  The serializing may take a different approach, than just a simple auto-entered serial number. Replace field contents could be used in a script to load a set of records in this related table with serial numbers.

                  • 6. Re: Calculation to Increase Numbers in Fields and Concatenate
                    kingsley47

                    Would the following suggestion be the route I should take?

                     

                    Currently, the hardness testers make their reports in excel. I could import the data from each excel sheet into some random table and then import from that table to the record/report I want to make.

                    • 7. Re: Calculation to Increase Numbers in Fields and Concatenate
                      philmodjunk

                      WHy a "random table"? Couldn't you just import into the report table?

                      BTW, if you had this set of related tables:

                      Report----<hardnessMeasurements

                      You can create a report with a list view layout based on HardnessMeasurements that includes the data from the parent Report record. This is a very flexible approach for this type of report as long as you have relationships that support it.