7 Replies Latest reply on Sep 18, 2015 4:03 PM by electon

    How do you create a subfield from an existing field?

    yiningw

      I am trying to categorize a "30 minute" and "60 minute" subfield from the "duration" field, but I'm not sure how. The actual durations aren't usually exact numbers, so I think I'd have to sort it manually. Do I create a portal?

        • 1. Re: How do you create a subfield from an existing field?
          Mike_Mitchell

          A portal is a device to display records from another table that is related to the current table. (Strictly speaking, this can be the same table, but let's not muddy the water.) Based on your description, what you need to do is "bracket" time durations into 30-minute blocks, so it's not really a matter of related records, but rather, "Does this item fall into the bottom or top of the hour?"

           

          To accomplish that, you would probably use a separate calculation field (or just a separate field that stored the result of a calculation). You'll need to decide what your business rules are for determining the breakpoints. For example, if anything from 0 - 30 minutes past the hour counts as 30 minutes, and anything from 31 minutes to 59 minutes counts as 60, then it's one way. But if you want to do anything from 0 - 15 minutes as 0, anything from 15 - 44 as 30, and anything from 45 - 60 as 60, then it's something else.

           

          If you'll post the exact rules, we can probably help you with a calculation that will give you the result you're after.

           

          Mike

          • 2. Re: How do you create a subfield from an existing field?
            dtcgnet

            You could create a new field, type Text, auto-enter the following formula. Call the new field "VideoLength" or something like that.

             

            Case (

                 Duration > 0 and Duration < = 30 ; "30 minute" ;

                 Duration > 30 and Duration < = 60 ; "60 minute" ;

                 Duration > 60 ; "Longer than 60 minutes" ;

                 ""

            )

             

            No need for a portal or anything else. Make sure to unclick the "Do not replace existing value" box on the auto-enter.

            • 3. Re: How do you create a subfield from an existing field?
              erolst

              dtcgnet wrote:

              Case (

                   Duration > 0 and Duration < = 30 ; "30 minute" ;

                   Duration > 30 and Duration < = 60 ; "60 minute" ;

                   Duration > 60 ; "Longer than 60 minutes" ;

                   ""

              )

               

              You can simplify this to

               

              Case (

                Duration > 60 ; "Longer than 60 minutes" ;

                Duration > 30 ; "60 minutes" ; // at this point, we already know its <= 60

                "30 minutes" // // at this point …

              )

              • 4. Re: How do you create a subfield from an existing field?
                Mike_Mitchell

                I got the impression the OP wanted to divide the durations into 30 and 60 minute "buckets" (so many minutes after the hour), which is why I said we needed to see his business rules. But if all he wants is 30, 60, and more than 60, then erolst's calculation will suit fine.

                • 5. Re: How do you create a subfield from an existing field?
                  dtcgnet

                  Good point, erolst. You'd need one more condition in case the duration was zero or empty.

                   

                  Case (

                    Duration > 60 ; "Longer than 60 minutes" ;

                    Duration > 30 ; "60 minutes" ; // at this point, we already know its <= 60

                    Duration > 0 ; "30 minutes" ;

                    "Undefined length" // // at this point …

                  )

                  • 7. Re: How do you create a subfield from an existing field?
                    electon

                    If you have the durations calculated as time then you could try this one.

                    The calculation outputs results in time format.

                     

                    Let ([

                      duration = GetAsTime ( "01:38:00" );     // substitute your time field here

                     

                    // the divider / 26 sets the threshold for the half hour detection. the lower the number the steeper the slope on both sides.

                     

                      halfHours = Minute ( duration ) / 26;

                      rounded = Round ( halfHours  ; 0)

                    ];

                      Time ( Hour ( duration ) ; rounded * 30 ; 0 )

                    )

                     

                    You'd need some kind of a threshold anyhow.

                    HTH